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 lherbert501,
This is expected behavior when using Dataverse via the read-only SQL endpoint in Power BI Report Server (PBRS). The Dataverse connection does not support stored procedures, functions, or certain dynamic SQL patterns, including handling IN (@Parameter) with multi-select values like standard SQL Server datasets. As a result, adding cascading logic directly to the main dataset query can be difficult.
To implement cascading parameters, use separate parameter datasets. For instance, create one dataset for Countries and another for Cities filtered by the selected Country parameter. Set up the City parameter dataset to reference the Country parameter, so only relevant cities are returned based on the selection. In Report Builder, configure parameters to Allow multiple values so users can select multiple countries and see all related cities.
If you want an “All” option, you can add a row to the parameter dataset (for example, using a UNION) and adjust the logic so all cities are shown when All is chosen.
Since this setup will be used in multiple reports, consider creating shared datasets for Country and City parameters in PBRS. This lets you reuse the cascading logic without needing functions or stored procedures in the database.
Overall, because of Dataverse SQL endpoint limitations, managing cascading parameters through parameter datasets and report configuration is recommended instead of relying on SQL functions or procedures.
Thank you
Community Member.
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