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 tablixes), 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
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
etc. Right? Right.
Implementation
The actual work is fairly simple. We add 3 tablixes 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 2 = X, False, True) — where 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
Hiddenproperty, not “Visible”. I know, it seems backwards, but that’s how it is.
In plain English, we mean, “if the row is the 1st row, show it, else hide it”. Likewise, for the second tablix‘s row, we’d say “if the row is the 2nd row show it”. And so on.
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.
