cancel_grouping_fields

LookML
Version

On this Page
Docs Menu

Go to Explore Parameter List

Usage

cancel_grouping_fields is a child of
explore
explore: view_name
...
1st
Tab
cancel_grouping_fields: [view.field]
one or more fully scoped field names

Definition

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.

Examples

Do not group the results if the user chooses the Order ID:

- explore: order cancel_grouping_fields: [order.id]

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

- explore: order cancel_grouping_fields: [order.id, order.hash]

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

- explore: person cancel_grouping_fields: [person.id, dna.id] joins: - join: dna sql_on: ${person.dna_id} = ${dna.id} relationship: one_to_one

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:

- explore: order cancel_grouping_fields: [id]

However, this is not the case, and you will receive an error. Instead you must write:

- explore: order cancel_grouping_fields: [order.id]

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

Still have questions?
Go to Discourse - or - Email Support
Top