Dynamic Group By in Widgets

What is Dynamic Group By?

Dynamic Group By is a powerful feature that lets you add an interactive dropdown menu directly to a widget’s title. Viewers can use this menu to change how the data in the widget is grouped on the fly.

For example, a single “Revenue over Time” chart can be instantly switched by the viewer from “Revenue by Industry” to “Revenue by Region” or “Revenue by Campaign Type,” all from one widget.

This feature allows you to build one flexible, interactive widget instead of needing multiple separate widgets for different data slices.

Supported Widgets:

• Bar Charts
• Single Axis Line Charts
• Pie Charts
• Data Grids

How It Works: Magic SQL

Magic SQL ({{groupBy:…}}): This is a special command you add to your SQL query. It acts as the “blueprint” for the dropdown, defining all the options the user can select.

What is Magic SQL for Dynamic Group By?

Magic SQL for Dynamic Group By is a special syntax used inside the SQL editor to define the options for your dropdown. It tells the widget:

• What SQL code to insert when an option is selected.
• What friendly label to show the user for that option.

The syntax for Magic SQL follows a specific format:

{{groupBy:SQL_Snippet_1<Label_1>|SQL_Snippet_2<Label_2>|...}}

Let’s break down each part:

Component	Example	Purpose
Opener	{{groupBy:	Required tag that declares the start of the dynamic block.
SQL Snippet	cm_companystats.industry_short	The actual SQL code (e.g., table.name) that will be inserted into your query when this option is selected.
Label	<Industry>	The user-friendly name that will be displayed in the dropdown menu. The text inside the < > brackets is what the user sees.
Closer	}}	Required tag that closes the dynamic block.

Step-by-Step Setup Guide

Follow these steps to configure a new Dynamic Group By widget.

Step 1: Write Your Base SQL Query

In the widget editor, start by writing your SQL query.

Important for Bar and Line Charts: This feature requires unpivoted data. Your query results must include exactly three columns, aliased as:

• _xaxis
• _dimension
• _measure

For Pie Charts and Data Grids, this specific unpivoted structure is not required.

Step 2: Add the Magic SQL to Your Query

Identify where your “group by” column should be. This is typically the column you will alias as _dimension.

Replace the static column name with the Magic SQL syntax. You must add your own SQL alias (e.g., as _dimension) after the closing }} tag.

Example Query:

SQL

select
{{groupBy:cm_companystats.industry_short|cm_companystats.company_region|cm_campaign.campaign_type}} as _dimension,
sum(touch_value) as _measure,
'Opps Attributed: '||count(distinct opp_Id) as _hover_label
from cm.cm_insights_attribution
left join cm.cm_companystats using (company_id)
left join cm.cm_campaign on cm_campaign.cm_id = cm_insights_attribution.campaign_id
where default_model = true
group by all

• This creates a dropdown with three options: “Industry,” “Region,” and “Campaign Type.”
• When a user selects “Region,” the SQL cm_companystats.company_region is inserted into the query.
• Functions like coalesce or ifnull are allowed in the SQL snippet (e.g., coalesce(cm_companystats.industry_short, 'N/A')<Industry>).

Step 3: Add the Magic Text to the Widget Title

In the widget’s settings, find the Widget Title field. Add the following Magic Text placeholder exactly where you want the dropdown to appear:

{{groupBy}}

Example Title:

Attribution over Time by {{groupBy}}

On the dashboard, {{groupBy}} will be replaced by the friendly label of the selected option (e.g., “Industry”). This text will be a different color and will be clickable.

Step 4: Preview and Save

Click “Preview” to check your setup.

• In the preview screen, you will see the dropdown label (e.g., “Industry”), but it will not be clickable. This is normal and confirms your setup is correct.
• Click Save. Once the widget is on your dashboard, the dropdown menu will be fully functional.

Key Behaviors and Rules

• Default Selection: The default group by is always the first option you list in the Magic SQL (in our example, “Industry”).
• Dropdown Order: The menu options will appear in the exact order you write them in the Magic SQL.
• Joins Required: You must ensure that any table referenced in a Magic SQL snippet (like cm_companystats) is properly joined in your query.
• Widget-Specific Control: The dropdown is part of the widget and is not connected to the dashboard’s filter drawer.
• Independent Widgets: Each widget’s Dynamic Group By is independent. Changing the selection on one widget will not affect any others.
• Validation: When you save, the system validates the SQL for every option in your Magic SQL. This may cause saving to take slightly longer than usual.

Troubleshooting and Errors

• Error: Magic Text requires Magic Sql Group By.
  • Cause: You added {{groupBy}} to the widget title, but the SQL editor does not contain the {{groupBy:...}} Magic SQL.
• Error: Magic sql requires Magic Text Group By.
  • Cause: You added the {{groupBy:...}} Magic SQL to your query, but you forgot to add {{groupBy}} to the widget’s title.
• Error: Dynamic group by requires unpivoted data. Results should include…
  • Cause: You are using a Bar or Line Chart, but your query is not returning the required _xaxis, _dimension, and _measure columns.
• Error: when working with custom Data Grids the dynamic group by column needs to be aliased as groupBy
  • Cause: Datagrids are expecting the column with the dynamic group by magic SQL to be aliased as “_groupBy” Note the capital “B” and the leading underscore