Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate
This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.
I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.
With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link
It is possible !!!
And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here
How to set it up
In my workspace I have a simple report with 2 parameters – Year and Country
In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.
You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.
Next is to design the flow in Power Automate
In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.
Next step is to get the rows from the Excel file – So I use the List rows present in a table
This action will return the three rows and next is to use the Control “Apply to each” to loop through each row
For the output we choose the value from the “List Row…” step
Then we add the action “Export To File for Paginated Reports
This action will run a selected report from a specific workspace
We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.
Add the end of the Action window – we can specify the values for each of the parameters.
OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown
As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”
We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.
Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content
The flow is now ready for testing – Save you flow and hit the test button.
Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.
Hopefully you will see that all steps are successfully.
And we can check our mail box to see the report delivered
The recipients of the e-mail can be sent to any e-mail address.
Can we pay by the hour ?
In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.
You only pay for the time the capacity is on – and the list price per hour is $ 8.
Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.