SSRS - Parameter

Parameters are use field.

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 

• 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

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)

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.

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.

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.

Display a subreport by passing a parameter to show a parent-child hierarchy.