SQL Server Reporting Services (SSRS) – Creating Full Width Group Headers in SSRS

Creating Full Width Group Headers in SSRS

Introduction

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:

Adding the table with the Table Wizard

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:

A Basic Table

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:

Merging the Header Cells

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:

Adding the Footer

You can then merge the footer cells as you did the header, and add the Hours Worked field to the appropriate cell:

Adding the Summary Field

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.

SQL Server Reporting Services (SSRS) – Exec SQL from a passed parameter

Let’s say you have a report that executes a dataset which produces fields for your report. You define that dataset like this:

select firstname, lastname, middlename 
from name_table

You then pull the columns from that table and load the data into your report fields (First Name, Last Name, and Middle Name).

Now, let’s say you want to make the SQL more complex by adding filtering with a WHERE statement like this:

select firstname, lastname, middlename 
  from name_table 
 where lastname = 'DOE'

Now comes the tricky part.

Let’s say that the report is executed from an application that has a search form. That form has 25 controls on it used for filtering. The client enters or chooses their filtering from those 25 controls and clicks a search button. The results are returned to a grid. The application then provides a PRINT button that executes your SSRS report that displays the same data provided in their grid.

You can do this two ways.

1) Define 25 parameters in your report and have your dataset interrogate each parameter in your WHERE clause. But what if every month, the application adds or removes controls from their search form? Now you have to change the report too!

2) Have the application pass the SQL statement used to get and populate their grid as a parameter to your report. You then define that one parameter in your report like this:

1

Then in your Dataset Properites, you must do two things:

a) In the Query Tab, inside the Query textbox, use the EXEC statement to execute the parameter that contains the SQL like this:

2

b) And lastly, you must manually define the fields in the Fields tab that are needed for the report like this:

3

As long as the data displayed in your report (See below) never changes, although the filtering in the application may change, you won’t need to change your report again. 🙂

4