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.