Multilevel Cascading With Select All - Issue Resolved

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 how it will look like:

Data Source

      1.       Right click on the data source folder on the left hand side pane.

      2.       Click on Add Data Source.

      3.       The following screen will pop up :

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 on the left hand side pane.

     2.       Click on Add Parameter.

     3.       The following screen will pop up , click on General on left side

     4.       Now click on Available values

Parameter 2: City

Steps:

      1.       Right click on the Parameters folder on the left hand side pane.

      2.       Click on Add Parameter.

      3.       The following screen will pop up, click on General on left side:

      4.       Now click on Available Values

5.       Now click on Default values

Parameter 3: CompanyName

Steps:

      1.       Right click on the Parameters folder on the left hand side pane.

      2.       Click on Add Parameter.

      3.       The following screen will pop up, click on General on left side

4.       Now click on Available Values

5.       Now click on Default values

Parameter 5: ContactName

Steps:

      1.       Right click on the Parameters folder on the left hand side pane.

      2.       Click on Add Parameter.

      3.       The following screen will pop up :

4.       Now click on Available Values

      5.       Now click on Default values

Now assign parameter values to datasets

For this steps are:

      1.       Double click dataset “CompanyName”.

      2.       The following screen will pop up :

      3.       Click Okay to both windows.

      4.       Now double click dataset “ContactTitle”.

      5.       The following screen will pop up :

 6.       Click Okay to both windows.

     7.       Now double click dataset “ContactName”.

 8.       The following screen will pop up :

9.       Click Okay to both windows.

      10.    Now run the report by pressing F5 function key from keyboard.

     

        I hope you like it. :)