Dealing with group headers in banded reporting tools like Crystal Reports feels easy and natural. The group header section is clearly visible on the design surface, and it is simple to add text or format group header fields. Dealing with SQL Server Reporting Services (SSRS) group headers is a little less intuitive, as it is not a banded reporting tool, and uses the concept of a “Tablix” for displaying grouped data in a table structure.
In this article, I’ll take you through the steps necessary to create a “full width” group header on an SSRS report, using Report Builder 3.0.
The Easy Way – Adding a New Table
The fastest way to get the basic Tablix on the report with the grouping we want is to use the Table Wizard, which is available on the Insert tab under the “Table” option. For this example, you’ll create a table based on a query that has last_name, date_worked, and hours_worked fields. You’ll group on the last_name column:
After clicking “Next”, you are presented with the default layout options. The “Blocked” options give you that “Excel” look that you don’t want – choose the “Stepped, subtotal above” option. Un-check “Expand/Collapse groups”, and click “Next”. Then you’ll choose the “Generic” style and click “Finish”. When you run the report you should end up with a table that looks something like this:
You’re close now! The next thing you need to do is select your group header row in the table and click the “Merge” button:
This gives you the desired full-width header cell, which you can then style to taste. It does, however, remove the summary value. To add a summary row as a “footer” for each group, you simply right-click on the detail row and insert a row below the group:
You can then merge the footer cells as you did the header, and add the Hours Worked field to the appropriate cell:
You can right-justify the cell, and you now have a data table with full-width group headers and footers – just style as desired!
The Harder Way – Modifying an Existing Table
It can be a little more difficult to modify a previously created table that has been set up as “Blocked”, but the following steps should suffice:
- Delete the Group column
Highlight the group column, and delete it. The table will still be grouped by that field, only the column will be removed.
- Insert a new Header Row
Right-click on the detail row, then select “Insert Row”, choosing the “Outside Group – Above” option. This will create a new row above each group instance. You can merge these cells to create a full-width header, and add the Group field to the merged cell.
- Insert a new Footer Row
Repeat the step above, but this time choose “Outside Group – Below”. This will create a new row below each Group instance, and any summaries can be placed here.
That’s all there is to it! Dealing with Group Headers and Footers in SSRS can be confusing at first, especially if you are coming from a banded reporting tool like Crystal Reports. It doesn’t take long to get used to working with the Tablix control, though, and I hope the steps above helped clear the way a little.