Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Looping Power Bi report

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.