SSRS – Multiple Row Grouping Elements to appear under a Single Column within a Table in a report

This is a guest post by a co-worker Ram Sangam.

In SSRS 2008, when you create multiple Row groupings based on the dataset that is being consumed in the report, SSRS adds these grouping elements as separate columns in the table of the report. In the example shown below (Fig.1), Adventure Works DW Sales information is being retrieved by Territory and Product Classification (using database AdventureWorksDW and the tables FactResellerSales, DimProduct, DimProductSubCategory, DimProductCategory and DimSalesTerritory). The row groupings in the table are Geographical Group(GeoGroup), Geographical Region(GeoRegion), Product Category and Product Sub Category. By default, these groupings (Group, Region, Product Category and Product Sub Category) are added to the report as columns in the report making the table wider.

Fig.1:

 

At times, it may be required these row grouping elements to be part of a single column within the table, giving it a tree view control experience (as shown below in Fig.2)

Fig.2:

 

The implementation of this look and feel required creating the Row Grouping in this following sequence.

- Insert a table from the Toolbox onto the report.

- Drag and drop the data field “TotalSales” from the dataset defined for the report on to the Data section of the table that was inserted in the previous step. This sets the dataset property for the table that we will be working with in this example.

- By default, the table is created with 2 rows (a header and a detail) and 3 columns. Delete the 2 columns which are currently not used in the report.

- From the “Row Groups” pane in the bottom of the screen, right click on the “=(Details)” row and “Add Group->Parent Group…”. Create the row grouping based on the top level “Geo Group” in this case (Be sure to check the option “Add Group Header”) and click OK.

- For the column “TotalSales” column use the “SUM(Fields!Totalsales.Value)” as its value.

- From the “Row Groups” pane in the bottom of the screen, right click on the group that we created and select Group properties to name the group appropriately for the report (in this case, “grpGeoGroup”).

- From the “Row Groups” pane in the bottom of the screen, right click on the “grpGeoGroup” row and “Add Group->Child Group…”. Create the row grouping based on the top level “Geo Region” in this case (Be sure to check the option “Add Group Header”) and click OK.

- For the column “TotalSales” column use the “SUM(Fields!Totalsales.Value)” as its value.

- The cell above the new group that was added (in this case “Geo Region”) will be empty by default. Set the property of the cell to the value of the parent group element (in this case “Geo Group”).

- Delete the left most column of the report, in this case the column holding the “Geo Group” information.

- Repeat these steps for the additional groups (namely Product Category and Product Sub Category) that are to be created.

- After creating all the groups, from the “Row Groups” pane in the bottom of the screen, right click on the “=(Details)” group properties, Change the visibility option to “Hide”.

- After creating all the groups, the report designer should look similar to screen shot shown in Fig.3. (Note in this example, I have added color scheme and renamed the columns to easier reading)

- To get the indentation in each level, use the groupings “TextBox Properties…->Alignment->Padding Options (Left)” to get the desired effect.

- To get the tree view click experience, use Row Groups pane from the bottom of the screen and for each Row Groupings’ “Group Properties…->Visibility->Display can be toggled by this report item” option to refer to the parent row group data element. For e.g., Product Sub Category grouping to use “Product Category” textbox as its toggle item.

- In the cases where the dataset is being served by Analysis Services, and when the MDX query returns subtotals in addition to the details, instead of SUM, use the "Aggregate()" for each of the group totals in the SSRS textbox. In addition to this, you will need to either delete the “=(details)” group from the “Row Groups” pane or make the detail row cell separate from its parent group (in this case “Product Sub Category”). This is accomplished by right-clicking on the “Product Sub Category” cell and selecting “Split Cells”.

Fig.3: