One of the nice things about a stepped report is that the child groups are displayed beneath the parent groups but within the same column as the parent group. In the next several steps, I’m going to walk through creating a basic stepped report using the Tablix data region. I used the Matrix data region template for this example.
First I created a report and dragged in a Matrix data region into the design area. I used the Adventure Works Cube and a simple data set with the Product hierarchy, Reseller Sales Amount and Calendar Year. Here is the query I used for the main dataset in case you would like to follow along.
SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS * [Date].[Calendar].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I put Product in the row group, Calendar Year on the column group, and Reseller Sales Amount in the details.
Next we need to create a parent group for to display our Subcategory. To create a parent group, in the Row Groups pane, right-click the Product group, select Add Group, then select Parent Group.
Select Subcategory as the field you want to group by. Make sure to check the Add group header check box.
After creating the new group for Subcategory, right-click the Subcategory group in the Row Groups pane, and create a new parent group for the Category field. Again, check the box next to Add group header.
At this point, you matrix should look like this.
But since we are creating a stepped style report, we want our parent groups in the same column as our detail group. So we can delete the columns that contain Category and Subcategory.
Then add Category and Subcategory to the rows above Product so that your Matrix looks like what you see below. Also, don’t forget to add Reseller Sales Amount in the cells next to Category and Subcategory.
Now we’re almost done. To create the stepped formatting, right-click the cell containing Subcategory and select Text Box properties. In the Alignment pane under Padding options, set the Left padding to 15pt. Then click OK.
Then right-click the cell containing the Product field and click Text Box properties. This time in the Alignment pane, set the Left Padding to 30pt.
The last thing we have to do is create that nice, fancy, collapsible drill-down action. To do that, right click the Product row group in the Row Groups pane and navigate to the Visibility pane. When the report is initially run, we want the Product row group set to Hide. Check the check box next to Display can be toggled by this report item. Since we want the Product row group to be toggled by its parent group, select the Subcategory text box in the drop down list.
We also should set the visibility settings of the Subcategory row group so that it is also hid when the report is initially run. It should be toggled by the Category row group.
And after adding some nifty back ground colors to make our report a little easier to read and some bold text on the header row, you are done! We now have a simple, yet easy to read, stepped report that will allow the end user to drill down into the child group rows to view the data at a more granular level.
Like this:
Like Loading...
