I recently faced a customer question:
“How do we export a Power BI Paginated Report to SharePoint for different parameters on a schedule and get notified when the PDF file is available”?
Background
One of our fundamental principles of architectural design is simplicity. At Coeo, we have helped many customers implement Power BI Premium and deprecate their Analysis Services and Reporting Services infrastructure. While deploying SSRS reports to Power BI as Paginated Reports is a fantastic Software-as-a-Service solution, how do we replace our on-premises data-driven SSRS subscriptions? That is the subject of the customer question and the topic we tackle below.
Subscriptions
We have many automation options on the Azure cloud platform, and my investigation started with the subscriptions feature in Power BI. The screenshot below shows how you can subscribe to a Paginated Report to receive an email at a cadence of your choice.
You have options allowing you to specify the type of attachment (PDF, XLSX, CSV etc.) and configure the email to include a report link and preview.
Subscribers receive an email such as:
What we can’t do with subscriptions is write the report to SharePoint and email interested parties, so enter option two.
Power Automate
Power BI is only one sibling of the Power Platform family of tools, including Power Apps, Power Automate and Power Virtual Agents. The Power Platform is a collection of SaaS services allowing you to do three critical actions with data to drive your business:
- Analyse – make sense of your data.
- Act – add logic to act upon your data insights.
- Automate – add workflow automation.
It is pointless to analyse without acting, and the automate is the glue to join everything together.
It turns out Power Automate can do what we require. Automate allows us to chain activities together in a powerful workflow mechanism to perform any number of automation tasks. Here is a screenshot to export a report:
That solution works fine and writes a PDF to SharePoint on a schedule as required. But what about taking things to the next level and writing many reports for different sets of parameters?
Your first task is to create a data table to contain your parameter sets. This table could be in a database, Excel file or, as in this case, a SharePoint list. Hit new list in SharePoint:
This example is simple and has one parameter with three possible values.
To create your flow within Power Automate, authenticate at https://powerautomate.microsoft.com/ and hit the create menu item. You can start from one of the many templates or hit “scheduled cloud flow” to start from a blank canvas. After specifying your schedule, you get:
You then hit “new step” to add more links to the chain to create powerful workflows.
Here, we use the “Get items” step in conjunction with an “Apply to each” step to iterate over each parameter set within the SharePoint list, export the Paginated report and write the output to SharePoint.
The “Export to File for Paginated Reports” step allows you to specify the location of the report, export format, an effective username for row-level security and any parameters. You will notice the parameter value of “DateFiscalYear”. This dynamic content is the output of the previous “Get items” step that reads the parameter values dynamically at runtime.
The next step is “Create file”. The settings of this step allow you to specify the SharePoint location and filename.
Here you can get clever with your filename and add dynamic properties. In this example, we append the fiscal year parameter and current time to distinguish each report for our consumers. The file content “Body” placeholder is the output of the previous step.
Lastly, we communicate with our business users that their reports are ready:
The result is three files written to SharePoint, one for each of our three fiscal years:
And an email notification to confirm all is complete:
Throttling
One issue I faced with this approach was a five-minute delay between creating each PDF. We generate the reports via the Power BI API, and this throttling is in-built. I found no documentation or method to configure this wait time, so build this into your planning.
Licensing
You pay for Power Automate per-user or per-flow. You also can limited capabilities via Microsoft 365 or Windows. Check out the licensing FAQ at https://docs.microsoft.com/en-us/power-platform/admin/powerapps-flow-licensing-faq to find the best option for you.
Learn More
The solution described in this post is one of the unlimited possibilities of Power Automate. You can add powerful workflow automation using a no-code graphical interface. To increase your knowledge, head over to Microsoft Learn at https://docs.microsoft.com/en-us/learn/paths/automate-process-power-automate/ and learn more.