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.