Forum Discussion
Dataverse PBRS functionality
Hi lherbert501 , Thank you for reaching out to the Microsoft Community Forum.
Yes, having three datasets (Country parameter, City parameter and the main report dataset) is the correct approach for cascading parameters in Report Builder / PBRS. The key is that the City dataset must reference the Country parameter, so the available cities are filtered based on what the user selects in Country.
For your City dataset, filter it using the Country parameter. Then in Dataset Properties -> Parameters, map @Country to =Parameters!Country.Value. Also ensure the Country parameter allows multiple values and that the City parameter uses this dataset for its Available Values. This is what creates the cascading behaviour, the City list refreshes based on the selected Country values.
For the main report dataset, the same idea applies. Reference both parameters and filter with IN since they are multi-value parameters. Report Builder will pass the selected parameter values to the query automatically. Even when using the Dataverse read-only SQL endpoint, this pattern works because cascading parameters are handled by the report datasets and parameter mapping, not by stored procedures or SQL functions.
Use cascading parameters in paginated reports - Power BI | Microsoft Learn
Hi v-sgandrathi
Thankyou for this. Very helpful.
I currently have three seperate sql datasets. 1 for each parameter and 1 for the report data. Is this how you meant?
I have e.g. select countryid,countryname from country - For Country dataset and parameter.
I have e.g. select cityid,cityname, countryid,countryname from city - For City dataset.
How do I set this up to reference the country? Obviously before it would say IN string agg or some function @Country.
How do I also reference this multi parameter of city in my main report dataset with the same logic?
I've looked for videos or documentation on how to do this in the dataverse and PBRS but I couldn't find anything.
Thanks
Hi lherbert501 , Thank you for reaching out to the Microsoft Community Forum.
Yes, having three datasets (Country parameter, City parameter and the main report dataset) is the correct approach for cascading parameters in Report Builder / PBRS. The key is that the City dataset must reference the Country parameter, so the available cities are filtered based on what the user selects in Country.
For your City dataset, filter it using the Country parameter. Then in Dataset Properties -> Parameters, map @Country to =Parameters!Country.Value. Also ensure the Country parameter allows multiple values and that the City parameter uses this dataset for its Available Values. This is what creates the cascading behaviour, the City list refreshes based on the selected Country values.
For the main report dataset, the same idea applies. Reference both parameters and filter with IN since they are multi-value parameters. Report Builder will pass the selected parameter values to the query automatically. Even when using the Dataverse read-only SQL endpoint, this pattern works because cascading parameters are handled by the report datasets and parameter mapping, not by stored procedures or SQL functions.
Use cascading parameters in paginated reports - Power BI | Microsoft Learn