• Marek Vavrovic

SSRS: Cascade Parameter

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.



2 views0 comments