Is there a way to pass built-in Global fields to the dataset query of paginated reports

[analystBose]

Is there a way to pass built-in Global fields such as Globals!RenderFormat.IsInteractive to the dataset query in Power BI paginated reports?

Use case: Report Builder is connected to Power BI semantic model. The report has more than 1 million rows so, Power BI is running into memory error every time the report is opened. So, I want to show only to 1000 rows when viewed in web but load the entire data if exported as EXCEL or CSV.

I have tried constructing dynamic query using the expression of the dataset query. However, that throws a runtime error "The 'CommandText' expression for the query 'ShippedReport' refers to the global variable RenderFormat, which is not valid for this type of report item expression."

[analystBose · Accepted solution]

Hi @v-dineshya,

Thank you for sharing these helpful workarounds.

I'll give the first option a try, it sounds promising. As for the second and third approaches, I've already tested them, but unfortunately, they didn’t work in my scenario. Loading the entire dataset in web exceeds the row limit in the Power BI Service, so I had to rule those out. The complete data should only be fetched during the export.

Appreciate the support, will follow up if I run into any issues with the first option.

Best Regards.

[v-dineshya]

Hi @analystBose ,

Thank you for your response. As you mentioned in your previous post, you are going to try fist option which was suggested by me. Once you have done with that testing, Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @analystBose ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @analystBose ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @analystBose ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @analystBose ,

Thank you for reaching out to the Microsoft Community Forum.

You are facing a known limitation in Power BI Paginated Reports when trying to use Globals!RenderFormat.IsInteractive in dataset queries.

Please refer the below link.

Paginated reports in premium don't work properly w... - Microsoft Fabric Community

Globals!RenderFormat cannot be used directly in dataset queries, That's the reason you are getting below error:
“The 'CommandText' expression for the query refers to the global variable RenderFormat, which is not valid for this type of report item expression.”

Please try below alternative workarounds.

1. Use Report Parameters, Create a report parameter (IsInteractiveView) with default value True. Set this parameter dynamically using custom code or embedding logic when rendering the report in Power BI Service. Power BI currently does not support passing dynamic parameters based on render format directly. You need to use Power BI Embedded or custom embedding to set the parameter based on user action.

2. Use Visibility Expressions Instead of Query Filtering, instead of limiting rows in the query, you can fetch all rows by using below expression.

=IIF(Globals!RenderFormat.Name = "EXCELOPENXML", True, False)

to control visibility of data regions or tables.

3. Create two separate reports, Interactive version --> limited to 1000 rows using a query filter and Export version --> full dataset, optimized for Excel/CSV.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh