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.
For example, the tab Available Values looks like this:
Go back to the report and in the bottom of the screen, select the first row group and choose “Group properties”. Click on the expression icon at “Group on” and enter the following value:
Notice that the syntax of the expression is a little different of what you usually use for field references, but this is just another way of building the expression and the other way of writing does not work in this case.
Click OK and if you have a second group, do the same for that group but then change the RowVariable1 into RowVariable2.
When you’re back at the report, right-click in the textbox where the label for the group is displayed (in my case above the textbox where it says “[Division]”). Enter the same expression here, because you want to make sure that the correct data will be displayed. Again, if you’re using a second group, do the same for that textbox that contains the label of the second group (in my case where it says “[Cost Center]”).
Finally, as you can see in my illustration of the table above, in the grey area I’ve added labels for the columns to make it clear to the end user what they’re looking at. But when other fields are selected to group on, these labels have to change as well. To change this, simply right-click on the textbox and choose Expression, and add the following expression:
And as you might have expected by now, do the same in the second group but then for RowVariable2.
Allright, when you’ve followed all these steps, your table should look like the following illustration:
Now run the report. You’ll see that you’ll have two parameters to choose from. Select a field value from these parameters (which are actually just text value entered in the parameter properties) and view your report.
With this method, it’s not just possible to dynamically fill the row groups, the same could be done with column groups. That and other nifty stuff I’ll cover in later parts!