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

Question

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:

1. How to create the Pivot table base on the above structure?
2. Can my SSRS Report reads data from the Pivot Table?

Welcome all gurus comment. Thanks very much!

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 :)

Comments

[marcus.the.programmer]

Thanks Charleh! It worked just fine! Now I wanna update it a bit to add in Parameters into the query.