How do I dynamicaly create an SSRS Report?

Question

Hi guys, Im trying to create a report in SSRS. The report calls a stored procedure for its data. I want to display the data in a table. But the thing is, the result from the stored procedure differs from time to time, because every customer has its own 'template'. This means the result for customer A could be :

AccountNumber | CustomerID
1234567890           0987654321
1579086421           1234565465
......................          ....................

and for customer B could be:

CustomerName | Address
Customer B          Teststreet 1
Customer Test     Teststreet 2
......................      ....................

There are 50 different columns to choose from. The order of the columns is also editable. My stored procedure takes care of this. The only thing I want is to put the resultset of the storedprocedure 1:1 in my report (header+body). Do you guys know how to do this?

If thats not possible, is there a C# solution to this? I.e creating a report object in C#, adjust settings etc.

Thanks

Answer 1

You can create the SSRS report dynamically based on the data set returned by the stored procedure. The report format (RDL) is documented and its an XML format. So you can use System.XML namespace to generate RDL. Alternate (and unsupported) way is to refer RDL object model assembly (Microsoft.ReportingServices.RdlObjectModel) - you can locate the assembly on SSRS 2008 server machine and copy it on your local machine. It offers an object model to read/generate RDL.

I have adopted approach where RDL is generated (as XML) dynamically based on the data-table and then publish the RDL on SSRS server using web services API.

Answer 2

One solution might be to modify your SP so that the data returned looks something like:

ColNameA       DataA       ColNameB    DataB
AccountNumber, 1234567890, CustomerID, 948477586
AccountNumber, 5466584426, CustomerID, 458852244

Then, in SSRS drag out a table. Create a group on ColNameA. In that Group Row, place the Field ColNameA in the first Column, place ColNameB in the second column.

In the Details Row, place DataA in the first column, and DataB in the second column, and should look like this:

The sample query i used was:

select 'AccountNumber' as ColNameA, 1234567890 as DataA, 'CustomerID' as ColNameB, 0987654321 as DataB UNION 
select 'AccountNumber' as ColNameA, 5546488393 as DataA, 'CustomerID' as ColNameB, 4747599393 as DataB

Getting the names of the Columns (AccountNumber, CustomerID or CustomerName, CustomerAddress) will be the key. You can get them via:

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'my_table_name'