Dataverse PBRS functionality

[lherbert501]

Hi,

I'm trying to use cascading parameters in Power BI Report Server and it won't seem to work.

I'm connecting via dataverse below and have  country and city parameters. The cities should only show from the selected country. If two countries are selected I want all cities for that country. I also want to include a manual all on both.

I think the limitation is the read only database and dataverse as previously I would have a function or proc to do this.

As this functionality will be needed on multiple reports I'm unsure what to do in this scenario where a previously a function would have worked great.

IN (@City) -  this just won't work in the main dataset to read it in and I can't use string agg or any function.

[v-sgandrathi]

Hi @lherbert501,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thankyou.

[v-sgandrathi]

Hi @lherbert501,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

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.

[lherbert501]

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

[v-hashadapu · Accepted solution]

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

Add a multi-value parameter to a paginated report - Microsoft Report Builder & Power BI Report Build...