GROUPING()

Use the GROUPING() clause with ROLLUP to determine whether null values are the result of a ROLLUP operation or part of your dataset. GROUPING() returns 1 if the null value is a subtotal generated by a rollup. It returns 0 otherwise.

This example uses the GROUPING() function and CASE statements together to label the subtotal and grand total categories. The first case checks for a null value generated by the rollup in the Category field. If true, then the query labels the field “All Categories.” Similarly, the second case checks whether a Sub-Category field is null. If true, the query labels the field “All Sub-Categories.”