Usage

Definition

cancel_grouping_fields lets you 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 or relationship: many_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.

Examples

Do not group the results if the user chooses Order ID in the field picker:

Do not group the results if the user chooses Order ID or Order Hash in the field picker:

Do not group the results if the user chooses Person ID or DNA ID in the field picker:

Common challenges

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 that appears in the field picker:

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 cancel_grouping_fields.

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 in which to use cancel_grouping_fields.