Creating Multiple-Column Reports [SSRS]

Now and then I get inspired to write about a certain topic as a result of what I encounter on the forums.  Here’s one of those moments.

The examples in this article are created using SQL Server 2012 RTM – 11.0.2100.60 (X64).  I’m also using the free AdventureWorksDW2012 sample database available at CodePlex.  But that doesn’t imply that you actually need 2012 to get this to work.  The principle explained in this article should also work on SQL Server 2008 and even 2005.

You can download the resulting RDL from my SkyDrive.

Introduction

Have you ever needed to create a report that shows a list of something spread over multiple columns?  Then you may have come across a feature called newsletter-style reports.  Excited to have found out about that feature, you started creating your report.  But alas, upon rendering the preview you discovered that it didn’t work.  Further investigation made you conclude that the feature is only supported in two specific renderers (PDF and Image).  Ouch!

In this article I’ll be showing you a method to create multiple-column reports that you can actually use with all renderers!

Setting The Scene

We’ve been asked to create a report that shows a list of product codes with each category displayed on a separate page.  The codes should be displayed in four columns.

Implementing The Report

To get these requirements implemented, we’ll be using some T-SQL skills in combination with nested tables.  So, first step is the dataset query.

The Dataset

The query retrieves only the fields needed in the report plus one additional, calculated field called DisplayColumn.  This additional field indicates the column in which the code should get displayed.  Here’s what the results look like:

List of product codes with column number by category

As you can see, the column numbering starts at 1, goes up to 4 and then starts again at 1.  It also starts at 1 again at the start of a new category.  This is needed because we’ll be grouping the data on category in the report.

Now, let me show you that query and explain a bit how the DisplayColumn is getting calculated:

declare @numberOfColumns int = 4;

select dpc.EnglishProductCategoryName, dp.ProductAlternateKey
    , (ROW_NUMBER() OVER (
        PARTITION BY dpc.EnglishProductCategoryName
        ORDER BY dp.ProductAlternateKey) + @numberOfColumns - 1) % @numberOfColumns + 1
    as DisplayColumn
from dbo.DimProduct dp
inner join dbo.DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
inner join dbo.DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey;

The main ingredient here is the ROW_NUMBER function.  According to its definition, it “returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition”.

That brings us to the OVER clause, and its PARTITION BY statement.  In our example we partition by category because we need the numbering to start over for each category.

Next up is the ORDER BY part.  We order by ProductAlternateKey, which is the product code that our report should display.  We want the codes to be ordered alphabetically so the column numbering should take that order into account.

As you can tell, I’m using some numeric trickery on the result of the ROW_NUMBER function.  The reason for that is because I’d like my numbers to start at 1 and end at 4.  If we would just do “row_number % 4” (% is the modulo operator btw), then our last column would be zero instead of 4.

Okay, that’s part one of the method explained.  Over to the visualization part.

Setting The Tables

We’re going to create a table that shows the product category in a header with the product codes spread over four columns below the header.

So, first drag a table into the design canvas and give it an additional column.

To quickly link the table with the dataset, drag the product code field into the detail cell of the first column.

Now we’re going to set up the grouping.  We’ll also be making use of nested tables and because tables cannot be nested on the detail level of a tablix, we need to set up grouping even on that detail level.  So first, open up the Group Properties of the Details level by right-clicking it in the Row Groups pane:

Opening up the Detail-level Group Properties

Click the Add button to add a group and group on the product category:

Group the Detail level on product category

We want to display a header on the category level, so we’ll need to add another group on top of this one.  Right-click the Details row group once more and add a Parent Group:

Adding a Parent Group to the Details level

Group by product category once more and activate the Add group header checkbox:

Adding parent group on product category

Here’s what our table now looks like:

Some cleaning-up to be done

We don’t need that first column and we don’t need that top row, so delete both.  Now select the four textboxes in the upper row, right-click and select Merge Cells:

Merging the header cells to prevent text getting cut off or wrapped

This way the header line gets to use all available space.  Now put the product category field into the remaining merged cell and render the report:

Grouping set up as expected

If all went well you should be seeing the four categories.  That means the grouping part is implemented.

Of course, the detail level is not yet as needed because only the first code of each category is being shown and the three other columns are still empty.

So let’s address that now!

Nesting The Tables

Currently our four detail-level cells are just plain textboxes.  But textboxes don’t allow filtering and we need filtering because the first column should show only the product codes with DisplayColumn equal to 1, second column 2, and so on.

So what we’re going to do is nest another tablix inside each of those four cells.  This nested tablix should be one-celled, so just one column and one detail-level row.  That can be done through several ways.  I’ll show you two.

To set up the first detail-level cell, drag another Table inside that first textbox (the one that currently contains the product code).  That gives us this:

