Today I present to you, a hopefully less confusing and more intuitive explanation of how to make a “multi-column list” control on your SSRS report. And if the term is confusing by itself, I’ll illustrate in a second.
Three links that helped me get going in the right direction:
- https://stackoverflow.com/questions/42936806/ssrs-multi-column-list-report-how-to-create-from-one-dataset
- https://www.experts-exchange.com/articles/12331/Simple-way-to-show-multi-column-data-in-SSRS-Horizontally-or-Vertically.html
- https://picnicerror.net/development/sql-server/create-multi-column-lists-sql-server-reporting-services-ssrs-2011-10-03/
They all roughly boil down to the same concept: placing redundant or repetitive controls (like a set of 2 or 3 tablix
es), and using a RowNumber()
expression with some modulo arithmetic to set “visibility” properties of the dataset rows shown in each.
We don’t quite get to “dynamic # of columns and rows based on the total # of records” nirvana (e.g. make it a 3×5 grid if you have somewhere around 15 records, but only make it 2×4 if you have 8 or less records, etc.), but I’d estimate that this approach would suffice in 90% of use-cases. And whoever at Microsoft decided to not build a control like this into the toolset, curse you!!
Yes, we’re not even past the intro section and I’ve already cursed someone at MSFT. Doesn’t change my passion for the platform in general; it’s just a minor curse. =)
Concepts
SSRS uses a dataset
to fill one or more controls
with data; controls such as the tablix
. In my regular implementations, a dataset
is usually a stored-proc. The tablix
shows the results of the proc in tabular form. The problem with the tablix
control is that you can’t turn it into a multi-column layout, as you might see with some more advanced UI controls. You might think that the matrix
control would be suitable for this, but no; that is just a table-like structure that can support hierarchical column-groups and row-groups.
For example, let’s say you have a table of Users, with columns ID
, Name
, and Role
. Like so:
ID | Name | Role ---|---------|-------- 1 | Bob | Admin 2 | Alice | User 3 | Jack | User 4 | Jill | User 5 | Joe | Admin
But with many more records. We want to display this on a report, which is typically viewed on a PC screen, i.e. landscape orientation (wider than tall). So wouldn’t it be nice if we could display it like so?
ID | Name | Role || ID | Name | Role || ID | Name | Role ---|-------|-------||----|-------|-------||----|------|------ 1 | Bob | Admin || 2 | Alice | User || 3 | Jack | User 4 | Jill | User || 5 | Joe | Admin ||
etc. Right? Right. So essentially what we want is 3 “sections” of data — 3 “columns” each with its own “part” of the table.
Bonus: Yes, this works even when the # of rows you have is not evenly divisible by the # of sections you want to break into!
Implementation
The actual work is fairly simple. We add 3 tablix
es to the report and place them side-by-side. We connect them all the same dataset
, i.e. stored-proc — which will only be executed once, conveniently! On the data row (as opposed to the header row) of each tablix
, right click to Row Visibility
. Then use an expression like so:
=IIf(RowNumber(Nothing) Mod N = X, False, True)
— where N
is the # of sections (tablix
es) you want, and X
is going to vary from 1 to (# of columns) - 1
, and finally to 0
for the last (right-most) tablix
.
Remember, the expression defines the row’s
Hidden
property, not “Visible”. I know, it seems backwards, but that’s how it is.
In plain English, and if we just want 3 sections (tablix
es), we could say the following. For Section 1, left-most: “If the row is the 1st row, show it, else hide it”. Likewise, for the Section 2, the middle, we’d say: “if the row is the 2nd row, show it.” And finally, for Section 3, the left-most: “If the row is the 3rd row, show it.” And so on.
So those Hidden
expressions would look like this:
=IIf(RowNumber(Nothing) Mod 3 = 1, False, True) //section 1 =IIf(RowNumber(Nothing) Mod 3 = 2, False, True) //section 2 =IIf(RowNumber(Nothing) Mod 3 = 0, False, True) //section 3
Not bad, right?
Final Thoughts
It’s certainly odd that there’s no native way of building such a report. But with a few simple tricks and visual slight-of-hand, we can fake it.

could you explain this =IIf(RowNumber(Nothing) Mod 2 = X, False, True) for the X. so for tablix 1 would be what 1? and tablix 2 what would it be? and finally tablix 3 would be 0? please explain
LikeLike
Hi Coder! My apologies, I actually got that wrong. ‘X’ is one part, but the Modulus factor is also variable depending on how many columns (tablixes) you have. I will edit the post to reflect this. Thank you for your comment! 🙂
LikeLike
Thank you! I’ll wait for the updated version 🙂
LikeLike
Edited and published! Hope you find it useful ❤
LikeLike