SSRS Interview Questions and Answers - What are Cascading parameters in SSRS report?

Cascading Parameters are set of parameters in which values of one Parameters depends on the value choose in another parameters.

It is always good idea to explain your answer with examples in interview. Let's consider below example.
We have a single value parameter call Region and then on selection of Region Parameter value, we would like to display Countries related to that region in Country Parameter.

The Region Parameter, you can provide static values or you can get the values from query. If more regions can be added or removed, it is good idea to get values for Parameter by using query. Let's say our table name is dbo.TotalSale which has Region, Country and other columns.


Main Query for Report

SELECT FName
    ,LName
    ,SaleAmt
    ,Region
    ,Country
FROM dbo.TotalSale
WHERE Region = @Param_Region
    AND Country IN (@Param_Country)


Query for @Param_Region Parameter ( Single Value Parameter)

SELECT DISTINCT Region
FROM dbo.TotalSale

Query for @Param_Country ( Multi Value parameter). Notice that we have used @Param_Region Parameter to get only countries related to that Region.

SELECT DISTINCT Country
FROM dbo.TotalSale
WHERE Region = @Param_Region