CODEWITHSUNDEEP
reporting-services
Harel Moshe
Harel Moshe

Reputation: 454

SSRS 2012 report with changing row group and column groups

i have the following table in the database:

    A    B    C    D
    ----------------
    A1   B1   C1   D1
    A2   B2   C2   D2
    A3   B3   C3   D3
    ...
    ...

I want to allow the user to generate a report in which he can choose two table-columns, and use them as X and Y axes - i,e, get the count of records having the corresponding values as the details in the matrix

in this example, the user chose column A (having values A1, A2 and A3 in the database) and C (havint values C1, C2, C3 in the database) as X and Y, respectively:

     C1   C2   C3
-----------------
A1 | 100  43   232
A2 | 232  12   23
A3 | 124  23   3434

in this example, the user chose B and D as X and Y, respectively:

     D1   D2   
--------------
B1 | 9    3   
B2 | 88   12   
B3 | 53   23   
B4 | 1    3

where the values in the title are distinct values of the columns A,B,C,D respectively.

I can't figure out if this should be done by changing the data-source, the matrix itself, or maybe it's a complex problem that should be solved using a cube.

How would you implement this?

Thanks, Harel

Upvotes: 1

Views: 1346

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

To solve this at the report level, you can set up expression-based Groups that will allow this to occur.

I created an example based on the following data:

enter image description here

I set up two parameters to control which columns to use at the row and column level:

enter image description here

For the row and column groups I used the following expression to group on, and to display in the headers.

Row:

=Switch(Parameters!RowGroup.Value = "A", Fields!A.Value
    , Parameters!RowGroup.Value = "B", Fields!B.Value
    , Parameters!RowGroup.Value = "C", Fields!C.Value
    , Parameters!RowGroup.Value = "D", Fields!D.Value
    )

Column:

=Switch(Parameters!ColumnGroup.Value = "A", Fields!A.Value
    , Parameters!ColumnGroup.Value = "B", Fields!B.Value
    , Parameters!ColumnGroup.Value = "C", Fields!C.Value
    , Parameters!ColumnGroup.Value = "D", Fields!D.Value
    )

Finally, just add CountRows() to the detail Textbox to display the entity count. Report looks OK:

enter image description here enter image description here

So not too bad to do at the report level. Hopefully this gives a rough idea of what's involved and whether you should do it at the Dataset level instead.

Upvotes: 1

Related Questions