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.