Nesting a table in a table

Now select the nested table by clicking one of its cells:

Select the nested table

It may be a bit tricky so if needed, temporarily increase the height of the detail-level row to enlarge the nested table.

Now remove two columns and remove the header row of the nested table.  Then right-click the grey square in the upper-left corner and open up the Tablix Properties:

image

These are the properties of the nested table.

Switch to the Filters page, click the Add button and set the filter to DisplayColumn = 1.

Filter on DisplayColumn

Click OK to close the properties.

Now put the product code field again in that first detail-level cell and render the report:

One column done, three remaining!

One column done, three remaining!

Let’s set up the second column.  This time, drag a List into the second detail-level cell:

image

A List is in fact a one-celled tablix with a Rectangle inside.  But we don’t need that rectangle here, we need a Textbox.  So select the rectangle by clicking once inside the cell.  You can tell that the rectangle is selected by looking at the Properties window, as shown above.

Now hit the Delete button on the keyboard.  In the Properties window, you should see Rectangle changing into Text Box:

Removing the Rectangle from the List leaves us with a Text Box

Once more we’ve got a one-celled tablix.  Set up filtering on DisplayColumn = 2, using the method as explained when setting up the first column (hint: upper-left grey square).

Put the product code field inside the second detail-level cell and render the report:

Second column set up

If all went well, your second column should now display some codes as well.

Two more to go!

Setting up the remaining two columns is actually really easy.  Select the nested tablix inside the second cell by clicking the small grey square in the upper-left corner.  Then hit CTRL+C, select the third detail-level cell and hit CTRL+V.  Also paste into the fourth detail-level cell.

Now change the filtering to 3 and 4 for column three and four and render the report:

All four columns display product codes

All four columns display product codes!

What now remains is some visual cleanup and adding page breaks on the category group.  I’m only going into details on the page break.

In the Row Groups of the main table, right-click the upper group and select Group Properties.  Then switch to the Page Breaks page and check the Between each instance of a group checkbox:

Adding a page break on the category group

Render the report to see the final result:

Report implemented: product codes in four columns grouped per category!

The first page displays the product codes of the first category, spread over four columns!  The other pages contain the other categories.

Requirements implemented!

Conclusion

Even though it takes a bit of work and some tricks, it is possible to create multi-column (aka newsletter-style) reports using basic SSRS components whilst still supporting all renderers.

I hope you enjoyed this article, have fun!

Valentino.

References

ROW_NUMBER() function

Share

