THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Reporting Services - It's a Wrap!

If you have any experience at all with Reporting Services, you have probably developed a report using the matrix data region. It's handy when you want to generate columns dynamically based on data. If users view a matrix report online, they can scroll horizontally to view all columns and all is well. But if they want to print the report, the experience is completely different and you'll have to decide how you want to handle dynamic columns. By default, when a user prints a matrix report for which the number of columns exceeds the width of the page, Reporting Services determines how many columns can fit on the page and renders one or more separate pages for the additional columns. In this post, I'll explain two techniques for managing dynamic columns. First, I'll show how to use the RepeatRowHeaders property to make it easier to read a report when columns span multiple pages, and then I'll show you how to "wrap" columns so that you can avoid the horizontal page break. Included with this post are the sample RDLs for download.

First, let's look at the default behavior of a matrix. A matrix that has too many columns for one printed page (or output to page-based renderer like PDF or Word) will be rendered such that the first page with the row group headers and the inital set of columns, as shown in Figure 1.

Figure 1

The second page continues by rendering the next set of columns that can fit on the page, as shown in Figure 2.This pattern continues until all columns are rendered.

Figure 2

The problem with the default behavior is that you've lost the context of employee and sales order - the row headers - on the second page. That makes it hard for users to read this report because the layout requires them to flip back and forth between the current page and the first page of the report. You can fix this behavior by finding the RepeatRowHeaders of the tablix report item and changing its value to True. The second (and subsequent pages) of the matrix now look like the image shown in Figure 3.

Figure 3

The problem with this approach is that the number of printed pages to flip through is unpredictable when you have a large number of potential columns. What if you want to include all columns on the same page? You can take advantage of the repeating behavior of a tablix and get repeating columns by embedding one tablix inside of another.

For this example, I'm using SQL Server 2008 R2 Reporting Services. You can get similar results with SQL Server 2008. (In fact, you could probably do something similar in SQL Server 2005, but I haven't tested it. The steps would be slightly different because you would be working with the old-style matrix as compared to the new-style tablix discussed in this post.) I created a dataset that queries AdventureWorksDW2008 tables:

SELECT TOP (100)
e.LastName + ', ' + e.FirstName AS EmployeeName, d.FullDateAlternateKey, f.SalesOrderNumber, p.EnglishProductName, sum(SalesAmount) as SalesAmount
FROM FactResellerSales AS f INNER JOIN
DimProduct AS p ON p.ProductKey = f.ProductKey INNER JOIN
DimDate AS d ON d.DateKey = f.OrderDateKey INNER JOIN
DimEmployee AS e ON e.EmployeeKey = f.EmployeeKey
GROUP BY p.EnglishProductName, d.FullDateAlternateKey, e.LastName + ', ' + e.FirstName, f.SalesOrderNumber
ORDER BY EmployeeName, f.SalesOrderNumber, p.EnglishProductName

To start the report:

  • Add a matrix to the report body and drag Employee Name to the row header, which also creates a group.
  • Next drag SalesOrderNumber below Employee Name in the Row Groups panel, which creates a second group and a second column in the row header section of the matrix, as shown in Figure 4.

Figure 4

Now for some trickiness.

  • Add another column to the row headers. This new column will be associated with the existing EmployeeName group rather than causing BIDS to create a new group. To do this, right-click on the EmployeeName textbox in the bottom row, point to Insert Column, and then click Inside Group-Right.
  • Then add the SalesOrderNumber field to this new column. By doing this, you're creating a report that repeats a set of columns for each EmployeeName/SalesOrderNumber combination that appears in the data.
  • Next, modify the first row group's expression to group on both EmployeeName and SalesOrderNumber. In the Row Groups section, right-click EmployeeName, click Group Properties, click the Add button, and select [SalesOrderNumber].
  • Now you need to configure the columns to repeat. Rather than use the Columns group of the matrix like you might expect, you're going to use the textbox that belongs to the second group of the tablix as a location for embedding other report items. First, clear out the text that's currently in the third column - SalesOrderNumber - because it's already added as a separate textbox in this report design. Then drag and drop a matrix into that textbox, as shown in Figure 5.

Figure 5

