SSRS - Parameter

About

Parameters are use field.

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.


Powered by ComboStrap