reporting services - How do i represent an unknown number of columns in SSRS?

Question

I'm working on a rather complex report in Sql Server Reporting Services. My SP returns a dynamic number of columns each of which are dynamically named.

Basically think of a time keeping application. Each column that is dynamic represents a time bucket that time was charged to for that team. If no time was charged to that bucket for the period of time the report covers it doesn't show. Each bucket has its own identifier which i need to be the column headers.

I have an SP that returns this all. It does it by doing a bit of dynamic SQL with an exec statement (ugly i know but I'm on SQL 2000 so a PIVOT option wouldn't work)

I can have an indefinite number of buckets and any or all might show.

I found this - http://www.codeproject.com/KB/reporting-services/DynamicReport.aspx - which is helpful but in the example he has a finite number of columns and he just hides or shows them according to which ones have values. In my case i have a variable number of columns so somehow i need the report to add columns.

Any thoughts?

Answer 1

As long as you know a maximum number of columns, it's possible to do this after a fashion.

First, name the columns with a result from your query, so you can either pass it in to the query or derive it there. Second, just build out the report as if it had the maximum number of columns, and hide them if they are empty.

For example, I had to build a report that would report monthly sales numbers for up to a year, but the months weren't necessarily starting in January. I passed back the month name in one column, followed by the numbers for my report. On the .rdl, I built out 12 sets of columns, one for each possible month, and just used an expression to hide the column if it were empty. The result is the report appears to expand out to the number of columns needed.

Of course, it's not really dynamic in the sense that it can expand out as far as you need without knowing the upper bound.