Home > SSRS Expert > SSRS #44 – More on Using Matrix to Grow Data Columns Vertically

SSRS #44 – More on Using Matrix to Grow Data Columns Vertically

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.

image

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.

image

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).

image

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.

image

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().

image

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: