SSRS - Parameter
Table of Contents
About
Parameters are use field.
Articles Related
Parameters
Report Parameters are passed to a report when it is rendered. Report parameters can be specified by:
- A user viewing the report interactively.
- A setting in a report subscription that delivers reports automatically at a scheduled time.
- A report that contains a parameterized subreport.
Report parameters can be mapped to dataset parameters to filter the report data.
Expression Syntax:
Parameters!myParameter.Value
Management
Create
- New > Parameter. The parameter will not be linked to the data set (ie unmapped)
- Defining a data set query containing a parameter will create a parameter and link it to this data set
Properties
Multiple value
In case of a collection parameter (multiple value), the operator IN must be used in the data set
SELECT TrId, Amount
FROM Transactions
WHERE YEAR(transactionDate) IN (@Year)
Visible
Hidden and internal parameters are useful when:
- the parameter value will be set programmatically by a client application
- or when the report will always be used as a subreport and the parameter value will be specified in the parent report.
Data Filtering
Data region or group
Filter a data region or group
You can use a unmapped parameter to filter data in a tablix data region or chart by setting the Filter property to an expression that compares a dataset field value to the parameter value. The whole data set will be retrieved and after then the filtering will occur.
Dataset
With a parametrized dataset, the retrieved data will be automatically filtered.
Parameters are specified in the query
For example, the following Transact-SQL query uses a parameter named @empName:
SELECT empName
FROM Employee
WHERE empName = @empName
Dataset parameters are mapped to report parameters.
Subreport
Display a subreport by passing a parameter to show a parent-child hierarchy.