As an SSRS Developer there are many ways to accomplish the goals set before us, but some ways offer advantages others do not. Here are some of those better ways. When developing SSRS reports we should always try to abide by certain rules to help us provide the best architecture and value. There always are exceptions causing some of these rules to not apply, but they should not be the rule.
1. Use Stored Procedures in place of embedded T-SQL in the Data Set Object.
Many data sets for SSRS reports are retrieved using T-SQL Statements. It seems very logical to generate the T-SQL and embed it in the report. By embedding the T-SQL Statement, should any change be required to the statement itself, one would need to modify the actual report and redeploy regardless of the impact of the modification. If we chose to use a Stored Procedure, then the type of modification will determine whether the SSRS report would ever need to be touched.
Assume the T-SQL Statement is returning 5 columns of information using a series of calculations to generate these values and a request is made to modify one of those calculations.
Using Embedded T-SQL, the SSRS report would need to be modified to accommodate the change in the T-SQL Statement and redeployed.
Using the Stored Procedure approach, a modification can be made to the Stored Procedure and no changes necessary to the SSRS report.
2. Process as much business logic in database engine.
SSRS provides a robust expression language allowing developer to perform a variety of tasks. Each of these expressions being performed on the report can extend the render time of the report, thus making the report slow to return results. Examine the tasks being performed by the SSRS expressions and determine if any of them can be performed against the data set prior to being returned to the SSRS Report.
Assume the SSRS Report has a requirement whereby the evaluation of 3-4 different columns will dictate whether a 5th column’s value will be colored ‘RED’ or ‘BLUE’.
Using an SSRS Reports expressions may look like this:
=IIF(Fields!Column1 = ‘YES’, “RED”, IIF (Fields!Column2 = “NO” && Fields!Column3 = “YES”, “RED”, IIF(Fields!Column3 = “MAYBE” && Fields!Column4 = “WHY NOT”, “RED”, “BLUE”)))
Pushing this logic to the Data Engine by using Case Statements to generate a new Column named “TextColor”, the SSRS expression would look like this:
=IIF(Fields!TextColor = “1”, “RED”, “BLUE”)
Keep in mind that ALL expressions at the detail level of the data sets are executed for each row in the data set. The more complex the expression, the more time is spent attempting to render the report.
3. Use Shared Data Sources instead of Local Data Sources.
Often times, report developers are developing a collection of reports potentially using the same data source. It is helpful to use a Shared Data Source to provide flexibility and global configurability to the collection of reports. By changing the Share Data Source all references made throughout the collection will inherit the change. This is a quick way to move reports from a Development Data Source to a Production Data Source by only changing one connection string.
4. Use Data Sets instead of Manual Lists for Parameter Values.
Parameters are a very useful way of providing interactivity with the report’s contents. Many lists are the output of T-SQL statement executed against a data source, but other times these lists may be a collection of manually entered values, not existing in a database. It is very tempting to manually create the list within the Parameter object itself, but don’t. As with Stored Procedures vs. Embedded T-SQL Statements, in the event of adding new values, the SSRS report would need to be modified and redeployed. By creating a Stored Procedure providing this manual list of values, the list values can be changed without having to change the report and more than one report can leverage the same list.
5. Use Shared Data Sets to load parameter values where possible.
Many times, a collection of reports being developed may use the same Parameter lists. In these cases, we should use Shared Data Sets and reference them in the individual reports. With this approach, the parameter lists values can be cached on the SSRS Instance and refreshed at an interval, thus reducing the render time of the report. Plus these refreshed data will be available for all reports using the Shared Data Source.
Written By: Warren Sifre