| Downloads |
|---|
| 95671.zip |
Recently, a Hitchhiker's Guide reader asked me how to filter a report on several values and pass those values into the report processor at runtime. In answering the question, I found it hard to find complete examples of the steps required to capture and supply parameters to a report when using the Visual Studio ReportViewer control. Since parameter management in the ReportViewer isn't handled automatically as it is in Reporting Services, this skillset is important to master if you expect to leverage the power of the ReportViewer control. This article should make this process a lot clearer—or at least I hope so.
The ReportViewer control is used to render RDL-based reports in a Windows Forms or ASP.NET application. When you use the ReportViewer control in local mode, you have to provide a populated data structure (such as a DataTable) whose columns map to the report elements defined in the RDL report definition. When you use the ReportViewer control in server mode, you can still pass parameter values to Reporting Services. This article explains how to collect, validate, and pass parameters to the query and reporting engines. I've provided a sample application that you can download and use to practice the techniques in this article. You can download the sample in the .zip file at http://www.sqlmag.com, InstantDoc ID 95671.
Working with Parameters
Parameters play a crucial role in making any report usable to consumers, who rarely know what they want until they don't get it. When perusing the information in a report, users often want to refine, refocus, or re-sort the data. Your code can process these refinements either by re-executing the base SELECT queries at the server or by simply changing report criteria such as the Filters property of the Table report item.
It's typically expensive—sometimes prohibitively—to re-execute the source rowset query because if your query returns more rows than needed, you increase the amount of time it takes to run the query, return the data, and render the report. However, if you plan to use the post-query filter strategy that I describe in this article, you'll need to balance the expense of server-side filtering (e.g., when passing parameters to the SELECT WHERE clause) with the utility and performance of selectively filtering rows on the client by using parameters that modify RDL expressions. To help you implement the post-query filter strategy, I show how to build an RDL expression that you can use in the Report Filters property. Before you get started, take a look at the sidebar "Query vs. Report Parameters," to be sure you understand the difference.
Capturing and Validating Parameters
When you use the ReportViewer control, it's your job to provide the values for the report parameters. In local mode (in which Reporting Services isn't rendering the report), your code also has to prompt for any parameters that the query or the report needs. In server mode (in which the ReportViewer control simply launches a Reporting Services report), Reporting Services generates the UI to prompt for, validate, and capture report parameters. In either mode, you must provide values for all parameters that don't have default values already set. You can pass parameters captured by your custom UI to a server report by using the same mechanism you use to pass parameters to a locally rendered report.
In the Reporting Services-generated FilteredAuthors report in Figure 1, the report calls for four parameters: Low Age, High Age, Author Name, and Acceptable Years. As you can see, the HTML-based report has filled in the default values set when the report's RDL was constructed. Because all of the parameters have default values in this case, the report processor runs the report using these initial values without waiting for user input. This automation can be expensive and time-consuming because your initial guess at the default parameter values might not match what the user wants. That's why I don't recommend setting default values for all parameters—unless you're virtually certain that's what the user wants.
In a ReportViewer application, you can (and probably should) persist the parameter values that the user last provided and reapply those values before the initial report is displayed. You can also save values in the application settings, an initialization file, in the registry, or in a cookie. As users interact with the server-generated HTML report interface, they can provide alternative values for each of the parameters as long as the values fall within the acceptable range as dictated by the settings supplied at design time in the Report Parameters dialog box. The Reporting Services report server administrator can override defaults or simply hide report parameters. In the case of the ReportViewer control, all parameter settings can be made by your application code.
The Visual Studio Report Parameters dialog box is essentially the same for the ReportViewer control and the Reporting Services BI tools Report Designer. For our sample report, the populated dialog box in Figure 2 defines five parameters but exposes only four. Note that the ThisYear parameter is special. Its default value is simply a Visual Basic (VB) expression that returns the current year. You can use this type of parameter as a value placeholder or anywhere you deem appropriate as you code report expressions. Most report properties can be set by using an expression. In a moment, you'll see that the ThisYear parameter value is incorporated in a report cell as well as in the numeric expression that converts the Year_Born data column value to age.
Although you can use the Report Parameters dialog box to preset any number of parameter properties, only some of these are exposed in the report as properties. You'll also discover that only some of the parameter properties can be set at runtime. I'll show you how to do that later.
Tip
Once the report parameters are defined, they're salted away in the RDL report-definition file. Ah, yes, the Visual Studio Report Builder default file extension is RDLC. However, you might find it handy to simply name your report with the Reporting Services RDL extension so that it can be shared between the Reporting Services Report Designer (which is exposed by the BI tools) and the Visual Studio ReportViewer control and Report Designer, which is a bit harder to use.
"Exploring Defined Parameters
Behind the scenes, the RDL(C) file contains elements and attributes in XML that define the report parameters along with every other aspect of the report and every settable attribute of the parameters collection. You can manipulate this collection (or at least read it) by using the GetParameters method against the LocalReport or ServerReport class that the ReportViewer control exposes. If you're feeling brave, you can open the RDL by using an XML editor and inspect or tune anything in the file that you think needs tuning. Is this process as dangerous as performing brain surgery in the bathroom mirror? Probably not, but I'd keep a backup of the file in case your daughter comes in looking for her curling iron and bumps your elbow. Just keep in mind that the RDL file is case-sensitive and gets rather anal. Since it's machine-generated, it might get changed back as soon as you dink with one of the Report Designer dialog boxes.
The RDL file elements that contain the parameters tell a lot about what the reporting mechanism stores for each parameter. Listing 1 shows a code snippet from the ReportParameters element.
Validating Impure Values
It's your code's job to validate the purity of the data being passed into the parameter Values array (which I'll discuss a bit later). Your application needs to validate every parameter the user supplies or limit the values to those you provide in pick lists. Remember, all data is evil until proven innocent. One routine that I included in the example application ensures that the value passed is a valid Integer. It's up to your application to ensure that the values are within an acceptable range. The code snippet in Listing 2 illustrates one approach to verifying that the supplied value is at least an integer. This routine could be called from the TextBox (or other UI control) Validating event.
When you apply the value to a strongly typed variable, the .NET Framework doesn't bother to do anything but make sure the data conforms to the type specified. The .NET Framework doesn't have any intrinsic tests to verify that the data conforms to any business rules you might have defined (e.g., the "age" parameters are used to input an acceptable age range). This limitation can cause problems when you set the ADO-.NET Command object's Parameter.Value property. The .NET Framework doesn't care what value you supply—at least not until the command is executed. The report's underlying collection of parameter values works in a similar way: Nothing complains until the report processor starts to interpret the RDL according to the parameters provided. Trying to figure out which parameter is in error and why can be a real pain.
However, the Reporting Services engine can test for valid values and you can supply a list of acceptable (or unacceptable) values that are displayed to the user when the report UI is exposed in the browser. In The Hitchhiker's Guide to SQL Server 2000 Reporting Services, my coauthor Peter Blackburn and I show how to write code-behind routines to provide even more sophisticated expression logic. Multi-value parameters are now incorporated into the reporting engine (disabled in SQL Server 2005 SP1 and reenabled in SP2). Basically, a multivalue parameter is passed around as either a delimited string or a string array. In any case, Microsoft and I both recommend that you don't let users provide any string parameter value without limiting what's acceptable either in code or by providing values in the Report Parameters dialog box in Figure 3.
The parameter in Figure 3 is designed to eliminate specific years from the list of Year_Born values. Because my example Biblio database (SQL Server Compact Edition .SDF) has a number of "test" values, the Available values feature lets me filter out this test data as needed. Because the values supplied can be single or an array of values, managing and validating the parameters is a bit more difficult. Fortunately, there are a number of Framework classes (some new) that can help.
Configuring the example Application
When it's time to render the report, you need to remember to set any required parameters before you execute RefreshReport. By definition, you'll need to provide a value-populated parameter for each parameter that doesn't have a set default value. Before doing so, you might want to build a custom code generator that automatically generates the UI elements needed to capture and validate the parameter values. Let's look at the underlying mechanisms that you can use to perform this task.
First, you need to set up the example project that contains the target report (RDLC or RDL) file and a Form (or page) that hosts the ReportViewer control. The code supplied with this article has a suitable example. Another feature of this example is its use of the newly released SQL Server 2005 Compact Edition database. In this case, the project contains the .SDF database file and also the DLLs used to access it. This means you won't have to install anything to try the sample—it even installs the ReportViewer control, which is not included in the .NET Framework.
I'm not going to walk through the process of creating the Data Source, which simply addresses the target database table. Not all of the columns in the target table are used in the report; the query that the report uses to build the TableAdapter contains a WHERE clause expression to eliminate any rows without a Year_Born value. It almost always makes sense to limit the number of rows to return from the query engine. Doing so lets the query optimizer leverage indexes and reduce load on the server and the network.
The next step is to add a Report item to the project that's exposed as an RDLC file. Name it something sensible so that you can locate and use it later. Note that the report heading includes a concatenated string set by the "ThisYear" report parameter. Figure 4 shows the configured report.
Your application can be based on either Windows Forms or ASP.NET architectures—both can leverage the power of the ReportViewer control. My example implements a Windows Form application that contains a single ToolStrip control (which you can see in Figure 5) that's configured to expose a Label and a TextBox or other UI control to capture the parameter values the user provides. The application also contains a button that re-renders the report once the parameters are reset.
The Report-Viewer control is added to this form and pointed at the RDLC report we just built, which links to columns in the Data Source. Setting the report filename in the ReportViewer control's Tasks dialog box inserts code to execute the TableAdapter Fill and execute the RefreshReport method. Again, we're focusing on report parameters, so let's move on to that part of the logic.
Setting the Table Report Item Filters
The whole purpose of this exercise is to show how to set several post-query filters that further refine what the user sees in the report. These filters are applied row by row in the target report item (in this case, a Table) and determine whether the row is exposed. You can use report parameters to alter any other report property that accepts an expression.
The logic in the filter blocks is a bit tricky. If the filter resolves to a simple "true" or "false" value, you'll have a lot less trouble getting the report processor to deal with them. It's possible to set up LIKE expressions and several other more sophisticated tests, but I found it's easier to call out to VB code when things get too complex. For details about how to build simple and complex expressions, see Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Applying Values to ReportViewer Report Parameters
Before you execute the RefreshReport method that launches the report processor, you should make sure that the parameters being passed to the report are correct. Remember, we need to ensure that the supplied parameter values are the right type, within a valid and acceptable range, and in the right format to pass to the LocalReport (or ServerReport) parameters collection. The code that Listing 3 shows is responsible for this operation. The code begins by extracting a description of the report parameters as exposed by the ReportParameterInfoCollection. The GetParameters method handles this extraction by returning an array of ReportParameterInfo objects. The returned objects aren't the same as the ReportParameter objects used to configure the parameter collection passed to the report processor by means of the SetParameters method.
Then, for each of the known parameters, the code adds a member to an array list that will be passed to the SetParameters method later in the routine. Before adding the member, the code checks the value for validity. In this case, I execute the aforementioned routine that checks to see whether the value is numeric and a valid integer. I expect a production application would also add business-rule validity checks to the custom TestForInt routine. If the value isn't valid, the code simply substitutes a benign value. Yes, the code should probably fail and return an indication to the user through an Error provider control—but let's keep focused here. Note that the code takes these simple (single-value) parameters from the source TextBox controls on the ToolStrip and passes them to the New constructor for the ReportParameter class.
At this point, you need to keep in mind several important points.
- When defining report expressions (as in filter or property expressions), you can include references to report parameters. As I illustrated earlier, I used several report parameters in the report filter expressions.
- Some of these parameters have values supplied by the user and others are set in code or by a query.
- When passing the validated array of parameters to the SetParameters method, you need to provide only a named element in the array for the parameters whose value you wish to set (or reset). All other values remain unchanged. However, you must provide a value-set parameter for each report parameter that doesn't have a default value set.
- The format of the value supplied must conform to the data type specified in the Report Designer Report Parameters dialog box. If the value format doesn't match the data type, the report processor throws an exception.
- If you've specified "acceptable values" in the Report Parameters dialog box, the value supplied must be one of these values.
- If you don't supply a value when using the ReportParameter New constructor, the default value as specified in the Report Parameters dialog box is used. There doesn't seem to be a way to extract the default value from the ReportParameterInfoCollection in code—not without using an XML query against the RDLC file.
- If you've specified one or more multi-valued parameters, you need to use the ReportParameter New constructor that accepts an array. You still need to verify that each value in the array is valid. The code that Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 95671) shows illustrates one way to validate all of the parameters passed in a multivalue array with the same routine used to validate individual parameters. Basically, the validation routine uses a CASE statement to test each parameter in the array for specific (acceptable) values.
Executing the Report
Once the parameters are set, it's time to render the report. Of course, this assumes that your code has passed any query parameters to the logic used to build the report dataset. Remember to validate the query parameters, too—they provide an opportunity to inject evil SQL into the query.
To render the report, execute the RefreshReport method against the specific ReportViewer class. You need to have a well-designed Try/Catch exception handler trap to handle the inevitable exceptions that will occur— especially if you let users provide unvalidated parameter values.
Summary
There's a lot more to designing and producing reports than I discuss in this brief article. However, I think that I covered most of the important details about how to define, capture, and manage query and report parameters. I strongly encourage you to read Chapter 14 of the Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) for a detailed discussion of the entire report paradigm exposed by Visual Studio's ReportViewer control and SQL Server Reporting Services.
