SSRS 2008 Report SQL options

Question

Hi,

I would like some guidance on what is the best approach for storing the Report SQL definitions for SSRS 2008 reports.

[Our web app will interface with the SSRS web service and display the reports in the VS ReportViewer control]

Currently I see 3 options

1. Store the SQL within the RDL [Report Definition] files.
2. Store the SQL in Stored Procedure/View [1 SPROC per report/parent report]
3. Store the SQL for all the reports in a single Reports table that has a ReportNameORGUID and ReportSQL columns at minimum.

For the purpose of the question the report designers will [initially] be our own support staff that have a a 'working' knowledge of SQL. The aim of the development group however is to retain tight control over the database standards and schema.

Option 1 allows for our own report designers to easily create and update reports but the SQL is distributed across all the reports and could be hard to control, maintain and update.

Option 2 will have performance benefits but the number of SPROCS could increase to a large amount and there is a requirement for the report designers to have the ability to create SPROCS and there is then the question of SQL standards being enforced in the SPROCS

Option 3 is attractive from a control point of view and we could provide a simple admin screen for designers to update the SQL to the Reports table but there is then the question of performance.

I would appreciate any input on addition merits or otherwise for the 3 options above and of course corrections to the assumptions I have made. If there are other options available it would be good to know.

The preferred solution from a DBA pov is detailed here http://sqlserverpedia.com/blog/sql-server-bloggers/ssrs-should-i-use-embedded-tsql-or-a-stored-procedure/

Thanks in advance,

Liam

Answer 1

I usually go with Approach #1. My SQL is embedded inside the report itself.

That keeps it consistent for me regardless of the Query Language - TSQL, MDX, PL/SQL, etc.