Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Maximizing Report Performance with Parameter-Driven Expressions
Learn about the inner workings of the SQL Server Reporting Services (SSRS) engine and how the report processor handles requests for reports.
William Vaughn
May 25, 2010
12 Min Read
Only static reports saved as pre-rendered images--snapshot reports--can be loaded and displayed (almost) instantly, so users are accustomed to some delay when they ask for reports that reflect current data. Some reports, however, can take much longer to generate than others. Complex or in-depth reports can take many hours to produce, even on powerful systems, while others can be built and rendered in a few seconds. Parameter-driven expressions, a technique that I expect is new to many of you, can aid you greatly in speeding up your reports.
If you're interested in a more general look at improving your report performance, check out this sidebar on other strategies. You can also download code for an example I created against the AdventureWorks2008 database.
The concepts I discuss here aren't dependent on any particular version of SQL Server Reporting Services (SSRS) but I’ll be using the 2008 version for the examples. Once you’ve installed the AdventureWorks2008 database, you’ll start Visual Studio 2008 and load the project ClientSide Filtering.sln. (This technique will work with Visual Studio 2005 BI projects, but I built the example report using Visual Studio 2008 and you can't load it in Visual Studio 2005 because the Report Definition Language—RDL—format is different.) Open Shared Data Source and the Project Properties to make sure the connection string points to your instance of SSRS.
The example report captures parameters from the user to focus the view on a specific class of bicycles, such as mountain bikes. Once the user chooses a specific bike from within the class, a subreport is generated to show details, including a photograph and other computed information. By splitting off the photo and computed information from the base query, you can help the report processor generate the base report much more quickly.
In this example, my primary goal is to help the user focus on a specific subset of the data—in other words, to help users view only information in which they are interested. You can do this several ways, but typically you either add a parameter-driven WHERE clause to the initial query or parameter-driven filters to the report data regions. I'll do the latter in this example.
Because the initial SELECT query executed by the report processor in this example doesn't include a WHERE clause, it makes sense to capture several parameters that the report processor can use to narrow the report’s focus. (There's nothing to stop you from further refining the initial SELECT to include parameter-driven WHERE clause filtering.) I'll set up some report parameters (as opposed to query parameters) to accomplish this goal.
In Visual Studio’s BIDS report designer, navigate to the report’s Design pane (as Figure 1 shows). Note that report-centric dialog boxes such as the Report Data window only appear when focus is set to the Design pane.
Figure 1: The report Design pane
Use the View menu to open the Report Data dialog box, which is new in the 2008 BIDS report designer. This box names each of the columns returned by the DataSet that’s referenced by (case-sensitive) name. If you add columns to the DataSet for some reason, make sure these changes are reflected here in the Report Data dialog box as well as on your report.
Don’t expect to be able to alter the RDL (such as renaming the DataSet) based on changes in the Report Data dialog. When you rename the query or change the columns being fetched, the designer doesn't keep in sync with the RDL very well. Be prepared to open the RDL to make changes from time-to-time.
Right-click the Parameters folder in the Report Data dialog and choose Add Parameter… to open the Report Parameter Properties dialog box, as Figure 2 shows. Here's where each of the parameters used in the filter expressions or the query parameters are defined. The query parameters should be generated automatically if your query references a parameter in the WHERE clause once the DataSet is bound to a data region on the report (such as a tablix report element).
Figure 2: The Report Parameter Properties dialog box
Use the Report Parameter Properties dialog box to name and define the prompt and other attributes for each of the report parameters you’ll need to filter the report. Figure 2 shows how I set the values, which are used to provide the user with a drop-down list of available Product Lines from which to choose.
Note that the Value setting is un-typed—you can’t specify a length or data type for these values. This can become an concern later on when you try to compare the supplied value with the data column values in the Filters expression I'm about to build, especially when the supplied parameter length doesn't match the length of the data value being tested.
I visited the Default Value tab of the Report Parameter Properties dialog to set the parameter default to M (for Mountain Bikes). If all parameters have default values set, the report processor doesn't wait before rendering the report when first invoked. This can be a problem if you can't determine a viable default parameter configuration that makes sense.
The next step is to get the report processor to filter the report data based on the parameter value. On the Report Designer Design pane, click anywhere on the Table1 tablix control and right-click the upper left corner of the column-chooser frame that appears. The trick is to make sure you’ve selected the correct element before you start searching for the properties. Be sure to choose the correct region when selecting a report element property page. It’s easy to get them confused.
Navigate to the Tablix Properties menu item, as Figure 3 shows. Here I'll add one or more parameter-driven filters to focus the report’s data. Start by clicking the Add button to create a new Filter.
Figure 3: Opening an element's properties
Ignore the Dataset column drop-down list because it will only lead to frustration. Just click on the fx expression button. This opens an expression editor where we’re going to build a Boolean-returning expression that tests for the filter values. It’s far easier to write your own expressions that resolve to True or False than to get the report processor to keep the data types of the left and right-hand side of the expressions straight. This is a big shortcoming, but there's a fairly easy workaround that I'm using for this example.
You can use the Expression editor dialog box, which Figure 4 shows, to build simple expressions or reference other, more complex expressions you’ve written as Visual Basic and embedded in the report. (The SSRS and ReportViewer report processors only know how to interpret simple Visual Basic expressions—not C# or any other language.) Virtually every property exposed by the report or the report elements can be configured to accept a runtime property instead of fixed value. Enter the expression shown in Figure 4. This compares the user-supplied parameter value (Parameters!ProductLineWanted.Value) with the Dataset column (Fields!ProductLine.Value). Click OK to accept this entry. Be sure to strip any space from these arguments using Trim so strings with different lengths will compare properly.
Figure 4: The Expression editor dialog box
Back in the Tablix properties page, the expression text now appears as <>, as Figure 5 shows. (This abbreviation, which hides the expression, isn't particularly useful. I've asked Microsoft's business intelligence tools team to fix it.) Now I'll set the Value expression. Enter =True in the expression’s Value field. Don’t forget to prefix the value with the equals sign. This approach might be a bit more trouble, but it’s far easier to simply use Boolean expressions than to deal with the facts that the RDL parameters aren't typed (despite the type dropdown) and that the report processor logic doesn't auto-type like you might expect because it’s interpreting Visual Basic.
Figure 5: Tablix properties page with <> showing
You’re now ready to test the report. Simply click on the Preview tab. This doesn't deploy the report to SSRS—it just uses the Report Designer’s built-in report processor to show you an approximation of what the report should look like when finally deployed.
If the report works, you’re ready to deploy it to the SSRS server. Right-click the project to access the Properties page, which Figure 6 shows. Fill in the Target Report Folder and Target Server URL. Of course, these values will be up to your report admin to determine. In my case, I’m deploying to a folder called HHG Examples and targeting my Betav1/ReportServer’s SS2K8 instance. Note that the instance names are separated from the ReportServer virtual directory by an underscore in the 2008 version and a dollar sign in the 2000 and 2005 versions.
Figure 6: The project's Properties page
Right-click the report to deploy and choose Deploy. This should connect to the targeted SSRS instance and save the report to the SSRS catalog. Deploying could take 30 seconds or longer the first time, as I’ve already discussed, so be patient.
Creating a Snapshot Report
Now that the report is deployed, you need to navigate to it with Report Manager so you can generate a snapshot. Use Report Manager to open the deployed report’s properties pages.
Using Internet Explorer (I haven't had much luck with Firefox), browse to your SSRS Folder.aspx page, as Figure 7 shows. Your report should appear in the report folder where you deployed it.
Figure 7: Browsing to the SSRS Folder.aspx page
Find your report and click it. The report should render (correctly) in the browser window, and this is how your end users should see the report. Report users shouldn't be permitted to see or alter the report parameters, however—be sure to configure the report user rights before going into production. The following operations assume that you have the appropriate rights.
Using the Parameters tab of the report property dialog, you can modify the default values assigned to the report, hide them, and change the prompt strings. More importantly, you’ll need to set up the report to use stored login credentials before you can create a snapshot.
Navigate to the Data Sources tab of the report properties page. I configured the report to use a custom Data Source that has credentials that are securely stored in the report server. This permits the report to be run unattended if necessary (such as when you set up a schedule to regenerate the snapshot). In this case, I’ve created a special SQL Server login that has been granted very limited rights to execute the specific stored procedure being used by the report and query against the appropriate tables, but nothing else.
Next, navigate to the Execution tab of the report property dialog. Choose Render this report from a report execution snapshot. Here's where you can define a schedule to rebuild the snapshot. This makes sense for reports that take a long time to execute, because when the report is requested, the last saved version is returned, not a new execution.
Check the Create a report snapshot when you click the Apply button on this page box and click Apply. This starts the report processor on the designated SSRS instance and creates a snapshot of the report. The next time the report is invoked from a browser, the data retrieved when the snapshot was built is reused—no additional queries are required to render the report. It also means that as the user changes the filter parameters, it’s still not necessary to re-run the query. This can save considerable time—especially in cases where the queries require a long time to run.
Handling More Complex Expressions
I mentioned that the report processor can handle both Visual Basic expressions embedded in the report or CLR-based DLL assemblies. It’s easy to add Visual Basic functions to the report, but calling an external DLL is far more complex and the subject of a future article. The problem is that code embedded in a report, be it a simple expression or a sophisticated set of Visual Basic functions, must be incorporated in every single report that invokes it. Because of this requirement, it makes sense to be strategic with these routines—don't go too far toward building a set of reports that use this logic. That way you can build a template report that includes the report-level logic so that it won’t have to be added on a piecemeal basis.
The Report Designer custom code interface in the Report Properties dialog box doesn't provide anything except a blank text box in which to save the Visual Basic functions you provide, so you probably want to add a separate Visual Basic class to the report project or build and test the functions independently. Once you’re happy with the code, simply copy the source code to the Report Code window, as Figure 8 shows. This custom function is used to change the coded Product Line designation to a human-readable value.
Figure 8: The Report Code window
The code used to invoke a Visual Basic function in an expression is straightforward—it’s too bad that the Report Designer doesn't recognize these functions to permit you to correctly point to them. This function is invoked on each row of the tablix in the Product Line cell. The expression invokes the named function, as Figure 9 shows. Note that IntelliSense doesn't recognize the function (or any report-defined function).
Figure 9: The expression to invoke the named function
The report supplied with this article has quite a few more expressions built in. As Figure 10 shows, the report captures several parameters, all of which are used in expressions to focus the Query report on a specific subset of the data. Because the report is recorded as a snapshot, the initial query isn't repeated when the user chooses another Product Line, price range, or color. I suggest you investigate the specific cells in the tablix data region control to see how these are coded. Hopefully you won’t find any surprises, because as they all use the same approach to building a Boolean left-hand side of the Filter expression.
Figure 10: The Product Report Query
By this point, I hope you’ve learned something about the inner workings of the SSRS engine and how the report processor handles requests for reports. Building an efficient query is just the beginning to building an efficient report, and you can use filters to minimize the amount of additional (often expensive) queries used to fetch more focused data. Remember that a snapshot report can be generated and stored indefinitely, so report consumers can use the results of your work over and over again.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
