Expand-collapse report with data set based on GROUPING SETS

Question

I've used the Expand/Collapse feature in SSRS reports before, but in all those cases it was Reporting Services that was doing the grouping and totalling. This time around I utilize GROUPING SETS in my dataset query to let SQL Server handle aggregating the data. I want to create a report that has Expand/Collapse features for the groups, but can't seem to get it to work.

Repro
First up, here's a way to get a small repro simulating my actual situation. Use the following query for a dataset:

-- Simulating with already denormalized data for sake of simplicity
DECLARE @Order TABLE (Category VARCHAR(20), Product VARCHAR(20), PersonId INT);

INSERT INTO @Order
        (Category, Product, PersonId)
VALUES  ('Fruit', 'Banana', 1)
       ,('Fruit', 'Banana', 1)
       ,('Cakes', 'Chocolate', 1)
       ,('Fruit', 'Apple', 2)
       ,('Cakes', 'Chocolate', 2)
       ,('Cakes', 'Berry Jam', 3)
       ,('Cakes', 'Chocolate', 3)
       ,('Cakes', 'Chocolate', 3)
       ,('Fruit', 'Banana', 4)
       ,('Cakes', 'Berry Jam', 5)

SELECT Category,
       Product,
       COUNT(DISTINCT PersonId) AS NrOfBuyers 
FROM @Order AS o
GROUP BY GROUPING SETS ((), (Category), (Category, Product))

This will provide this output (I've manually ordered the output to illustrate my intentions):

Category    Product     NrOfBuyers
--------    -------     ----------
Fruit       Apple       1
Fruit       Banana      2
Fruit       NULL        3
Cakes       Berry Jam   2
Cakes       Chocolate   3
Cakes       NULL        4
NULL        NULL        5

To foreshadow what I'm aiming for, here's what I want to get in Excel.

Expanded version of intended result:

expanded report result

Collapsed version of intended result:

collapsed report result

What I've tried so far:

While writing this question and creating the repro I did realize that my first approach of just dumping my dataset in a tablix was wrong.

So what I tried to fix this was recreating the tablix with proper Row Groups like so:

Row Groups pane in SSRS

In addition to that I need a column on the left hand side outside the main group to hold the toggle "+" for the grand total row.

However, this gives incorrect numbers for the collapsed version:

preview of report, collapsed

These should be different: Cakes and Fruit have a "Subtotal" of 3 and 4, respectively.

This seems like a problem with ordering the rows, so I've checked the sorting for the Tablix and that should order rows as the appear in the "intended result" screenshots. It doesn't, and after a bit I understood why: the groups do sorting as well. So I've added sorting for the groups as well, e.g. this is the one for the Product Row Group:

SSRS sorting for Product Row Group

This seems to improve things (it does the sorting bit I needed anyways) but it doesn't fix having the wrong numbers in collapsed state.

What do I need to do to finish this last stretch and complete the report?

Jeroen Jeroen · Accepted Answer · 2014-05-19T13:13:52

The approach can work, but one last step is needed to get the correct numbers for collapsed state. Know that with the example from the question this design:

report design

Shows the following expression for this cell:

=Fields!NrOfBuyers.Value

But this sneakily seems to come down to this:

=First(Fields!NrOfBuyers.Value)

When it is evaluated in the context of a collapsed row.

So, one way to "fix" this and get the correct sub totals is to change that expression to:

=Last(Fields!NrOfBuyers.Value)

Which will give the desired output in collapsed state:

completely collapsed

Or semi-collapsed:

semi-collapsed

And finally, expanded:

Expanded result

Expand-collapse report with data set based on GROUPING SETS

1 Answers