Normally, we don't want to type in values in the parameter field. So, we want to populate that field with the available data from our backend table.
Another reason is we want to give the report consumers only the available values from our dataset.
For example, with the category parameter, we may want to provide all the category values from our dataset. We don't want to leave them guessing what categories are out there to choose from.
To populate our @Category parameter, we need to create a new dataset for our report.
Since we might only need this for this particular report, we'll create a report dataset (in contrast to a shared dataset).
- Right-click on the
Datasetsfolder in the Report Data window, and select Add Dataset. The Dataset Properties dialog box then displays our options to create a dataset. - Let's name our dataset
dd_Categoriesand select the option Use a dataset embedded in my report. - Let's skip the data source, we'll come back to that in a second. On the Query type, select Text then type:
SELECT Name FROM Production.ProductCategory.Our Dataset Properties should look like the following screenshot:
- Let's go back to the data source. Click on the New button next to the data source field, which displays the Data Source Properties window. Let's name our source
DS_Categories, and reference our project's data source as shown in the following screenshot:
We may also want to select the option to Use single transaction when processing the queries. We don't need to specify new credentials for this source because we're inheriting all the credentials from our referenced data source. Click on OK on both dialog boxes when done.
- Now, we right-click on the
@Categoryparameter in the Report Data window, and select Parameter Properties. - On the General tab, select Allow multiple values.
- Switch to the Available Values tab. Select Get values from a query and specify the dataset name, value field, and name field according to the dataset we just created. Click on OK when done:
Our report Category parameter should now show the drop-down list of categories:
