What's your preferred method for generating datasets in SSRS?

Question

I am trying to figure out what is the 'best' (read: "your preferred method") way to generate datasets for SQL Server Reporting Services Reports (either 2005/2008):

• In-report queries
• Stored procedures
• Views

But more than just choosing one of the above, why would you use that particular method? Also, please include your perspective (Developer/DBA/etc).

Thanks.

Answer 1

Stored procedures. I tend to write stored procedures for both parameters and the report data. By using stored procedures for parameter datasets they can be easily shared between reports. For report data I like to make certain that I draw a clear line between the data within a report and the formatting that comes out. By keeping that line it has been easier in my experience to test and promote reports to production.

Also, I find stored procedures a little easier to manage and troubleshoot than a view or in-report queries.

Answer 2

You should not bother with in-report queries, they are pretty much there to play with and practice. But you can hardly get a very good report writing a query directly in RS. Why..well for one thing a lot of sprocs can be reused not only for the application side but also the reporting side. Your application may have various stored procedures used to fill drop down lists / combo boxes that cascade (one relates on another). You may need this functionality for your reports as well. With stored procedures you could issue a call to the sproc from your report or your application. When the sproc changes (if it ever has to), you're ok because the updates you made to the application stored procedure also update your report's stored procedures.

My vote is definately for stored procedures.

Answer 3

I have been working with MS Reporting Services for about one full year now. I found that the best way to generate reports with this system is to run queries from your data access layer, via stored procedures.

If find that if you do it this way, you have all your returned datasets in one spot. It makes it easier to manage. All your database output is controlled from the same location.

Off topic but I would also recommend that you generate your RDLC files in memory. We have about 100 different report types. Instead of managing a bunch of RDLC files, we manage a ReportEngine class. The ReportEngine class basically generates a bunch of different report types. This is quite advanced, but the results are worth it. Source code to generate a RDLC file with a table: C# or VB.NET.