SSRS 2005 Horizontal Matrix with dynamic rows

Question

I have a question regarding SSRS, the Matrix control and generating dynamic rows.

I currently have a dataset that gives a list of values broken down by week:

 Week | Value1| Value2 | Value3
 W1   | 1     | 2      | 3
 W2   | 4     | 5      | 6 

Which I display on a week-by-week breakdown on a report using a matrix control? Here the row headings static but the column headings are dynamically added from the dataset:

         | W1    | W2
 Value1  | 1     | 4
 Value2  | 2     | 5
 Value3  | 3     | 6

This part is fine however I now need to add another dimension as the final report needs to be split by a couple of variables. The new dataset will be something like this

 Week | VariableA | VariableB | Value1| Value2 | Value3
 W1   | ResultA   | Result1   | 1     | 1      | 1 
 W1   | ResultB   | Result1   | 0     | 1      | 1 
 W1   | ResultA   | Result2   | 0     | 0      | 1 
 W2   | ResultA   | Result1   | 1     | 2      | 5 
 W2   | ResultA   | Result2   | 3     | 3      | 1 

And I need to generate a report like so:

                | W1    | W2
 Value1 ResultA | 1     | 4
 Value1 ResultB | 0     | 0
 Value1 Result1 | 1     | 1
 Value1 Result2 | 0     | 3

Is it possible to generate dynamic rows in this way whilst I am also generating dynamic columns? I do not know what the values for the variables are going to be.

Thanks in advance and leave a comment if you need any more information.

Answer 1

You can get the result using SQL by applying the UNPIVOT and the PIVOT functions. The UNPIVOT will be applied twice, the first time to convert the multiple columns of value1, value2 into rows and then the second implementation will convert the variablea/variableb columns to rows.

The basic syntax will be:

select week, 
  col + ' ' + varcol1, value
from table1
unpivot
(
  value
  for col in (value1, value2, value3)
) u
unpivot
(
  var
  for c in (variablea, variableb)
) u1;

see SQL Fiddle with Demo. This gives a result similar to:

| WEEK |           COL1 | VALUE |
---------------------------------
|   W1 | Value1 ResultA |     1 |
|   W1 | Value1 Result1 |     1 |
|   W1 | Value2 ResultA |     1 |
|   W1 | Value2 Result1 |     1 |
|   W1 | Value3 ResultA |     1 |
|   W1 | Value3 Result1 |     1 |
|   W1 | Value1 ResultB |     0 |

Then you can apply the PIVOT function to convert the W1 and W2 values into columns:

select col1, W1, W2
from
(
  select week, 
    col + ' ' + var col1, value
  from table1
  unpivot
  (
    value
    for col in (value1, value2, value3)
  ) u
  unpivot
  (
    var
    for c in (variablea, variableb)
  ) u1
) d
pivot
(
  sum(value)
  for week in (W1, W2)
) p;

See SQL Fiddle with Demo. This converts the data to the result:

|           COL1 | W1 |     W2 |
--------------------------------
| Value1 Result1 |  1 |      1 |
| Value1 Result2 |  0 |      3 |
| Value1 ResultA |  1 |      4 |
| Value1 ResultB |  0 | (null) |
| Value2 Result1 |  2 |      2 |
| Value2 Result2 |  0 |      3 |
| Value2 ResultA |  1 |      5 |
| Value2 ResultB |  1 | (null) |
| Value3 Result1 |  2 |      5 |
| Value3 Result2 |  1 |      1 |
| Value3 ResultA |  2 |      6 |
| Value3 ResultB |  1 | (null) |

This could be converted to dynamic SQL which would need to be placed in a stored procedure.