In this article we will show you,
- How to add Column Totals to Matrix reports?
- How to add Row Totals to Matrix reports?
- How to add Grand Total to Matrix reports?
- How to create Drill Down Matrix Report in SSRS manually?
Drill Down Matrix Report in SSRS
We are going to use below shown report to Enable the Drill Down in SSRS Matrix Reports. Please refer SSRS Matrix Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Matrix report. If you observe the below screenshot, It was a normal Matrix report with Country, State Column as Row Group and Gender, Occupation as Column Group.
Add Totals to SSRS Matrix Report
Right click on the Aggregated data column will open the context menu with multiple option. Please select the Add Total Option and then choose Row or Column as per your requirement. Here we are selecting Row it means, It will generate new Row below the existing row with Row Total
Next, we are selecting Column. It means, It will create new column on right side to the existing row with Column Total
TIP: You can always change the default aggregate function SUM to other function by selecting the fx Expression from the above screenshot.
We can add the Grand total by selecting the empty cell in between the Row Total and Column Total. Next, add the Sales Amount column to that cell as shown in below screenshot.
We changed the Background Color of the Grand Total cell to show you the, difference between Row, Column and Grand Total. Click on the Preview tab to see the Report Preview
Enable Drill Down in SSRS Matrix Report
It will be very easy to enable Drill Down option when we are creating reports using Report Wizard but to enable manually we need some extra effort. In this example we will show you, How to hide State Names and Occupation Names and
- Allowing user to select the Country Name to show or hide the state names
- Allowing user to select the Gender Name to show or hide the Occupation / Profession Column Names
First, goto Row groups pane and right-click on the State Group will open the context menu. From the context, Please select Group Properties.. option as shown in below screenshot.
Once you select the Group Properties.. option, a new window will be opened to configure the Group Properties. Please select the Visibility tab on the left hand side.
Visibility tab have multiple options:
- Show: When you run the report initially, If you want to show the state names then select the Show option
- Hide: When you run the report initially, If you want to Hide the state names then select the Hide option. In this example, we want to hide the state names initially so we selected this option.
- Show or Hide based on an Expression: You can write your own expression to show / hide the state names by clicking fx button.
- Display can be toggle by this report time: If you select this option, State names will be show/hide based on the report item we selected here. In this example, we want to toggle the state names based the Country names selected by the user so select the Country column from the list as shown below.
Now, Goto Column groups pane and right-click on the Occupation Group and select the Visibility tab on the left hand side. In this example, we want to toggle the Occupation column names based on Gender selected by the user so select the Gender column from the list as shown below.
Click Ok to finish enabling the Drill down functionality to Matrix report Manually.
Click on the Preview tab to see the Report preview. If you observe the below screenshot, State Names and Occupation Names are not displaying and we have + symbol beside the Country names and Gender Names to expand them.
Once you click on the + symbol, corresponding state names will be displayed.
Let us preview the whole report.
Thank You for Visiting Our Blog