Now I've added my sub report control to both of the 2 tables

How To Create Multi-Column Lists in SQL Server Reporting Services (SSRS)

I recently encountered a scenario where I wanted to display my report content in the form of a multi-column list.  In essence, I was looking to create the SQL Server Reporting Services (SSRS) equivalent of an ASP.NET Repeater, to render my report content both horizontally and vertically (in this case a dynamically repeated SubReport) a number of times dictated by my source dataset.  I spent a little bit of time investigating some more complicated possibilities such as building a matrix of row and column groupings when the solution finally hit me.  It is simply brilliant in its simplicity.  Here’s how to do it.

Set up your SSRS report with a Table for each column you want in your listCreate your report as normal with whatever other components you need and add your source dataset.  Now, for every column that you want to have in your list, add a new Table control.  In my example, I wanted to create a 2-column list, so I’ve created 2 Table controls, side by side.

Now that you’ve done that, the next step is to associate both of them with your dataset.  This will allow the tables to dynamically generate a single row for every row of data that you’ve returned.

Next up, add whatever content you want to have in your table/list.  This could just be a field from the dataset or it could be something a little more grandiose.  In my example I’d already created a sub report which contained a Chart control.  I was wanting to render n charts in 2 columns all the way down the report.  As you can see in the next picture, I’ve added my sub report control to each of the 2 tables.

Now I've added my sub report control to both of the 2 tablesIf we try to run the report now (assuming you’ve set up any required parameters for the sub report), what you’ll see is 2 columns (tables) each displaying exactly the same data.

The next part is to get the 2 columns to display data horizontally as well as vertically.  The secret to this part is not to think about this as a single list but now start thinking of it as 2 separate tables, based on the same dataset (as it really is).

So, right-click on one of the tables and select the “Row Visibility” menu item:

Select the "Row Visibility" menu itemIf this hasn’t given the trick away, I don’t know what will.  On the Row Visibility dialogue box, click the “Show or hide based on expression” radio button.

All we need to do now, is enter an expression which will hide the alternating rows on this table.  We’ll enter a similar expression on the second table but hiding the other rows which were visible on the first.

Use the Mod function to calculate the modulus to determine which rows to hide.  If you’re using 2 columns here, you want to find the modulus of 2, if 3 columns, the modulus of 3 and so on.

If the remainder is 1, we display the row, otherwise we hide it.

Use modulus to show/hide each row based on the remainder.For the second table (i.e. our alternating items) we simply need to change the remainder value expected to 0.  Therefore the 2nd table will display only the rows that are divisible by 2.

And that’s it.  Using this simple method, we can create a de facto list in SSRS by using a little bit of maths and some basic slight of hand.  The best part is that because we’re using the same dataset for both tables, we only hit the database once.  There is a slight overhead on rendering time in SSRS so I wouldn’t recommend using this for thousands of items but then you really wouldn’t want to create a list of thousands of items anyway, would you?

A simple tip, with impressive results.

Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET, now mainly AWS. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you're a gamer.

Leave a Reply

13 Comments

  1. grateful

    Graham,
    Your idea/solution was EXACTLY what I was looking for. I was hitting the wall trying to figure out how to do an 11-column report with each column having different # of rows depending on the data. I was getting all twisted up trying to do it w/Pivots and other complex data transformations in SQL, but your approach made it very easy, THANKS!!!

    The only remaining issue is just cosmetic. I want each column to be the same height, regardless of # of rows of data. I put a rectangle around each tablix and gave it a height greater than the max # of rows expected, but the rectangles’ height gets extended by the number of rows in the tablix. I was looking to set the property of “ALLOW TO SHRINK/EXPAND” to FALSE, but this property doesn’t seem to exist for Rectangles or Tablix. Have you figured this out, too?

    • Graham

      Hey there. Unfortunately, I’m not sure about a way to fix the height of a rectangle/tablix but I have to admit that I’ve not tried it. I’ll have a look and see if I can come up with anything. Sorry for the delay in getting back to you!

  2. Edilberto S. Forero

    If you use for instance a N x 3 (items for rows x columns in page) and for each column you use a tablix using the visibility for the group based on the restriction:
    =IIF(ROWNUMBER(“DataSet1”) MOD 3=1,FALSE, TRUE)
    =IIF(ROWNUMBER(“DataSet1”) MOD 3=2,FALSE, TRUE)
    =IIF(ROWNUMBER(“DataSet1”) MOD 3=0,FALSE, TRUE)

    And include some borders the result will work really fine, works great for me in order to generate the labels for a books in my work.

  3. Paul Rohorzka

    Thanks a lot for posting about this neat and simple approach. It helped me get rid of some nastily convoluted SQL.

  4. Alex

    Thank you so much! You save my life =)

  5. Greg

    This saved me a lot of time and trouble. Thanks.

    • Hi Minilil, I’m not entirely sure what you mean by saying “with charts”. Do you mean having charts displayed in the tables, i.e. a chart per row/cell? Happy to help if I can!

  6. Hi Graham, just a small hint from my side, you don’t the IIF clause as your comparsion with “=” return already true or false …

    NOT (ROWNUMBER(“DataSet1″) MOD 3=0)
    is enough …

    altough many many thanks

    • Hey Timo, thanks for the hint, that’s a really good suggestion to simplify the expression!

  7. How would you do apply this technique to a group. For example a manager as the group and the managers reports as the detail. How do you get the columns to alternate by the manager while still including the list of reports under each manager.

    • Hi Wayne, apologies for the delay in responding. Did you ever get anywhere with this?

      I’d approach this as standard a matrix, with the manager as Column Groups, and the reports as row detail. This should let you produce a multi-column “table” that you can then style as needed.

Next ArticleKlout online influence scores gaining extra sway