Archive

Posts Tagged ‘matrix’

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

April 28, 2011 Leave a comment

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.

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

March 9, 2011 7 comments

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: ,
%d bloggers like this: