SSRS Show/Hide Drilldowns

Drilldowns are often asked for to get an initial high-level view of data, then delve deeper into any interesting areas. One way to achieve this is to use the ‘Action’ property of an object to link through to another report, pre-populating parameters if necessary. Another is to set up a matrix with several levels of hierarchy and allow users to show the branches of the tree they are interested in. This is the technique I’ll be showing you in this article. In my example, I have created a stored procedure which groups by store, product, year & month and sums total sales amount. Alternatively you could pull out the raw data and let the report handle the grouping and aggregation.

Results
Stored procedure results

I then created a report with a matrix. For the columns I added a year group and underneath that a month group. For the rows, I added a store group and underneath that a product group. Obviously the structure completely depends on what gives most value to the user. With year and month, this is a natural hierarchy, but you could easily have the store level under the product level if this made more sense. I also created an ‘All Years’ level by adding a column outside of any column groups, and an ‘All Stores’ level by adding a row outside of any row groups. This gives me 3 row levels and 3 column levels. I also have an ‘All Months’ column for each year, and an ‘All Products’ row for each store.

The cells in the ‘data’ area of the matrix are all populated with Sum(Fields!TotalSaleAmount.Value) to sum up whichever values fall within the column and row group of that cell. If you view the report at this point, you’ll get a full table with all the levels and all the data showing.

MatrixDesign
Matrix design

The drilldown aspect is set up with just two properties: ‘Hidden’ and ‘ToggleItem’. For the four groups (store, product, year, month), set Hidden to True so that when you initially view the report you will only see the top-level ‘All Stores’ and ‘All Years’. Then for each of the groups, set ToggleItem to the report item that you want to control show/hide of that group. +/- will be shown in that report item, and it naturally makes sense for the parent group to show/hide its children or, as I’ve done, the ‘All’ level to control the individual items at that level.

Toggle1

Toggle2
Using AllYears textbox to toggle SaleYear group visibility

A handy aspect of this technique is that when you export to excel, the functionality is recreated using excel’s Groups and it exports in the state which you’ve left it. You can show/hide in a similar way to the report view and this is great for users who are used to doing this kind of analysis in excel.

ReportView1

ReportView2

ReportView3
Showing/hiding different parts of the data in report view.
ExcelView
Excel export
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s