Dynamic Group By
Dynamic group bys are support in Bar, Single Axis Line Charts, Pie Charts, and Data Grids. Dynamic group bys in custom sql widgets look and behave nearly identical to migrated widgets. There are a few key differences:
- Dynamic group bys in custom sql widgets group by selections all function independently as opposed to widgets migrated from the same page have the group by selection linked
- The controls for dynamic group bys are always on the widget and never in the filter drawer as opposed to most migrated widgets where they will exist in both the widget and the filter drawer
- The group by selection in custom sql widgets is 100% configurable while migrated widgets are not
To activate dynamic groups by there are two unique magic sql snippets. One of these snippets appears in the sql editor, while the other appears in the widget title. NOTE: putting magic sql in the widget title is unique to this feature and required
The format for the magic sql looks like this:
{{groupBy:cm_companystats.industry_short<Industry>|cm_companystats.region<Region>|cm_companystats.company_tier<Company Tier>}}
There are 3 parts:
{{groupBy:- this declares the start of the group by
cm_companystats.industry_short- this is the sql that will be inserted when this group by is selected
- This must reference a table.name
- coalesce/ifnull functions are allowed
<Industry>- this is the friendly name that will be displayed in the drop down
Each drop down selection is followed by a | which is used to indicate the start of a new selection. After all desired drop down selections are made the magic sql must be closed with }}
Widget Title Requirements
Somewhere in the widget title the string {{groupBy}} must exist. When viewing a widget in the dashboard, the {{groupBy}} will be replaced by the selected friendly label declared in the magic sql outlined above. This text will also be a different color. Clicking on the friendly label will cause a menu to appear allowing you to select a different group by value. If the magic sql is detected in the sql, but NOT in the widget title, the widget will throw an error.
Example Query:
select
{{groupBy:cm_companystats.industry_short<Industry>|cm_companystats.company_region<Region>|cm_campaign.campaign_type<Campaign Type>|cm_companystats.company_fit<Company Fit>|cm_companystats.customer_status<Customer Status>}} 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
Note
- The default group by selection is the first option in the magic sql.
- The drop down menu will populate in the order that is written in the magic sql. it will not apply any sorting of friendly labels
- On clicking preview or save the validation will check to ensure all selections of the dynamic group by are valid, including the sql. Depending on how many options there are, and how big the query is, this may take longer than normal time
- In the preview screen on the widget editor, the group by is not fully functional clicking the label in the chart title will cause the drop down to appear but no options are selectable. If you get this far it means the dynamic group by is setup correctly
- All other widget or filter type specific rules will still apply when constructing a widget with a dynamic group by