SQL SSRS How to drill down State into top three cities. Do I do it in SQL or SSRS?

I am creating a simple report where I have a location (down to the city level) and some information about that city in regards to my customers.

Here is what the table look like

Now what I want to happen is to have a tablix in SSRS, but not broken down by the city level, but by the State level, and with the totals summed to the state level. Then when you click on each state, it breaks down the TotalCustomerValue field (and other fields) by the top 3 cities (and maybe a “other” to make sure the totals are equal?)

I know a bit about SQL but I have no experience doing drill downs. If anyone can help point me on the right path, that would be greatly appreciated. Do I keep my query exactly as is and all the drill down stuff is done in SSRS? Or do I have to make changes to my query?

Answer

So in your tablix you want to use rowgroups.
Your first rowgroup will be State, then your second group City.

A quick way to see that in action is to use the Matrix wizard. Drop State then City into the Rowgroups and the TotalCustomerValue into values. On the next page check expand / collapse groups (and optionally totals)

If you right click on the state row group you will see under visibility that “Display can be toggled by this report item” is checked.

To solve the fact that you don’t want all cities but want to group them, you will need some MDX in the expression for the City group.

there is a good post here which will give you some guidance:

MDX – TopCount plus ‘Other’ or ‘The Rest’ by group (over a set of members)