Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.