Again, you need to do some tricks here to get the appearance and behavior right. We don't really want repeating rows in the embedded matrix, so follow these steps:

  • Click on the Rows label which then displays RowGroup in the Row Groups pane below the report body.
  • Right-click on RowGroup,click Delete Group, and select the option to delete associated rows and columns.

As a result, you get a modified matrix which has only a ColumnGroup in it, with a row above a double-dashed line for the column group and a row below the line for the aggregated data. Let's continue:

  • Drag EnglishProductName to the data textbox (below the line).
  • Add a second data row by right-clicking EnglishProductName, pointing to Insert Row, and clicking Below. Add the SalesAmount field to the new data textbox.
  • Now eliminate the column group row without eliminating the group. To do this, right-click the row above the double-dashed line, click Delete Rows, and then select Delete Rows Only in the message box.

Now you're ready for the fit and finish phase:

  • Resize the column containing the embedded matrix so that it fits completely.
  • Also, the final column in the matrix is for the column group. You can't delete this column, but you can make it as small as possible. Just click on the matrix to display the row and column handles, and then drag the right edge of the rightmost column to the left to make the column virtually disappear.
  • Next, configure the groups so that the columns of the embedded matrix will wrap. In the Column Groups pane, right-click ColumnGroup1 and click on the expression button (labeled fx) to the right of Group On [EnglishProductName].
  • Replace the expression with the following: =RowNumber("SalesOrderNumber" ). We use SalesOrderNumber here because that is the name of the group that "contains" the embedded matrix.
  • The next step is to configure the number of columns to display before wrapping. Click any cell in the matrix that is not inside the embedded matrix, and then double-click the second group in the Row Groups pane - SalesOrderNumber.
  • Change the group expression to the following expression: =Ceiling(RowNumber("EmployeeName")/3)
  • The last step is to apply formatting. In my example, I set the SalesAmount textbox's Format property to C2 and also right-aligned the text in both the EnglishProductName and the SalesAmount textboxes. And voila - Figure 6 shows a matrix report with wrapping columns.

Figure 6

Published Sunday, May 09, 2010 9:08 AM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JPatrick said:

How do I show the header only once when column wraps?

July 22, 2010 3:09 PM
 

Chad said:

Thanks!  This is very helpful.  There's so many non-obvious "tricks" for displaying SSRS and thanks for sharing this one.

February 28, 2011 4:18 PM
 

Chad Dipman said:

Stacia,

I have tried out this technique using task data from Project Server 2010, and works great for turing a long list of task data into a matrix.  I really like it.  However, unlike your report which has alphabetized the list of EnglishProductName(s), my report seems to randomly order my TaskName(s) in the inserted matrix.  I have tried various sorting scenarios in the ColumnGroup1 Group Properties dialog box, but so far have not had luck sorting in any way.  Any ideas on what I am doing wrong?

Thanks,

Chad Dipman

October 3, 2011 6:01 PM
 

smisner said:

Chad, I don't think you're doing anything wrong. Sometimes Reporting Services behaves oddly with respect to sorts. In general, I prefer to manage sorting in the dataset by using an ORDER BY clause when working with relational sources. Report execution typically performs better that way and then I can be certain that I get the desired results. You do need to make sure that you remove the sorting from the group properties when you take that approach.

November 20, 2011 2:04 AM
 

Navin said:

Hi Stacia,

Is it possible to generates tables  dynamically ?

For e.g. lets say i have to print the details of employees(employee table) from a department(Department Table) in a separate table or subreport.So for each entry in Department Table there will be a separate table generated dynamically with corresponding employee details.

Thanks ,

Navin

January 25, 2012 1:51 AM
 

smisner said:

Hi Navin,

Yes, you can use a list that use Employee for the grouping and then put a table inside the list to provide the details. The catch is that the Employee grouping and the details in the table all must come from the same dataset. You can get around that by using a sub report inside the list rather than a table, but you might run into performance issues with that approach is you have a lot of employees and employee details. The only way to know if you're going to have performance problems is to try it.

January 25, 2012 10:06 PM
 

Navin said:

Thanks Stacia !!

I tried subreport approach that works fine ,as there are not many related records no noticeable performance issue.

January 27, 2012 8:50 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement