Data source: Northwind database
Dataset:
SELECT c.Country,c.City,c.CompanyName,c.ContactName,c.ContactTitle,
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 )
AND c.City IN ( @pCity)
AND c.CompanyName IN ( @pCompany )
The query returns over 2.000 rows. I use IN operator in the WHERE clause which allows me to populate each parameter with multiple values.
First I select the Country, then I will get multiple Cities to select from the second parameter based on the value selected in the first parameter, and at the end from the third parameter I will be able to select the companies based on the selected value in the second parameter.
Step 1
Dataset for @pCountry.
I will use 3 parameters for this report. I want to provide the values for these parameters form query. So first I have to prepare 3 embedded dataset for each parameter. This one is for parameter @pCountry.
SELECT DISTINCT(COUNTRY) FROM dbo.Customers
Step 2
Dataset for @pCity
SELECT DISTINCT City FROM Customers
WHERE Country IN (@pCountry)
Step 3
Dataset for @pCompany
SELECT DISTINCT CompanyName FROM Customers
WHERE City in (@pCity)
Step 4
Go to PARAMETERS, set @pCountry to get to values from a query
Step 5
Go to PARAMETERS, set @pCity to get to values from a query
Step 6
Go to PARAMETERS, set @pCompany to get to values from a query
Report preview
As you can see from the parameter population, you can select multiple items for each parameter.
