How to Default Values & Available Values for Report Paramaters

Report Paramater

In SSRS, parameters are used to specify the data to use in a report, narrow down your report data for better analysis, connect related reports together, and vary report presentation. Report parameters can be Single-Valued and Multivalued.

The most common use of parameters is to vary report data retrieved by dataset queries. In this scenario, users are prompted for a value or values when they run the report, and the dataset query retrieves only the data that is requested. You can change the report parameter properties in the report design tools to include a valid values list that displays at run time.

You can also create cascading parameters, which retrieve hierarchical data from a data source.

Below are the uses of report parameters:

1. To Control Report Data - by selecting required parameters values to filter the report data.
2. To Control Report Appearance - use parameters to change report appearance using expression-based properties, including conditionally hiding report items and conditionally changing text color.
3. To Connect to Other Reports - use parameters to link to drillthrough reports, subreports, and linked reports.
4. To Select Specific Data Columns - parameters can be used to select specific columns of a table/matrix at run time.

Query parameters are added to a dataset query by way of the query designers or the Dataset Properties dialog box. After you create a query with parameters, Reporting Services automatically links query parameters to report parameters with the same name.

Below are the steps to create parameters:

Create a new Data Set for the report parameter. I will add a dataset dsDesignation for Employee's Designation using query

SELECT DISTINCT Desg AS Designation FROM Employee

Now I will add a parameter Designation. In Report Data section, right click on Parameters node and click Add Parameter...

In Report Parameter Properties window, enter Name and Prompt of the parameter and select the Data type from dropdown box. For our example, enter Designation in Name and Prompt text boxes and select Text as data type.

Set Avaliable Values for Report Paramater

Now click on Avaliable Values to set available values for the parameter. Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field and Label field.

Set Default Values for Report Paramater

Click on Default Values, Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field. Click OK to save changes.