Forum Discussion
Report Builder-Cascading Prompt
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.
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.
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.
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.