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.
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.
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.
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.