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.
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. :)