Tags: , , , ,

  1. Sergio’s avatar

    Valentino, a very useful article !
    I’ve gone through the frustration of the SSRS subreports not populating the columns and with this technique now I can do it !

    Reply

    1. Valentino Vranken’s avatar

      Thanks for your comment Sergio, it’s always nice to hear that an article has proven to be useful!

      Reply

  2. Domenic’s avatar

    This is phenomenal… I’m curious, though, about another closely related scenario.
    For instance, what if you wanted to display the first N rows in the dataset in column 1, but the second 0-N rows in column 2?
    For instance, given a simple table of id, name, say you have this data:
    1 a
    2 b
    3 c
    4 d
    5 e
    6 f
    7 g

    That is the raw dataset. Let’s say you want to display 3 rows per column, with 2 columns
    1 a 4 d
    2 b 5 e
    3 c 6 f

    7 g

    How would one go about doing that?

    Reply

    1. Valentino Vranken’s avatar

      Hello Domenic,

      Thanks for your interesting question! It would take a bit too much space to type the answer here so check out this new article: http://blog.hoegaerden.be/2013/07/17/creating-multi-column-reports-the-top-down-version-ssrs/

      Best regards,
      Valentino.

      Reply

  3. Domenic’s avatar

    In the above example, after 3 c 6 f is a page break that didn’t render because i used angle brackets.

    Also, the table in the example was:
    id name
    1 a

    Where 1 is the id, and a is the name, etc.

    Reply

  4. Gosi’s avatar

    Great post, but what if you have a two columns with large amount of data that is going to next page. Is it possible to have the data in column two continue in column one on the next page?

    Reply

    1. Valentino Vranken’s avatar

      No sorry, can’t think of any way to get that implemented…

      Reply

      1. Domenic’s avatar

        This was essentially the same thing I was asking :(

        Reply

      2. Domenic’s avatar

        Really a difficult problem that would probably mean you would have to clearly define how many records you could fit on one page to accomplish, and it would have to be supported by the reporting engine I would imagine.

        Reply

  5. mark brethren’s avatar

    Thanks for posting this. I have a different question here. I am building a report that has a subreport. The main report contains a list of Offices and the subreport has staff information. Currently, if you run the report, it has each office as header and the staff information. For example, Office A has 3 staff information, while office B had 4 staff information . I am trying to create a multicolumn report that will spill the excess report on the another column. The problem I am having right now is the same report is showing up on both columns. I used expression =iif((RowNumber(Nothing) Mod 2) = 1, False, True)
    and =iif((RowNumber(Nothing) Mod 2) = 1, True, False)
    but I am really stuck right now. Any help will be appreciates

    Reply

  6. Chad’s avatar

    This is fantastic, thank you!

    I have a 3 column working nicely, my issue is around row height, I can live with it as is, but is there any way to have the row height be the highest of the 3 columns?

    I can set the height to something short then let it grow, but this gives me varying number of cells per page.

    I want a 3 by ‘X’ matrix, X can be bigger page to page if the data allows it, but X needs be the same column to column for readablity.

    Reply

  7. Rekha’s avatar

    Hello,
    I am newbie. Saw ur post and it is very useful for me.
    I have one problem.
    I used column Groups as Catergory_type, month.

    The o/p should need to come like

    CAT I CAT II CAT III
    Jan Feb Mar Jan Feb Mar Jan Feb Mar
    10 10 20 9 8 19 17 12 9
    8 9 7 10 9 16 27 19 8

    Here the Category names and month is display when i preview.
    But i dont know how to print the values. Generally it prints the SUM() values.

    Could u plz help me about how to print it?

    Waiting for ur reply.
    Please help me.

    Reply

  8. Konstantinos’s avatar

    Hi Valentino,

    I followed your steps to create a report with only one category and I would like to add a pagebreak every 10th row. How would you do this instead of adding the pagebreak after every category?

    Reply

    1. Valentino Vranken’s avatar

      Hi Konstantinos,

      Interesting question… I would add an additional field to the query using the ROW_NUMBER trick explained above but this time the number needs to increase by 1 every 10 records. Here’s how that goes:

      , ROW_NUMBER() over (order by (select null))/11+1 PageNumber

      This gives you a PageNumber field on which you can now group your tablix. Add a page break on the group and there you go: page breaks every 10 records!

      Thanks for your question!
      Valentino.

      Reply

    2. José Luis’s avatar

      Did you try using the fiuncyion Ceiling?

      Reply

  9. Joni’s avatar

    Thank you so much for the clear explanation. I was able to create a report using your step-by-step directions.

    Reply

  10. Nilesh’s avatar

    Awesome! This is a creative work around solution. Your blog was very helpful in solving a report request I had. Thank you for sharing this!

    Reply

  11. José Luis’s avatar

    Wonderful! This article is very useful. Thanks a lot.

    Reply

  12. Tony’s avatar

    Muchas gracias!

    Reply

  13. manas’s avatar

    GroupName1 GroupName2
    1 a 8 x
    2 b 9 y
    3 c 10 z
    GroupName2
    4 d
    5 e
    6 f
    7 g

    Neet to print in one page

    Reply

  14. manas’s avatar

    GroupName1 GroupName2
    1 a 8 x
    2 b 9 y
    3 c 10 z
    GroupName2
    4 d
    5 e
    6 f
    7 g

    Neet to print in one page,Thanks in advance

    Reply

  15. manas’s avatar

    With group By …This is phenomenal… I’m curious, though, about another closely related scenario.
    For instance, what if you wanted to display the first N rows in the dataset in column 1, but the second 0-N rows in column 2?
    For instance, given a simple table of id, name, say you have this data:
    1 a
    2 b
    3 c
    4 d
    5 e
    6 f
    7 g

    That is the raw dataset. Let’s say you want to display 3 rows per column, with 2 columns
    With group By
    1 a 4 d
    2 b 5 e
    3 c 6 f

    7 g

    How would one go about doing that?

    Reply

  16. Matt’s avatar

    I wish the example was simpler. What if I didn’t want to group by category? Then what would I partition over on?

    Reply

  17. Vivek’s avatar

    Thanks for sharing, nice article and very helpful.

    Reply

  18. Ron’s avatar

    Thank you for putting this together. I was scratching my head on how to do this and you saved me.

    Reply

  19. Brian’s avatar

    Thanks for putting this together. It was very useful and well-written.

    Reply

  20. Salman’s avatar

    Hi Valentino,

    I implemented your way but still not getting the the columns in a seq defined in filter of 1st child tablix.
    if I have data like below

    100 1
    100 2
    100 3

    the result would be 100 100 100

    But it comes
    100 100
    100

    I have grouped the tablix on 4 fields.
    Please give a loom

    Reply

  21. Craig’s avatar

    This is fantastic! I needed to replicate the Crystal reports function called “format with multiple columns” to show multiple rows across one row in separate columns.
    This did the trick and was very easy to follow.
    Thank you so much for sharing this solution!

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved