How to use the new TSQL PIVOT operator with reporting services?

Hi,

I am still tryng to find a solution to this problem.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125123&SiteID=1

Assuming that Matrix will not work for me, I thought that may be there would be a solution by using a standard report of some kind and the new PIVOT operator.

I do not know in advance the number of rows and columns and I do want the report formatted as a flat file.

Any idea?

Thanks

Philippe

Hi,

I guess I have a solution that would work.

First tests are OK but this will require more testing because this is at the edge. The complete code solution may be too much for a post here so I will keep it at the idea level. Too bad I do not have a blog.

1) Create a dataset that generates the maximum amount of columns you expect to see in your report. Use strong naming convention to accomodate various scenarios.

2) Create a second dataset called "Columns" that counts the number of column that are produced by the PIVOT operator

3) Create a report design where all possible columns are included and use each column's Visibility Hidden property to store something like

= iif( CountRows("Columns") <4, True, False)

Increase the <value by 1 for each column

Use expressions to compute the column title, when the column is displayed, to show something meaningful like in my application, Week 1,2,3,4 or Month 1,2 or Quarter 1,2,3.

4) Replace the initial fake dataset by the final one which has the variable number of columns generated by your PIVOT operator. I use stored procedures so I can better manage the user selections, variable number of weeks or months or quarters. I need to display this accross columns so I can stuff more data in the "report". This is not really a report by the way.

5) For those of you who want to use the flat file in an Excel export for easy pivoting, If you need any items like a report title, make sure the text box containing this text is slightly wider than the maximum report width. This will avoid the appearance of blanks columns in the middle of your Excel doc. These blanks columns prevent creation of pivot tables.

Now you get a flat file type of report with variable number of columns.that users can export to Excel and easilly pivot.

Word of caution. Never refresh the fields list otherwhise the report will return an error saying that only fields present in the dataset can be used.

If someone has a better idea, I buy it immediately.

Regards,

Philippe