My previous blog SSRS – Repeat Details Grouping to show data columns vertically shows how to grow data columns vertically on a Table with repeating the Details Grouping.

Just when I thought I am done, the business user sent me another request to change the report layout to show data rows horizontally, instead of vertically.

There are some blogs talking about how to dynamically change the number of columns to display in SSRS horizontally. One I read talked about how to change the RDL/XML file during runtime; another one talked about how to use expressions in the Invisibility property. I understand that they were all trying to avoid changing the underneath data structure and were trying to use Table rather Matrix. In my opinion, all of them are pretty misleading without giving the Matrix a try.

Matrix in SSRS is designed to allow your report to grow horizontally as your the number of rows grows.

Here is the format the business user wants.

To create a report with columns shown vertically, but data rows displayed horizontally, I need to use a Matrix, not a Table. I only have one default Column Grouping that has ID as the grouping Expression, but LOAN NUMBER in the column textbox. ID is the PK for the underneath SQL table, but I don’t want to show it.

As for the Rows grouping, I do not even need to create one. All I need is the default Row, and keep adding Row till I have enough Rows for all the columns for my underneath SQL table.

To simply just Add Row, not to create a Row Grouping, make sure you right click the data cell, not the row heading

or the entire row

With the above approach, I do not need to change anything in my underneath SQL table, nor do I need to write any Expression dynamically. Especially I do not need to modify my RDL/XML file during runtime.

One more thing before I close this blog. Because I am using a Matrix, which was designed primarily for data aggregation, the report designer will automatically aggregate the data for you when you drop a field in the data cell.

Just simply remove the aggregation function.