Automate paginated reports in PBI Report Builder using Power Automate

Power BI Report builder is a great tool to create pixel-perfect paginated reports using your existing Power BI datasets. For more information on these tools, please check my other article on Paginated reports using Power BI datasets & Report Builder

There are cases when business users need to generate reports frequently, most of the times because they need to get an update after some data thresholds (e.g. weekly sales is more than 10K).

The Power BI site is very user-friendly, users can generate reports in few clicks, but if the generation of reports can be automated this could potentially save precious time and reduce manual work.

This article explains how to automate the report generation using Power BI Report Builder & Power Automate.
If you are not familiar with these two tools, I recommend you to watch this video from Microsoft training (min. 12:50)

Case scenario
The manager of a retail chain needs to receive a sales report after sales promotions end, also the store that runs this sales promotion should receive this report at the same time.

Let’s say there is a sales report available in the PBI report builder and users access the service to generate it, on the parameters they select the store and the sales promotion name, then they get the list of products sold for this sales promotion.

1- sample sales promotion report — report builder

The sales table looks like this:

2- Sales promotion table — PBI dataset

Since sales are organized at different dates during the year (which is not defined), the first thing to do for the report automation is to check if the sales promotion ends on the current date (today) and the second step is to send an email with the report attached to the manager and the store that organized the sales (see email on the 2-sales promotion table)

Checking if any promotion sales ends today
There are different ways to check if a date = today, I found the easiest way was to do it on the Power BI dataset, adding a new column FinishToday = true/false
Here the formula:

if [End Date] = DateTime.Date(DateTime.LocalNow()) then true else false

Creating a simple report paginated report to get the email

Then, create a simple paginated report, let’s call it ‘Get email’ report, that contains one table with one field: Email and one parameter &rp:FinishToday=true

But what if there is no sales promotion ending today?
This can break Power Automate, to bypass this issue you can create a fake sales promotion ending today, then modify the recent added column:

if [Email] = “n/a” then true else if [End Date] = DateTime.Date(DateTime.LocalNow()) then true else false

The dataset will look like this, we are safe to go now.

As a result, in a day where one sales promotion ends, the ‘Get email’ report will bring two values (the fake one and the real one), to get the real one just sort the results, at the end this ‘Get email’ report should look like this:

‘Get email’ report

Creating a flow in Power Automate

Now let’s see this in action in Power Automate, here the flow created:

1. Calls the simple ‘Get email’ report that returns one parameter: Email
2. If there is an email different than ‘n/a’ then there is a sale promotion ending today
3. Generate the existing report, note: the exiting report should be modified to get the parameter &rp:FinishToday=true
4. The report generated can be saved on OneDrive or send by email, for the second you already have the email of the store from step (2)

After I finished this automation, I faced other problems like having multiple sales promotions ending on the same day, that was a bit more complex but also can be done with these two tools.

I hope you enjoy the reading and give you some ideas to automate reports.