SSRS: Text Parameter

Parameter: Specify values

Demo 2:Parameter: Get values from a query.

Demo 1

Data source: database Northwind

Data set:

Step 1

Create a shared data source and dataset. I am providing the parameter in the query: @pCountry

Step 2

Create a basic report by inserting a table and adding couple of fields to it. If you have a look on this design view, the parameter @pCountry is ready to use.

step 3

The parameter is not case sensitive. We can do couple of simple modifications to make this parameter work better.

step 4

Right click on the parameter name > parameter properties

Prompt: specify the text on the left side of the parameter.

Allow multiple values: to make this works, change the where clause in the data set to:

where c.Country IN (@pCountry). If you need to use multiple value use IN operator, otherwise it will return an error.

step 5

Northwind database contains 21 distinct countries. You can specify them all, or just one, or just couple of them.

step 6

You can also specify Default Values. Default value must be on of the already specified values. Can not provide USA, because USA is not on that list.

step 7

Because "Venezuela" is specified as a Default value, report will automatically load the data for this country.

Report preview

Demo 2

I am working with the same dataset. To get the data I have joined 3 tables

Select c.Country,

c.City,

c.CompanyName,

od.Quantity,

od.UnitPrice

from Customers c

inner join Orders o

on c.CustomerID = o.CustomerID

inner join [Order Details] od

on od.OrderID = o.OrderID

where c.Country IN (@pCountry)

Step 1

In the previous demo, I have created a simple table with a parameter getting the values from a specified list. I would like to change it and makes it more dynamic

Step 2

Write the queries for the parameter values

Step 3

Right click on the Dataset > Add Dataset...

1. Name it. (ListOfCountries)

2. I use a dataset embedded in my report, means this query will be only available for this report. (I could also create a new shared dataset - do not need it)

Step 4

I have 2 datasets (SQL queries). I use the ListOfCoutries dataset to populate my @pCountry parameter.

Step 5

Double click on the @pCountry parameter to open the Report Parameter Properties window.

Available Values : Get values from a query

Dataset: ListOfCountries

Specify value (the value is a value returned by query) and label (label means how do you want the value to be displayed. You can modify the label by using CASE statement) .

Report preview