CODEWITHSUNDEEP
sqlsql-serverreporting-services
zacker20
zacker20

Reputation: 25

Create an SSRS report that can have multiple matrices with different columns

I am attempting to develop a rather complex SSRS report and want to see if this is possible. Basically, I am trying to create a report that will have multiple pages and each page will have different columns (unknown number of columns, so the report has to be created dynamically).

I have got my dataset to look like this so I can create a matrix so the columns will be generated dynamically: Dataset Capture

That way the column_name can be displayed dynamically at the top of the matrix and I have row groups on row_id and page_id, to get the data under the correct column and to break on the next page.

But the problem I am running into is that when you get to the next page there could be different columns that I don't want to show on page 2. Those columns do not appear in the dataset for page 2 but - they will still show up on the report - just have blank data for them. See picture below: Report Capture

My question is: Is this possible to do with SSRS? I have a column SLA_ID in the dataset which basically is a report id. Could that SLA_ID be used to define the number and name of columns for each page? All the report definition data will be stored in the database that I am creating - I just can't figure out how to get SSRS to cooperate with this set of data.

Basically my end result is to have one RDL that can run the same type of report but the report can display different data/columns depending on the client selected. Thank you for any help you can provide!

Upvotes: 2

Views: 2769

Answers (2)

Tom
Tom

Reputation: 1373

You can easily add another matrix like this.

Step 1 : Go to column groups

Step 2 : Click on down arrow and add group

enter image description here

Upvotes: 0

niktrs
niktrs

Reputation: 10066

You can do this by nesting a matrix in a table.

Step 1: Create a table with only one group row on page_id and set it to page break.

Step 2: Create a matrix with row_id on row group, column_name on column group and column_value on values

You design would look like the image below enter image description here

Step 3: Insert the matrix inside the table group cell

enter image description here

Executing the report your result will look like the images below

enter image description here

enter image description here

Upvotes: 1

Related Questions