Have you ever created a report using a matrix with many row groups? Unless you created a stepped report, you may have created a clumsy looking report with row groups spread out across many columns. Fortunately, we can put multiple row groups inside the same column to create a clean looking multidimensional report. Here’s a quick reference for creating stepped reports in SSRS.
First, create a new report with a matrix inside it. I may create some basic how-to’s at a later date, but there’s plenty of other resources out there. For your dataset, use the following code with the AdventureWorksDW2012 database, which is available for free on Codeplex.com. You’ll need to create your own data source connection to this database as well. Here’s the T-SQL code for the dataset that I’ll use in this example.
SELECT SUM(fis.SalesAmount) SalesAmount ,d.CalendarYear ,pc.EnglishProductCategoryName ProductCategory ,sc.EnglishProductSubcategoryName ProductSubcategory FROM dbo.FactInternetSales fis INNER JOIN dbo.DimDate d ON fis.OrderDateKey = d.DateKey INNER JOIN dbo.DimProduct p ON fis.ProductKey = p.ProductKey INNER JOIN dbo.DimProductSubcategory sc ON p.ProductSubcategoryKey = sc.ProductSubcategoryKey INNER JOIN dbo.DimProductCategory pc ON sc.ProductCategoryKey= pc.ProductCategoryKey GROUP BY pc.EnglishProductCategoryName, sc.EnglishProductSubcategoryName,d.CalendarYear ORDER BY pc.EnglishProductCategoryName, sc.EnglishProductSubcategoryName, d.CalendarYear
First, lets add our Sales Amount measure by clicking by hovering over the detail matrix cell and clicking on the detail icon.
After you’ve added the Sales Amount measure, drag the Calendar Year field from your dataset in the Report Data pane, into the Row Groups pane.
Next, we need to add the Product Subcategory group as a parent group of the Calendar Year group. To do this, right click on the Calendar Year group in the Row Groups pane, and select Add Group -> Parent Group.
This will open a dialog box. In the Tablix Group dialog box, select the Group By textbox, click the down arrow, and select ProductSubcategoryGroup. Then, check the Add Group Header checkbox, and select OK.
This creates a new column containing the new Product Subcategory row group. However, this isn’t what we actually want. We want all of our row groups in the same column, and this is the *trick* in making a stepped report in SSRS. To get the Subcategory group in the same column as the Calendar Year group, first select hover over the empty cell in the Calendar Year column, click on the blue detail icon, and select Product Subcategory.
Now we have both groups in the same column, so we no longer need the Product Subcategory column. Right-click in the grey rectangle above the Product Subcategory column header and select Delete Columns.
Perfect! Now we need to repeat the process for the Product Category group.
In the Row Groups pane, right click on the Product Subcategory group, and select Add Group -> Parent Group. In the Tablix Group dialog box, select the Group By drop arrow and this time, select the Product Category Group. Also, be sure to check the Add Group Header checkbox. This is a required step in this process.
Now we’ll see our new Product Category group as a new column. We need to repeat the process of adding our new row group into the same column as our other row groups and deleting the new Product Category group. In the Calendar Year column (we’ll rename this at the end), click the blue details icon, and select ProductCategory. Now that you’ve added the Product Category row group into the Calendar Year column, we can delete the Product Category column. Right click on the gray rectangle, and select Delete Columns.
Ok. Now we have all three of our row groups inside the same column. Let’s do some formatting to improve the aesthetics. Right click on the Product Subcategory group in the Row Groups pane, and select Group Properties. In the Group Properties window, select the Visibility tab in the left pane. Select the option to Hide the group when the report is initially run. Then check the checkbox for the option to toggle the display option by another report item. In the dropdown list, select the Product Category group. This allows the Product Subcategory group by be a drill down group.
Repeat the same process for the Calendar Year group in the Row Groups pane. This time, select the Product Subcategory group to be the toggled report item. This will allow users to drill down into the Product Subcategory group, and see yearly sales.
Now that we have all of our groups in the same column, and we’ve set them to be drill down groups, let’s indent the subgroups to improve the readability of our report.
In the matrix, right-click on the Product Subcategory cell, and select Text Box Properties. In the Text Box Properties window, select the Alignment tab in the left pane. In the Padding Options section in the right pane, change the Left padding to 10 pt. Click OK. Next, repeat this process for the Calendar Year group cell, this time changing the Left padding to 15 pt.
Now, in the detail cells for Product Category and Product Subcategory, select the Sales Amount measure. This will add a calculation SUM(SalesAmount) to aggregate the Sales across our row groups. Also, rename the Calendar Year header label to Product Categories.
Once we perform the minor formatting we’ll be finished. Change the font for a font of your liking. Format the measures to a currency value. Then center and bold the header, and change the color scheme to your liking. Now lets look at our finished interactive stepped report.