Posted on in Power Automate, Power BI

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.

16 thoughts on “Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate”

  1. Nice article Erik!

    This could be a good workaround for centrally managing subscriptions for paginated reports, as the inability to view and edit other users subscriptions in the Power BI UI is quite disappointing 😦

    There’s one thing I’ve not managed to figure out yet, so wondering if you have any idea – do you know of a way of inputting multiple values for a single parameter using this process?
    E.g. using your example, is it possible to send an email with a single attachment where the values for the Country parameter are Australia and Canada?

      1. Thanks very much Erik 🙂 I’m new to your blog, but have read some good stuff on it already!

        As an extension of my query above, I wonder if or how it would be possible to pass a ‘Select All’ type value to a paginated report parameter via this process. E.g. using your example again, selecting all available Country values in one export.

  2. Pingback: Multivalue parameters in #PowerBI paginated report when using #PowerAutomate to export to file – Erik Svensen – Blog about Power BI, Power Apps, Power Query

  3. Hi, What is the best way to download like 10 000 PDFs as monthly statements from paginated report? This solution took like 30 seconds to create and download 1 statement, so for 10 000 clients….

    1. Hi
      Where does the data comes from ? If you do 10.000 queries there might be a bottleneck there – and then think about power bi premium to get more capacity to the paginated report processes

      1. Power BI Dataset. We tested this with PPU, A4 SKU and A6 SKU. The result was always the same – 30 seconds for the whole operation (Render PDF and download it). The problem will not be with the Dataset. First, the report was like one page, then we increased the number of pages per one report to 50 pages. The difference for whole operation was like 1 second (30 seconds for 1 page and 31 seconds for 50 pages per report). May be we will try it again with REST API, because as I understand it, this connector use the REST API, may be, go directly with through REST API will save some time. Or create more flow fetch the PDF in parallel.

  4. Erik, thanks very much for detailing this process. Have you experimented with having multiple, semicolon-separated emails in the Email column of your worksheet? Or would it be “safer” to add multiple rows per country and year in instances where you need to send the same report output to multiple emails?

  7. Hi there, just wondering if the Export to file from Paginated Reports will ever be available to use without the Premium Capacity? Is there a timeline for this? It would be hugely beneficial! I have a paginated report with about 75 parameter based subscriptions that need to be run on demand, not just on a schedule. This could make it a click of just one button rather than having to run all 75 subscriptions separately.

    1. Hi Sheila,

      You do not need Power BI premium capacity to use paginated reports – https://learn.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-faq.

      For your use case you might check out whether Power Automate could be a solution – https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-automate-paginated-integration – here the license might be an issue.

      So you might want to check this post out and see if that could be a solution – https://pbi-guy.com/2023/02/03/export-paginated-reports-automatically-in-a-low-code-way-without-power-bi-premium/

      If you only want to export data without formatting then check out the Power Automate action “Execute a query against a dataset” – this can enable you to create a csv file based on a DAX query that can be modified via parameters/variables

      Hope this can help you progress.

      all the best
      Erik

Leave a comment