Parameterized Reports
A parameterized report uses input values to complete report or data processing.
With a parameterized report, you can vary the output of a report based on values
that are set when the report runs. Parameterized reports are frequently used for
drillthrough reports, linked reports, and subreports, connecting and filtering reports
with related data.
Using Parameters
Parameters are used in dataset queries to select report data, to filter the result
set that the query returns, or to set layout properties used to display or hide
parts of a report. You can also specify cascading parameters that populate a series
of dependent, drop-down parameter lists. For example, a drop-down list of Region
parameter values can be used to populate a drop-down list of City parameter values.
You can use parameters with linked reports by pairing a specific parameter with
each linked report to change the outcome. For example, you can create a single regional
sales report that shows the sales for all regions, and then use a parameter for
each linked report to filter data for a particular region. Specific parameter values
can be stored with the report so that users do not have to type values.
Not all parameters may be visible in the report at run time. A report author,
report server administrator, or content manager can specify which values to use
and then hide the input fields on the report.
Query Parameters and Report Parameters
Reporting Services supports two kinds of parameters: query parameters and report
parameters. Query parameters are used during data
processing to select or filter data. Query parameters are specified in the syntax
of a data processing extension. If a query parameter is specified, a value must
be provided either by the user or by default properties to complete the SELECT statement
or stored procedure that retrieves data for a report. Report
parameters are used during report processing to show a different aspect
of the data. A report parameter is usually used to filter a large set of records,
but it can have other uses depending on the queries and expressions used in the
report. Report parameters differ from query parameters in that they are defined
in a report and processed by the report server, while query parameters are defined
as part of the dataset query and processed on the database server.
To add Report Parameter:
- For adding Parameters to Report move your cursor towards Menu and clicks
on Report Menu.
- Choose Report Parameters Tab.
After adding the Report Parameter we need to set properties of the added
parameter. The parameter has following properties, which we have to set.
Parameter Properties:
- Name - Shows the parameter name and its must be unique name.
- Data Type – Data Type of Report
- Prompt - Shows the Display Name for Parameter in Report Viewer Control
The prompt string defines the label that identifies the parameter in the input
area. The prompt can be the name of the parameter or directions to the user, for
example, "Name" or "Enter Name". If the prompt is left blank and a default parameter
value is specified, the default value is used, and the input box for the parameter
is not displayed when the user runs the report. If prompt is left blank, and no
default parameter value is specified, the report cannot run. If you do not want
to prompt the user for parameter values, mark a parameter as Hidden or Internal.
We can have more options to set the parameter behavior. We have more options
to set the more properties, which we find in Check Boxes as like following:
- Hidden
- Internal
- Multi Value
- Allow Null Value
- Allow Blank Value (Not for Integer Data Type)
Lets see what ways the above properties are useful for parameter while designing
the report.
- Hidden
By using Hidden Parameter, you can hide the parameter on the parameter
input area of the published report, yet set values for it on a report URL or in
a subscription definition.
- Internal
If you set a parameter to Internal, it is not exposed in any way except
in the report definition. In other words Internal Parameter is a parameter that
cannot be changed at runtime. A consumer of a published report will never see this
as a parameter.
- Multi Value
If you want to display multiple selection then MultiValue parameter
is used.
For e.g. Suppose we want to display list of Employees into Drop Down List Box and
allowed user to choose among of the listed, we can set the MultiValue Parameter.
- Allow NULL Value
Allow Null Value indicates that the parameter can have null values.
- Allow Blank Value
If we want to allow an empty string as a valid value then we have to
set this parameter.