Share via

Improve the Performance of Reporting Services Parameterized Reports

  • Article

Summary

If you've ever developed SQL Server Reporting Services 2008 R2 reports with more than a just few parameters then you may be aware that those reports may take a while to load.  This post will reviel a few tricks and work-arounds I've discovered while delivering similar reports.  The information reflects not only my own experience, but the collective experience of SSRS product group members, and other consultants at Microsoft.

Problem Statement

SQL Server 2008 R2 Reporting Services reports experience performance degradation as greater numbers of parameters are added to the report design.  The degradation appears to center around the rendering of the parameter display panel wherein the default “cascading” behavior of the parameter panel appears to play a major part in the unacceptable performance. What’s more, the performance degradation appears to occur on the client machine rather than on the server. During report execution resource consumption on the data source server barely registers with the results of queries returning instantaneously.  However, on the client machine CPU consumption spikes dramatically.

Solution Alternatives

Server Configuration:

  • Use Cumulative Update 7 (CU7) for the RS Add-in for SharePoint: It has been observed that the best performing configuration for SharePoint/Reporting Service integration indicates the use of Cumulative Update 7 (CU7) for the Reporting Services Add-in for SharePoint.  Any subsequent attempt to upgrade RS Add-in to Service Pack 1 (SP1) results in the return of the unacceptable performance degradation.
  • Ensure the use of a 64-bit platform:  Installing SSRS on 64-bit server platforms enable the access of far greater amounts of memory than 32-bit platforms.  Memory in SSRS 2008 is used more efficiently at the data structure level. Under intensive workload pressures, it uses a file system cache in order to reduce memory utilization. By being more efficient and writing to disk, it can process more report executions (even large reports) successfully. The administrator uses settings to determine when SQL Server 2008 Reporting Services starts to use the file system cache and how aggressively the cache is used. The administrator should consider configuring memory settings for the SQL Server 2008 Reporting Services to optimally use their computer resources. Fine-tuning these settings can provide an increase in performance under intensive workload over the default configuration.
  • Use cache execution wherever reports do not require live execution. By enabling the cache execution setting, a temporary copy of the report is cached in memory; thus, preventing the re-execution of report queries each subsequent time the report is executed.
  • Scheduled Execution can be used to cause a report execution snapshot to be stored in the RS database.  This can be scheduled for non-peak hours that remain synchronized with the refresh interval of the data source.  This ensures that unnecessary report executions do not occur by using the report snapshot to render the report for the calling user rather than the report itself.
  • Prepopulate the Report Cache by Using Data-Driven Subscriptions for Parameterized Reports: For large parameterized reports, performance is improved by prepopulating the report cache using data-driven subscriptions. Data-driven subscriptions enable easier population of the cache for set combinations of parameter values that are frequently used when the parameterized report is executed. Note that if you choose a set of parameters that are not used, you take on the cost of running the cache with little value in return. Therefore, to identify the more frequent parameter value combinations, analyze the ExecutionLog2 view. Ultimately, when a user opens a report, the report server can use a cached copy of the report instead of creating the report on demand.

Report Design:

  • Control the size of reports by designing them with fewer numbers of parameters.
  • Cache the results of parameter dataset queries in the form of text-strings on the RS server’s local file system, and then pass those text strings to populate the UI controls rather than re-executing queries.  This should reduce the amount of data being passes across the network.
  • Consider developing an ASP.NET Report Display Page featuring its own parameter control panel that can be optimized to handle parameter datasets and values using finer-grained controls than are available via the Reporting Services ReportView control.

How to Disable the "Cascading" Behavior of Multiple Parameters

When designing reports with multiple parameters the default behavior is to create a dependency between each of the parameters.  This causes each parameter to be refreshed when any of the other parameters is configured at run-time.  This causes each parameters dataset to be refreshed; thus causing additional latency.  This behavior should be disabled for any parameters that do not actually require filtering by the other report parameters.

  1. Double-click the dataset for the parameter under the "Datasets" folder in the "Report Data" pane to open the "Dataset Properties" dialog.
  2. Next select the "Parameters" item in the left-hand pane then select each parameter in the list and click the "Delete" button to remove the parameter.
  3. Select the "Query" item in the left-hand pane, and then click the "Query Designer..." button to open the query designer dialog.
  4. Delete the references to the parameters that are present in the "FROM" clause of the MDX query.
  5. Execute the query to verify that it returns the appropriate values.
  6. Click "OK" to close the query designer and close the dialog.

This should remove dependencies between parameters; thus disabling the "cascading" behavior.

