SSRS Report from a PIVOT query with unknown number of columns

Question

I have a PIVOT query that is returning an unknown number of dynamic columns. So the query will return something like

ID | Col1 | Col2 | ..... | ColN

ID is the only static column and the rest are all dynamic. The number and the names of the columns are unknown.

How can I write a report in SSRS that can handle this? Any tips and direction will be appreciated.

Thanks.

rschoenbach rschoenbach · Accepted Answer · 2018-11-21T16:25:33

SSRS will not be able to handle your pivot table because the columns are not known at design time. SSRS has feature called Matrix that will handle the pivot a run time based off run time data if your design time structure is static.

ID | ColumnName | Value
------------------------
1  | Colu1      | Value1
1  | Colu2      | Value2
2  | Colu2      | Value2

I would recommend that you return detail rows from TSQL and allow SSRS to handle the pivot. This will allow for one stored procedure to be used for multiple SSRS reports and aggregations instead of the single aggregation provide by PIVOT.

To save on duplicate processing, Matrix will also handle the sorting so no need to sort inside of TSQL.

Reference: Create a Matrix

SSRS Report from a PIVOT query with unknown number of columns

1 Answers