SQL Dynamic Pivot Query in SSRS 2005

0
votes

In my Sql Server Reporting Services (SSRS) 2005 report, I have a query that performs a Pivot and results in a dynamic column list. I closely followed the example in http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx.

Now, the problem is my query can return different columns and I don't know the names of those columns when I'm designing my SSRS report. The query runs fine under the Data tab of the report designer, but the layout designer does not know my column names and it won't let me assign fields to be displayed.

Is there a way to do this? can I dynamically layout the dataset fields in code? can I access data columns in the data set by position rather than name?

1
reporting-servicesreportingservices-2005
Can you post a screen shot of what you mean? You just need to create a cross tab report in RS, it doesnt matter how many columns you have or their names. That is the advantage, you lay it in the column group and it will dynamically add all columns irregardless of names. Need more details as to what you are asking and possibly some screen shots with arrows :). – JonH
Make that an answer and I'll accept it! You're right. I had a mental block and wrote an overly complicated T-SQL query to do the pivot. All I needed was a simple query and let the Matrix do the pivot for me. – Michael Levy
See my answer - always pivot and present at the front end rather then the back end. It makes it all the more easier. – JonH

1 Answers

1
votes

You really can take advantage of reporting services to leverage a column based cross tab report. Write the T-SQL in the db layer, then in the reporting layer drop and drop a cross tab report. In the column grouping just add the column you need and rs is awesome and smart enough to dynamically add all columns.

Here was my original comment :):

Can you post a screen shot of what you mean? You just need to create a cross tab report in RS, it doesnt matter how many columns you have or their names. That is the advantage, you lay it in the column group and it will dynamically add all columns irregardless of names. Need more details as to what you are asking and possibly some screen shots with arrows :).

One thing to always remember - don't make your database programming to difficult - presentation should mainly and almost always be done in the front end tool, in this case reporting services. Your database layer's code should be really simple standard T-SQL with maybe a grouping. The client will handle the presentation issues.