How to Replace a Date Dimension DropDownList Parameter Control with a DateTime Picker

  1. Double-click the Date dimension Parameter under the "Parameters" folder in the "Report Data" pane to open the "Report Parameter Properties" dialog.
  2. On the "General" tab change the data type for the parameter to Date/Time in the "Data type:" dropdown list.
  3. Click the "Available Values" item in the left-hand pane and select the "None" radio button in the "Choose the available values for this parameter" dialog.
  4. Click the "Default Values" item in the left-hand pane, click the "Specify values" radio button, and then click the "Add" button to create a default value.  Next click the  expression button next to the new default value, and type an expression that resolves to a valid DateTime value; e.g., "=Today", or "=DateAdd(DateInterval.Day, -Today.Day, Today)".
  5. Next, double-click the dataset for the report body under the "Datasets" folder in the "Report Data" pane to open the "Dataset Properties" dialog.
  6. Click the expression button next to the "Parameter Value" dropdown list for the DateTime parameter whose value you need to specify, and then enter an expression that uses the parameters' DateTime value to concatenate a string that resolves to a valid member expression for the Date dimension that is specified in the the MDX query for the dataset.

    1. From Date – Click the expression button next to the appropriate parameter and enter an expression in the following format

      =“[Start Date].[FriendlyDate].&[“ + CDate(Parameters!StartDate.Value).Year.ToString() + “]&[“ + IIF(CDate(Parameters!StartDate.Value).Month <= 6, “1", “2") + “]&[“ + DatePart(DateInterval.Quarter, CDate(Parameters!StartDate.Value)).ToString() + “]&[“ + CDate(Parameters!StartDate.Value).Month,ToString() + “]&[“ + Format(CDate(Parameters!StartDate.Value), “s”) + “]”

       

    2. To Date – Click the expression button next to the appropriate parameter and enter an expression in the following format

      =“[End Date].[FriendlyDate].&[“ + CDate(Parameters!EndDate.Value).Year.ToString() + “]&[“ + IIF(CDate(Parameters!EndDate.Value).Month <= 6, “1", “2") + “]&[“ + DatePart(DateInterval.Quarter, CDate(Parameters!EndDate.Value)).ToString() + “]&[“ + CDate(Parameters!EndDate.Value).Month,ToString() + “]&[“ + Format(CDate(Parameters!EndDate.Value), “s”) + “]”

Notice that the only difference between the two expressions is the parameter value being used; the from date uses Parameters!StartDate and the to date uses Parameters!EndDate. Remember that these values must reflect the names being used in your OLAP database since Date and Time dimensions often serve as role-playing dimensions and may have a very different name. The purpose is to build a string representation of a dimension expression that may resemble the following format:

[End Date].[FriendlyDate].[Year].[Semester].[Quarter].[Month].[2011-11-22T00:00:00]:

The italicized values need to be resolved using the values supplied by the Date/Time picker control in the Reporting Services Report Viewer user interface.

  • To derive the Year:
    • CDate(Parameters!ParamName.Value).Year.ToString()
  • To derive the Semester:
    • IIF(CDate(Parameters!ParamName.Value).Month <= 6, “1", “2")
  • To derive the Quarter:
    • DatePart(DateInterval.Quarter, CDate(Parameters!ParamName.Value)).ToString()
  • To derive the Month:
    • CDate(Parameters!ParamName.Value).Month,ToString()
  • To derive the Member Date:
    • Format(CDate(Parameters!ParamName.Value), “s”)

 How to Replace a Boolean Dimension Attribute DropDownList Parameter Control with a Checkbox Control

  1. Double-click the parameter under the "Parameters" folder in the "Report Data" pane to open the "Report Parameter Properties" dialog.
  2. On the "General" tab change the data type for the parameter to Boolean in the "Data type:" dropdown list, and then check the "Allow null value" checkbox.
  3. Click the "Available Values" item in the left-hand pane and select the "None" radio button in the "Choose the available values for this parameter" dialog.
  4. Click the "Default Values" item in the left-hand pane, and then click the "No default value" radio button.
  5. Click the "Advanced" item in the left-hand pane, and then click the "Never refresh" radio button.
  6. Next, double-click the dataset for the report body under the "Datasets" folder in the "Report Data" pane to open the "Dataset Properties" dialog.
  7. Click the expression button next to the "Parameter Value" dropdown list for the boolrsn parameter whose value you need to specify, and then enter an expression that uses the parameters' value to concatenate a string that resolves to a valid member expression for the dimension that is specified in the the MDX query for the dataset.
    =”[Dimension Name].[Attribute Name]” + IIF(IsNothing(Parameters!ParameterName.Value), “.[All Members]”, IIF(CBool(Parameters!ParameterName), “.&[Yes]”, “.&[No]”))

 

How to Replace a DropDownList Parameter Control with a Textbox Control

  1. Double-click the parameter under the "Parameters" folder in the "Report Data" pane to open the "Report Parameter Properties" dialog.

  2. On the "General" tab change the data type for the parameter to Text in the "Data type:" dropdown list, and then check the "Allow blank value" checkbox.

  3. Click the "Available Values" item in the left-hand pane and select the "None" radio button in the "Choose the available values for this parameter" dialog.

  4. Click the "Default Values" item in the left-hand pane, and then click the "No default value" radio button.

  5. Click the "Advanced" item in the left-hand pane, and then click the "Never refresh" radio button.

  6. Next, double-click the dataset for the report body under the "Datasets" folder in the "Report Data" pane to open the "Dataset Properties" dialog.

  7. Click the expression button next to the "Parameter Value" dropdown list for the boolrsn parameter whose value you need to specify, and then enter an expression that uses the parameters' value to concatenate a string that resolves to a valid member expression for the dimension that is specified in the the MDX query for the dataset.

    ="[Dimension Name].[Attribute Name]” + IIF(Parameters!ParameterName.Value.ToString() = "", “.[All Members]”, ".&[" + Parameters!ParameterName. Value.ToString() + “]”)