Paginated Report - Expression In Connection String

[Anonymous]

We are trying to use Power BI Paginated reports for our paginated reporting solution.  In SQL Server Reporting Services (the technology Power BI Paginated reports are based on) you could define your Data Source connection as an expression that used parameters in the report.  According to this Microsoft documentation this is also supported in Power BI Paginated reports - https://docs.microsoft.com/en-us/power-bi/paginated-reports-embedded-data-source

However I have not been able to get it to work.  In that document it indicates the Report Builder Data Source properties windows should have an 'expression' button (just like it the Visual Studio SSRS editor does).  However when I look in Report Builder I do not see this button.  However I can just enter my expression in the connection string box.

I verified that I had my connection string expression correct by running the report in the Visual Studio SSRS editor (Report Builder does not support running reports with connection expressions).  However if I attempt to upload .RDL file to our Power BI instance I receive an error saying the definition of the report is not valid.

I am sure that it is the connection string expression causing this error because if I edit the report to make the connection string a static connection string, then I am able to successfully upload the report to Power BI.

My questions, is the Microsoft documentation wrong, and Power BI Paginated reports do not support connection string expressions?  Or is there some other step I need to take?

Thank You

[TOK]

@Anonymous Please check your Report Server version and compare it to your Report Builder version.

I had similiar issues, but was able to deal with them by downloading the matching report builder.

[Anonymous]

@TOKWe are using Power BI in Azure for our server, not Reporting Services.  Do you know how we would go about checking our Report Server version in that scenario?  I did check to make sure that I am on the latest version of Report Builder, and I am.

[rpatkar · Accepted solution]

On Power BI service, we cannot use expressions in connection string for RDL reports. At this time we do not have plans to provide a fix for this issue.

[Anonymous]

Is it now possible to use an expression in an RDL string?

[rpatkar]

hi Hannes, this is still not possible. Thank you for posting at Microsoft Idea (powerbi.com). In meantime, you can try workaround here based upon your use case and security considersations by using SQL Linked servers and change the source table in query with fully qualified name (server-database-schema-table). 

[Anonymous]

Thank you rpatkar.  Much appreciated.  Will try the workaround, as per your suggestion.

[v-diye-msft]

Hi @Anonymous 

Please kindly make sure you are using the latest version, and here are some articles could be your reference :

https://docs.microsoft.com/en-us/sql/reporting-services/data-connections-data-sources-and-connection-strings-in-report-builder?view=sql-server-2014 

https://docs.microsoft.com/en-us/power-bi/report-builder-expressions 

If not helps, please raise it to the support team for better advice:

https://powerbi.microsoft.com/en-us/support/