Quickie: SSRS Multi-Column List/Grid

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
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 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 N = X, False, True) — where N is the # of sections (tablixes) 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 (tablixes), 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.