stepped report example

Creating Stepped Reports with SSRS 2008

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.

Capture1

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.

Capture2

Select Subcategory as the field you want to group by. Make sure to check the Add group header check box.

Capture3

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.

Capture3a

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.

Capture4

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.

Capture4a

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.

Capture4b

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.

Capture5

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.

Capture6

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.

Capture7

About these ads

7 thoughts on “Creating Stepped Reports with SSRS 2008”

  1. Great Article Dustin – I can use this technique to clean up my drill down reports and use the report realestate more efficiently. While are on the subject of drill down and stepped reports – would it be possible – in addition or as an alternative to the + drill down symbol – could the group names act more like a web enabled hyperlink that feature an underline or font color change action when the mouse is rolling over it? Sort of giving the user the massage of – ‘hey there is more information here if you click on me’

    1. Unfortunately the + symbol is the only way to indicate the drill down capability. Although you could format the text to look like a hyperlink (blue font color and underlined).

      1. Would it be possible to create a custome mouse_over event and use a code assembly to dynamically change the font properties during the mouse_over event? – Maybe this is not possible either …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s