How to add a dynamically pivot table result to SSRS Report?

pivot, reporting-services, sql, t-sql

Solution

First of all - you can use the Matrix in SSRS 2005 (or Tablix in later versions) which will give you what you want. The problem you have, however, is that matrices work better with stuff in a vertical format. So in your case you need to query like so:

SELECT Area, 'A_Percent' as Type, A_Percent as Val FROM YourTable
UNION ALL
SELECT Area, 'B_Percent' as Type, B_Percent as Val FROM YourTable
UNION ALL
SELECT Area, 'C_Percent' as Type, C_Percent as Val FROM YourTable
UNION ALL
SELECT Area, 'D_Percent' as Type, D_Percent as Val FROM YourTable

Then you should have a result set which looks more like this:

Area    Type       Value
Area_1    A_Percent  50
Area_2    A_Percent  42
Area_3    A_Percent  20
Area_1    B_Percent  12
Area_2    B_Percent  28
Area_3    B_Percent  16

Now you can use this in the Matrix control. Drop the Area field into the 'columns' group. Drop the Type field into the 'rows' group and drop the Value into the middle (which will turn into a SUM() expression)

All done :)

Problem

I'm new to Pivot & SSRS, I need to create a report which is something similar to Pivot Table. The report layout is as follow: ``` Area_1 Area_2 Area_3 Area_4 ... Area_N A_Percent B_Percent C_Percent D_Percent ``` Since the "Area_N" is dynamic, hence my table layout is as follow: ``` Area A_Percent B_Percent C_Percent D_Percent ---- --------- --------- --------- --------- Area_1 45 55 66 77 Area_2 22 33 11 55 Area_3 12 45 88 36 Area_4 67 23 37 28 ... Area_N 76 67 35 28 ``` So my question is: - How to create the Pivot table base on the above structure? - Can my SSRS Report reads data from the Pivot Table? Welcome all gurus comment. Thanks very much!

Original source