Report Builder-Cascading Prompt

[ldwf]

I have a simple Report Builder report with two report parameter prompts.  The queries to build the parameters are in SQL.  One prompt is Product Type, with values such as '4 Door' '2 Door', and 'All Types'.  The child prompt is the Make.  Currently the child prompt shows all makes regardless of the product type select.  I can change the child SQL query to only show the makes associated with what was selected in the product type parameter, but when the user selects 'All Types' in the product type parm, I want all the makes to appear in the child parameter.  Instead, the child parameter is not showing anything when I select 'All Types'.  I've tried changing the SQL using a CASE statement but it doesn't work

[Poojara_D12 · Accepted solution]

Hi @ldwf 

In Report Builder, when working with cascading parameters—like a Product Type parent and a Make child—it’s common to filter the child parameter based on the selected value of the parent. In your case, the issue arises because the SQL query for the Make parameter doesn't return any results when "All Types" is selected in the Product Type parameter. This usually happens when the SQL filter doesn’t correctly account for the "All Types" option. To fix this, your SQL query for the Make parameter should be modified to handle the special "All Types" value explicitly. One effective approach is to use a condition like:

WHERE (@ProductType = 'All Types' OR ProductType = @ProductType)

This way, if the user selects "All Types", the condition resolves to TRUE for all rows, and the query returns all makes. If a specific type like "2 Door" or "4 Door" is selected, it filters accordingly. Avoid using CASE statements in the WHERE clause directly for filtering unless you're returning static values; conditional logic like the one above is more effective for dynamic filtering in parameter-driven queries. Also, ensure that "Allow multiple values" is unchecked if you're only supporting single selections for each parameter. This approach should enable your child parameter (Make) to correctly reflect all options when "All Types" is chosen.

[v-dineshya · Accepted solution]

Hi @ldwf ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Poojara_D12 , Thank you for posting response in this community.

Hi @ldwf , In addition to @Poojara_D12  response , i am adding microsoft official documents.

Please refer below Microsoft official documents.

Add cascading parameters to a paginated report - Microsoft Report Builder & Power BI Report Builder ...

Paginated report parameters in Report Builder - Microsoft Report Builder & Power BI Report Builder |...

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Poojara_D12 · Accepted solution]

Hi @ldwf 

In Report Builder, when working with cascading parameters—like a Product Type parent and a Make child—it’s common to filter the child parameter based on the selected value of the parent. In your case, the issue arises because the SQL query for the Make parameter doesn't return any results when "All Types" is selected in the Product Type parameter. This usually happens when the SQL filter doesn’t correctly account for the "All Types" option. To fix this, your SQL query for the Make parameter should be modified to handle the special "All Types" value explicitly. One effective approach is to use a condition like:

WHERE (@ProductType = 'All Types' OR ProductType = @ProductType)

This way, if the user selects "All Types", the condition resolves to TRUE for all rows, and the query returns all makes. If a specific type like "2 Door" or "4 Door" is selected, it filters accordingly. Avoid using CASE statements in the WHERE clause directly for filtering unless you're returning static values; conditional logic like the one above is more effective for dynamic filtering in parameter-driven queries. Also, ensure that "Allow multiple values" is unchecked if you're only supporting single selections for each parameter. This approach should enable your child parameter (Make) to correctly reflect all options when "All Types" is chosen.

[GilbertQ]

Hi @ldwf 

What you're looking for is cascading parameters. Here is a blog post expanding how to get this done. Cascaded Parameters in SSRS