How to Pivot Data in SSRS

Pivoting data can be tricky to say the least, whether the actual pivot is done in SQL Server or Reporting Services. There are cases to be had for both approaches, only to be constrained by your reporting design requirements. I’ve done both and had a very good reason for doing each one its own way.

On one report, I knew that my column headers were always going to be the same, no matter what group of data I was running the report against. In this instance, I let SQL Server do the pivot operation.

In another report, I knew that my column headers were going to change depending on the parameters that were passed into the report. In this case, I had no choice but to let SSRS do the pivot.

If I had a choice, I would do the pivot in SQL Server instead of SSRS, if not for simplicity’s sake. I’m more of a coding type of guy. However, there are instances where you cannot avoid a pivot in SSRS. This tutorial will walk you through the steps of setting up a report in SSRS to pivot data on the fly.

First, we need some data to operate with. The data below is what we are going to start with:

CREATE TABLE tmpTable
(
grp INT
,SortOrder INT
,AssessmentTitle NVARCHAR(50)
,AssessmentLevelLabel NVARCHAR(5)
,LevelScaleLow INT
)

INSERT INTO tmpTable
SELECT 1 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,110 AS LevelScaleLow
UNION
SELECT 2 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,138 AS LevelScaleLow
UNION
SELECT 3 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,147 AS LevelScaleLow
UNION
SELECT 4 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,159 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,116 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,153 AS LevelScaleLow
UNION
SELECT 3 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,174 AS LevelScaleLow
UNION
SELECT 4 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,198 AS LevelScaleLow

This is how your table should appear:

This is ultimately how we want our data to appear in the Report:

Since this data can change depending on the Assessment that we pass to the stored procedure, a Matrix is going to be needed in the report in order to facilitate dynamic columns. As I have discussed in an earlier post, a Matrix is needed for situations where dynamic columns are required. Else, you can get by with using a static table.

Now that our data is setup for our tutorial, it’s time to start working with pivoting the data in SSRS.

1. We need to create a new report and add a Matrix dataset to it. Here, I’m assuming that you have already created a data source and populated it with the data from the table above.
2. Grab the Matrix from the Toolbox and drag-n-drop it onto the Report. The Matrix data region provides helpful clues as to where you need to place your fields, specifying Rows, Columns, and Data.
   1. If we look in our table above we can tell that our Rows are going to be the AssessmentLevelLabels with their associated LevelScaleLow values, and the columns are going to be the AssessmentTitles.
3. So, for Rows, select AssessmentLevelLabel, for Columns, select AssessmentTitle, and for data, select LevelScaleLow.

Grouping and Sorting is automatically done when you assign the fields to the Row Grouping and the Column Grouping (AssessmentLevelLabel and AssessmentTitle in our case).

Now, run the report, and VIOLA! Notice that your data is now displayed in the pivoted format!

This approach of pivoting the data in SSRS instead of in SQL Server has a couple of advantages:

• You don’t have to design the report to accommodate every single column. This isn’t such a big deal in our example, since we ultimate only have two columns that end up as output. But imagine if you have the possibility of having 30 columns at any one time. You have to design the report and place out every single one of the 30 columns. What a pain!
• Portability – If you decide to drive your pivoted data from a stored procedure, and you are using this pivot across multiple reports, then you only need to go to one central location to make your changes, and they will all be propagated to each report.

That’s all there is to it! If you have any questions, feel free to leave a comment.