Data source in SSRS

Stored Procedures | Views | Embedded Queries in SSRS

How to handle data access for an SSRS report requires many considerations. Is store procedure really required even for a simple SSRS report? Many takes position of doing store procedure for all data access. There is not a simple answer to this. Input and agreement from DBAs and developers could be helpful.

So the three variations are

• Store Procedure as data source
• SQL view as data source
• Embedded SQL in dataset

Store Procedure as data source

Having store procedure means that it can be used for more than one report, which results in easy maintenance of data source across many reports. It reduces the chances of errors. Store procedure reuse a query plan, also known as cache, for enhanced performance. DBAs prefer transparency, so centralization of queries is often preferred. This also permits security of the object, as needed, likes of Execute permission etc. and DBAs can tune the object making it more effective.

This also provides a Meta layer between source tables and what becomes available in reports. This means data is exposed to reports in more business like terms, rather than absolute technical terms that may not be as intuitive to report developers/business users. This provides that data source in much simplified for and also allows creation of calculated column, eliminating need of creating them in the report.

Change management is another advantage here by using system tables and columns in conjunction with data tables. And any minor change does not require redeployment of SSRS report.

On the flip side, it may require additional resource to create the store procedure as resource being used for report development may not have permission to create SQL objects. This goes with the fact that more in-depth knowledge of SQL and schema is required to create stored procedures.

This option also requires multi-object deployment, SSRS report file and related stored procedure. This could create more points of breakdown if deployment is not done concurrently. Same with testing, multi-step testing process is required to test all related objects, namely SSRS report and store procedure. Even when only stored procedure is change, however minor, still you need to test SSRS report to any inadvertent lapse.

Lastly, additional resources and handling is required for vast number of queries and what may be some redundant queries.

SQL view as data source

This is particularly helpful when exposing data source to end-users for ad-hoc reporting. This can create data source with business friendly terms. DBAs can handle security more effectively, securing the object as needed, as well as tuning the query.

Same view can be used for more than one report and this centralization is preferred by DBAs because such queries are more transparent to them. This also reduces chances of error and duplication.

Just like stored procedures, this method also provides a Meta layer between source tables and what becomes available in reports. This means data is exposed to reports in more business like terms, rather than absolute technical terms that may not be as intuitive to report developers/business users.

Also, just like in case of stored procedures, this require additional permissions when additional (outside) resources are used. This requires multi-objects deployment which could be prone to errors when not deployed concurrently. Similarly any changes also require multi-object testing.

View do have limitations, like can’t use temp tables within view, for one. Table joins don’t always work seamlessly in report development.

Proper coordination is required to manage changes, as a single view may be used by multiple reports. This also requires proper query reuse plan. Views can be used in conjunction with stored procedures and even embedded SQL.

Embedded SQL in dataset

It is easier and cleaner method, that required no special server side permissions. And only requires single-step deployment.

However, that query designer does not retain code formatting very well and removes in-line comments. For direct table access, additional permission would be required to database objects. It is also much more difficult to monitor SQL objects being accessed by embedded SQL. When reporting off large dataset, the SQL query may not perform very well as a stored procedure would. Any changes required, needs that report be opened in BIDS or report builder. Also, this method may lead to redundant queries which are handling similar logic in a different way.

In the end less critical reports or simple reports can utilize embedded queries for the sake of simplicity and quick turnaround. But for complex and critical reports stored procedures should be considered. Consider using views for simple ad-hoc reports.