Multilevel Cascading with Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:

1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot of what this process will look like:
Multilevel-Cascading-with-Select-All-1 Multilevel Cascading with Select All

 

Data Source

1. Right click on the Data Source folder in the left window.
2. Click on Add Data Source.
3. The following screen will appear:
Multilevel-Cascading-with-Select-All-2 Multilevel Cascading with Select All

 

Datasets

Dataset 1: Country

SELECT DISTINCT Country

FROM Northwind.dbo.Customers

ORDER BY Country ASC

 

Dataset 2: City

SELECT l11.City1+’_’+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11

,l11.City

FROM (

SELECT l1.City+’_’+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1

,l1.City

FROM (

SELECT DISTINCT City

FROM Northwind.dbo.Customers

WHERE Country IN (@Country)

)l1

)l11

ORDER BY l11.City ASC

 

Dataset 3: Company Name

SELECT l11.CompanyName1+’_’+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11

,l11.CompanyName

FROM (

SELECT l1.CompanyName+’_’+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1

,l1.CompanyName

FROM (

SELECT DISTINCT CompanyName

FROM Northwind.dbo.Customers

WHERE City IN(@City)

)l1

)l11

ORDER BY l11.CompanyName ASC

 

Dataset 4: Contact Title

SELECT l11.ContactTitle1+’_’+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11

,l11.ContactTitle

FROM (

SELECT l1.ContactTitle+’_’+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1

,l1.ContactTitle

FROM (

SELECT DISTINCT ContactTitle

FROM Northwind.dbo.Customers

WHERE CompanyName IN(@CompanyName)

)l1

)l11

 

ORDER BY l11.ContactTitle ASC

 

Dataset 5: Contact Name

SELECT l11.ContactName1+’_’+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11

,l11.ContactName

FROM (

SELECT l1.ContactName+’_’+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1

,l1.ContactName

FROM (

SELECT DISTINCT ContactName

FROM Northwind.dbo.Customers

WHERE ContactTitle IN(@ContactTitle)

)l1

)l11

ORDER BY l11.ContactName ASC

 

Parameters

Parameter 1: Country

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-3 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-4 Multilevel Cascading with Select All

 

Parameter 2: City

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-5 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-6 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-7 Multilevel Cascading with Select All

 

Parameter 3: CompanyName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-8 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-9 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-10 Multilevel Cascading with Select All

 

Parameter 4: ContactTitle

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-11 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-12 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-13 Multilevel Cascading with Select All

 

Parameter 5: ContactName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-14 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

 

Now assign parameter values to datasets

Steps:
1. Double click dataset “CompanyName”.
2. The following screen will appear:
Multilevel-Cascading-with-Select-All-16 Multilevel Cascading with Select All

3. Click Okay for both windows.
4. Now double click dataset “ContactTitle”.
5. The following screen will appear:
Multilevel-Cascading-with-Select-All-17 Multilevel Cascading with Select All

6. Click Okay for both windows.
7. Now double click dataset “ContactName”.
8. The following screen appear:
Multilevel-Cascading-with-Select-All-18 Multilevel Cascading with Select All

9. Click Okay for both windows.
10. Now run the report by pressing F5.

If you are interested in learning more, please feel free to ask in the comments below or email us at info@optimusinfo.com