Forum Discussion
Data gateway support for paginated reports
hi jcheng, unfortunately in my scenerio it's not working by I'm using Qzure SQL Managed Instance as db source, I found it working in another solutions what you have to do is to change SQL instead of Azure SQL as connetion type in your report datasource configuration.
I am able to find a solution for this pagenated report in connecting to SQL MI database. You will need to create CNAME in your local DNS for SQL MI server which is FQDN by default. The CNAME could be just the first part of FQDN. Then, the Power BI report builder will use the CNAME instead of FQDN as SQL data source. The Report builder will try this CNAME as local SQL server instead of Cloud/Azure SQL server. After publishing pagenated report to Power BI App Workspace, you need to specify the rigt Gateway for this connection. Then, the pagenated report should run fine.
James
I have checked with Microsoft on this gateway support for paginated reports. At this moment there is no gateway support and there is no roadmap for the same in 2020. Also it is already called out in the microsoft documentation https://docs.microsoft.com/en-us/power-bi/paginated-reports-data-sources
The other option on hand is to use a power bi dataset sourced from Azure or other data sources refreshing via gateway. Also there is a limitation there, you cannot connect to the power bi dataset having live connection. You can do it on the dataset having direct query or import or composite models.
Hope this helps!
Hi Anonymous ,
I was just trying out jcheng suggestion to make a CNAME of the Managed Instance URL and now paginated reports are actually working from Power BI using an on-premise Data Gateway connecting to a private-only SQL Managed Instance.
If this is all that it takes, shouldn't the documentation have a note about this?
Since the solution is only the introduction of a CNAME DNS record, I guees its a supported scenario?
And big thanks to jcheng for your suggestion 🙂
- Iam glad that jcheng suggestion worked for you Anonymous. Are you sourcing the data from SQL server? If yes then paginated report will support using the gateway as mentioned in the document shared, but in my case iam using the Azure SQL server which is not supported yet.