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.
Prev. page
[1]
2
3
next page 
