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

Question

Hey all,

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

I hope you find a solution, but consider that most of the solutions will be based on SQL Server 2005 or 2008.

SQL Server 2000 is about to have a tenth anniversary! I think that's about 100 in computer years.

Answer 2

You should be able to do this with a matrix. I've not used SSRS 2000, though, so I'm not sure of any limitations. Here is something from MSDN going over it though: http://msdn.microsoft.com/en-us/library/ms157334.aspx

Answer 3

I've had the need to do this in the past and the conclusion I came to is "you can't", however I'm not positive about that. If you find a solution, I'd love to hear about it.

An issue that comes to mind is that you need to define the report using the names of the columns that you're going to get back from the stored proc, and if you don't know those names or how many there are, how can you define the report?

The only idea I had on how to do this is to dynamically create the report definition (.rdl file) via C#, but at the time, I wasn't able to find an MS API for doing so, and I doubt one exists now. I found an open source one, but I didn't pursue that route.

Answer 4

I had a similar problem in one of my projects and I solved in the following way...

I created the report (RDL file) so that in contained those columns that would always appear on the report. It was only at runtime (when user requested the report) when I could know how many columns should be displayed. Typically I had to add several columns with titles and values that I couldn't predict (that was dependent on the users' input).

So at runtime, before rendering the report I modified its content - read and changed the RDL/RDLC file and invoked its rendering only then.

I made it from within C# application but it should be possible using any languages that has support for SSRS.

The full description how to dynamically add new columns to SSRS reports from C# can be found there.

Answer 5

I need to do the same on SQL 2005 SSRS. But I am yet to find a good article that I can follow to implement this. Can someone provide me good article or some tips that I can work on ?

Answer 6

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.