Parameters in SQL Server Reporting Services (SSRS) add a level of interactivity to reports. Parameters can be utilized for everything from criteria in a query to filters for a Tablix to controlling visibility of objects on a report. Furthermore, they can be populated from a set of constant values, or they can be based off the results of a data set query. Parameters are the main component of any interactive reporting tool.
Step 1: To create a new parameter, right click on the Parameters folder and select Add Parameter.
Step 2: On the new report parameter screen, after naming the parameter, the next steps include:
- Deciding what prompt wording should be presented to user running the report.
- Defining the data type; note this is the data type of the value not the parameter label. Also, this data type also determines the type of parameter that will be presented to the report consumer. For instance, if Date/Time is selected, a calendar type selector will be presented.
- Allowing blank values.
- Allowing null values.
- Allowing multiple value
- Visible – of course this means the parameter is visible to all users1
- Hidden – parameter is not presented to end users when running the report, but the parameter can be passed into the report at run time and does show up when setting up subscriptions.
- Internal – like hidden except in addition to not being presented to users running the report, it cannot be passed in a run time. This would be a value that you want to set within the report, but do not want users to be able to change.
Step 3: After defining the general properties for the parameter, we must next decide how to populate the parameter list. The first option is none which creates a “free form” text box for entry when the sql server reporting services is run.
Step 4: The second option allows you to manually enter the labels and related values that will be presented to the report consumer.
Step 5: The final option allows us to populate the label and related values based on a query. In the below illustration, the TerritoryLookup dataset is used and specifically, the TerritoryID field for the values and Name for the label. The label can be the same as the value. For this parameter, a drop-down list of territory names will be presented to the report consumers.
Step 6: Save and Build the Report.