Home > SSRS Expert > SSRS #35 – Use Matrix to Grow Data Columns Vertically and Data Rows Horizontally

SSRS #35 – Use Matrix to Grow Data Columns Vertically and Data Rows Horizontally

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.

clip_image002

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.

clip_image004

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.

clip_image006

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

clip_image008

or the entire row

clip_image010

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.

clip_image012

Just simply remove the aggregation function.

clip_image014

Categories: SSRS Expert Tags: ,
  1. Raghu
    April 28, 2011 at 11:14 am

    Hello Sherry,

    Just now i did see “Use Matrix to Grow Data Column…” post, many thanks for this valuable post.
    I am exactly facing same problem, my manager wants me to show Row data as column data and column data as Row data in the Matrix.
    I didn’t undersatnd that where where do i need to add rows, as you said i clicked (Right click)in the Data Cell, i didn’t find “Add row”option, if you don’t mind Can you please let me know bit claerly, you would be much appreciated for that. Thanks

    Like

  2. Brandon
    June 24, 2011 at 3:44 pm

    Not sure if you will respond, but I was curious if you could then repeat that first column (the column with the descriptions) on each page?

    Any help you could give me would be appreciated.

    Like

    • Sherry
      June 26, 2011 at 12:39 am

      By “repeat that first column (the column with the descriptions) on each page”, I guess that you meant if we can make the description column stay when we scroll to the right. I don’t have answer for that, because matrix does not give us option to let columns/rows stay as we scroll.

      Sherry

      Like

  3. kodster
    November 8, 2012 at 5:47 am

    Thanks Sherry, the post helped me to get started with horizontally dynamic report.

    Like

  1. April 28, 2011 at 5:46 pm
  2. April 28, 2011 at 6:28 pm

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: