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...
Name it. (ListOfCountries)
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
