Posts tagged ‘SSRS’

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:

grp SortOrder AssessmentTitle AssessmentLevel LevelScaleLow
1 1 Biology I 110
2 1 Biology II 138
3 1 Biology III 147
4 1 Biology IV 159
1 2 Math I 116
2 2 Math II 153
3 2 Math III 174
4 2 Math IV 198

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

Level Biology Math
I 110 116
II 138 153
III 147 174
IV 159 198

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.

Inconsistent Column Width in SSRS

The issue I ran into was this: I was in Design mode of a report that I was developing. No matter what I did to change the column width on my columns, in both Preview as well as when I published my report, the column widths were still off. FRUSTRATING!!

After Googling for a couple of days, and coming across some not-so-helpful answers, I finally decided to try one more thing. Being fairly new to SSRS, I decided to search to see what the difference was between a Matrix and a Table. I came across the following definition from BOL:

“Use a matrix to display grouped data and summary information. You can group data by multiple fields or expressions in row and column groups. Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page.”

In laymen’s terms, a Matrix is used if your report can have dynamic columns. If you don’t need that functionality, then a Table will suffice just fine.

At Last! My last-ditch attempt to circumvent this most annoying problem. I did away with my layout having a Matrix as the root, and dropped a Table into Design mode instead, and started building my report. To my delight, I could size the columns any way that I wanted to, and the formatting would stick!

Bottom Line: If you have to use a Matrix because of the reason mentioned above (dynamic columns), then formatting the layout of those columns is going to be a royal pain. If you can get away with using a Table instead, then do it. It will make your life so much easier.

Inconsistent Background Colors in SSRS 2008

I ran across this issue a while back and figured I’d blog about my findings. After running my issue through Google, it turns out that I wasn’t the only one with this same problem.

In SSRS, you can use the following code expression to set alternating background colors on your rows as part of the Background Color property, making the data easier to read:

=IIF(RowNumber(Nothing) MOD 2,"WhiteSmoke","White"

The only problem with this code is this: If your matrix has grouping on the dataset, then there is the possibility that your background colors can be inconsistent. Look at the image below to see an explanation of what happens when you use the above code snippet as your background color:

image

Notice on this example how the first row is White, then the next 2 are WhiteSmoke, the next White, etc. These rows should be presented as shown below (alternating White to WhiteSmoke):

image

 

To accomplish this, I derived the background in SQL Server using a case statement, and then passed that column value as the background image in SSRS.

CASE
    WHEN DENSE_RANK() OVER (ORDER BY LastName) % 2 = 1 then 'WhiteSmoke'
    ELSE 'White'END as BGColor

I found a great write-up relating to the different ranking functions within SQL Server here; if you’re confused about any of the ranking functions, I would suggest it as a good read. It is written to SQL Server 2005, but is also applicable to SQL Server 2008. The way that I am using the ranking function here, the case statement performs a MOD 2 (% 2) function on the LastName. If the result = 1, then we pick WhiteSmoke as the background color. Else, we choose White.

Now, all that needs to be done is to include the BGColor column as part of your dataset, and then set the Background Color property in the cells that you want to alternate (in my case, I wanted the entire row to alternate colors, so I set BGColor as the Background Color in all of my cells).

Another advantage of this approach is centralized maintenance. Say, for instance, that you have a stored procedure in which you are incorporating this code, and that stored procedure is utilized by 5 different reports. If, for some reason, your user comes back saying that they want the alternating background colors to be different, then you have to physically touch each RDL file to accommodate this change. By determining the background inside SQL Server, you only have to make the change in one place — the stored procedure.

One thing to note: Whatever you use as your order column in your case statement (in my case, I used LastName), you have to ensure that your grouping in SSRS is also set to order by that column. Else, your row colors will once again be out of order.

Hope that this write-up helps someone work around the headache that I spun my wheels working through!

NOTE: If you enable sorting on any of your columnsin SSRS, then this approach will not work. What I have found is that you can use the function that I mentioned above, and all you need to do is to ensure that your data is presented to SSRS sorted by LastName (perform the sort in your dataset query in SSRS, and the also sort your data in the Report).

=IIF(RowNumber(Nothing) MOD 2,"WhiteSmoke","White"