Looping Power Bi report

[Anonymous]

Hi ,

I am a beginner in Power BI and trying to convert a SSRS report to Power BI.

Am doing Sales Rep KPI report. My report has 2 slicers. Rep Code and the Account numbers(filtered when rep code is selected).

Its a one to many relation between rep code and account number. So, each Rep can handle hundreds of accounts.

Now, I have created the required visuals for sales rep KPI but they currently work based on the selection of the Rep Code and Account number.

Here comes the problem, if I just give this report to user, it takes them hours and hours to generate the reports for each sales rep.

I want to do something like, I schedule the report to run on a particular time,where I just send the Rep code parameter and the Power Bi report to be looped with in all the accounts of the sales rep and create a PDF.

In SSRS what I have done, is created the sales rep KPI as a report and then used it as subreport in another report where it generates the report for all the accounts. Once I schedule the main report, user will get a PDF with the KPI of each account.

Note: Power BI report I created had 2 pages. So, when looped, I need to loopboth these pages for each account number.

Please suggest me how can I achieve this using Power BI.

Thanks,
Aditya.

[lbendlin]

Then you would need a Premium license in Power Automate. SQL connector is not part of the standard/free package.

Instead of Power Automate you can use Powershell commands, but these are far more pedestrian and you have to implement the entire process manually.

[Anonymous]

All the data related to reps and account numbers will be strored in SQL. 

[lbendlin]

Let's assume you have your list of reps in an Excel file on a sharepoint or Onedrive.

In Power Automate you would

- open the excel file

- iterate through the list

- for each list item call the Power BI Report Export with the rep specific filter and the pages you want to export

- wait for the rendering to finish

- take the resulting PDF and store it somewhere or email it to the rep

[Anonymous]

Hi Ibendlin,

Thanks for the suggestion. I have never tried Power automate. So, watched some tutorials, what i understand is, it will actually help to automate the workflow. ie; When a file is received in a folder then email or do other actions.

But, in my case, I will supply a sales rep parameter,

First step: get all the account number for the sales rep

Second step: Lets say if there were 100 account numbers from 1st step, I need whole report for each account (100 reports)

Third step: All the 100 reports to become paginated into single PDF and emailed to user  .

If you send me some tutorials of whatever you are suggesting, it would be very helpful.

I am new to this forum, so i didn't relaised I have done double posting. Sorry for that.

I don;t have option to attach files. So nto able to attach.

[lbendlin]

You can use Power Automate to do the looping, and to call the Export Report To File function with a report filter (in your case the rep).

Please do not double post.