cancel_grouping_fields is a child of
|one or more fully scoped field names|
cancel_grouping_fields enables you to stop Looker from adding a
GROUP BY clause to the SQL that it generates. If any of the fields that you specify are included by the user, Looker will not group. This functionality is typically used to improve query performance on very large tables. Except in rare and unique circumstances, you should only include fields that are unique to each row in the table, such as the primary key.
Since Looker measures represent SQL aggregate functions, which require a
GROUP BY clause to work, you should note that
cancel_grouping_fields will not work with any report that includes measures. Furthermore
cancel_grouping_fields does not work when
relationship: one_to_many is used.
Finally, note that the fields you list must be fully scoped. In other words, they should be written as view_name.field_name, and not simply as field_name.
Do not group the results if the user chooses the Order ID:
Do not group the results if the user chooses the Order ID or Order Hash:
Do not group the results if the user chooses the Person ID or DNA ID:
cancel_grouping_fields Requires Fully Scoped Field Names
Most parameters in Looker will assume a view name, based on the place that the parameter is used, if you write a field name by itself.
cancel_grouping_fields does not work this way and requires you to write both the view name and field name.
For example, you might think this would work, and that id would be interpreted as the Order ID:
However, this is not the case, and you will receive an error. Instead you must write:
cancel_grouping_fields Is Triggered When Any Specified Field Is Chosen, It Doesn’t Require All Fields To Be Chosen
If you specify more than one field in
cancel_grouping_fields, grouping will be cancelled if a user selects any field in the list. The user is not required to select all the fields in the list. For this reason, multi-column primary keys don’t work with
Things to Know
cancel_grouping_fields Is Not Required For Looker To Work Properly, It Is For Query Improvement On Large Tables
When writing SQL by hand, most people will not include a
GROUP BY clause unless it is absolutely necessary. Looker also avoids unnecessary
GROUP BY clauses in some cases. If one of the dimensions in your query has been defined as the primary key (by using the
primary_key parameter) of the explore you’re using, the
GROUP BY clause will be dropped.
However, there are some cases when another dimension - which is not the primary key - still defines a unique row. In these cases Looker may generate an unnecessary
GROUP BY, because grouping by dimensions is a fundamental part of how Looker works. In the majority of cases, this won’t cause any problems. Results will show up the way you expect them and will be speedy.
However, on some very large tables, unnecessary
GROUP BY clauses can lengthen query times. This is the ideal situation to use