It happens to me lots of times that I’ve created an SSRS report, grouped on a certain dimension, and while the end user is happy with the result, I get the question back “Can I also group this table on this-and-this dimension as well?“. Well, sure you can! You can create a second tablix or even a second report that retrieves exactly the same data but then you group the table on another dimension. I was at that stage where I was thinking that there should be a better way to do that instead of copying and pasting the report and change some settings. And there is, so I’ll share the way I’ve solved this issue and made one report very dynamic so the user of the report has a lot more flexibility than just filtering some data. I’m well impressed with the possibilities in this area, so I’ll call this “Part 1” and devote another blog post to go deeper into this subject.
To start off with, let me explain the business situation of the case. This case is dealing with support calls that have been registered, and the business controller wants to have a report in Reporting Services on the amount of cases were created and handled. He wanted to have flexibility on grouping the data, for example to show by division, by manager, by employee who handled it, etc.
So how did I solve this? In the Business Intelligence Development Studio, you create a report as you would normally do, and you create a dataset with the data you want to display. Make sure you include all the fields in here for which you want to include the grouping possibility for. For example, I chose the fields division, cost center, manager, employee and a sum on the support calls. As a side note, it doesn’t matter here if you retrieve your data from a database directly or from an Analysis Services cube.
Here is what the dataset looks like:
Next step is to create a tablix. It doesn’t matter if you create a table or matrix here, that depends if you want to add column groups as well. This tutorial only displays the functionality with row groups. For now, select any of the fields in the dataset to at least create one group, it would be easier in my example if I already create two groups. In the end, when I’ve created my table, it looks like this:
You can run your report to see if you get the results that you want. Next step is to add the selection for the end user so that they can decide what fields to group on. To do this, create two new variables (or one, if you only use one group). I’ve called these parameters RowVariable1 and RowVariable2. Some important settings for the parameters are the following:
- Data type: text
- Multiple select: No
- Available values: Add the values that you want the end users to select. The label doesn’t matter what you enter, the value must be the same name as the field name in your report data.
- Default value: Whichever value you think the end user wants.