0

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

enter image description here

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?

Natan
  • 139
  • 2
  • 13

1 Answers1

2

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)

iainc
  • 862
  • 6
  • 20
  • So I don't have to do anything to my query? This will all be done in SSRS? – Natan Jun 25 '19 at 17:40
  • 1
    I got it to work almost. It does it like it is supposed to except, it comes expanded automatically. Is there a way to have it so it says only AL Total .... and then when you click on it, it expands into all the cities? https://i.imgur.com/Ta3wyHo.png – Natan Jun 25 '19 at 17:50
  • 1
    @Natan yes. You set the row group for Cities to be hidden by default, toggled by the textbox where you display the State name. See here: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-an-expand-or-collapse-action-to-an-item-report-builder-and-ssrs?view=sql-server-2017 – BishNaboB Jun 25 '19 at 18:07