In my previous post, SSRS #35 – Use Matrix to Grow Data Columns Vertically and Data Rows Horizontally, I mentioned that you do not need to create any row grouping in order to grow your data columns vertically. What you need to do is to simply add rows (each row represents one column from your SQL table) to the matrix.
One reader asked me how to just add rows. I am trying to make it more clearly in this post.
Here is what the default matrix looks like when you drop it in your data region.
There are three noticeable sensitive cells, i.e. Columns, Rows, and Data. If you right click on either Columns or Rows, you will get options that will allow you to manipulate groupings, such as Insert Group, Edit Group etc.
If you right click on the Data cell, you will get different options, which allow you to simply add columns or rows (no grouping is required).
Bu choosing Add row option, you will be able to add rows to the matrix, with each row represent one column from your SQL table.
In this example, I had two rows. I dropped my columns to the Data cell. The above shows the default behavior.
Since I do not need to aggregate my data, I will remove the aggregation function First().
Before I close this post, I want to add a side note about the context of this post.
The Context for this post
- You SQL table holds the “raw” data, meaning that the data is not summarized/aggregated.
- You don’t want to summarize/aggregate your data for the report.
- The pair of each matrix row and SQL column is hard-coded, meaning that the number of rows in the matrix will not grow automatically (when your SQL table has new columns).
I recently had a conversation with a co-worker. She needed to modify a report that has about dozen matrixes, and each one shows summarized data, but with the pair of each matrix row and SQL column being hard-coded. Now she needs to add one more measure to each matrix. She is very experienced, and immediately understood that she has two choices here. One is to continue to hard-code the pair of each matrix row and SQL column. Another one is to change both the matrix design and the base SQL table so that the matrix rows will grow dynamically in the future.
Perhaps I will blog about the choice # 2 in the future.
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.