Unanswered

How to Loop a PBI Paginated Report based on a Report Builder Parameter?

(0) Share

Report

Posted on by Community Power Pla...

I have a list of Vendor names that I'm using to filter each Paginated report which is set as parameter in Report Builder. I would like Flow to export a report for each Vendor / "Do-Loop" and then have the report saved to a specific SharePoint folder with the Vendor's name.

What is the best way to accomplish this?

Screenshot 2021-02-17 161419.png

I tried using this Flow template and using this expression String('Vendor_Name') under the ParameterValues value -

Screenshot 2021-02-17 161655.png

I'm getting all sorts of errors. I have tried multiple expressions but will get this error "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Vendor_Name' referenced by 'inputs' in action 'Export_To_File_for_Paginated_Reports' are not defined in the template.

I have the same question (0)

All responses (9)

Answers (0)

Community Power Pla... on at

Like (0)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Just so I'm being clear. I have a list of 500 vendors. (It's lots more complicated than that) I want Flow to select Vendor #1 from the Report Builder's parameter and run a report. Then export it to Vendor #1's folder on share point. Then Vendor #2, #3... all the way to Vendor #500

I can manually type "Vendor #1" and the process will run correctly.

I thought I could do an Items expression and a Parameters expression. I have all sort single expressions combination expressions.

Thanks! I would even take the syntax for writing multiple Vendor names in the Parameter values at this point. Lol

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Just found out about the "Until Do" that can wrap around the Export Power Bi Reports API. But I'm still don't know what expression to use. The Report Builder "Parameter" is like a filter. For the filter <Vendor_Name>Is it String('Vendor_Name') or parameter('Vendor_Name') or parameter(String('Vendor_Name')) OR something else

Was this reply helpful? Yes No
MattFinlay 24 on at

Like (1)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Hi Brian_F12 - did you ever get a solution for this please? We have the exact same requirement.

thanks
Matt

Was this reply helpful? Yes No
nich3play3r 242 on at

Like (1)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

@MattFinlay if you haven't found this yet, it might help.

Was this reply helpful? Yes No
tlld 12 on at

Like (0)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Hi, I know some time has passed, however I'm also looking for a solution to this requirement.
However, I cannot use PowerAutomate as stated in the link provided by @nich3play3r
Is there any possibility to do this natively with Power BI Report Builder?

Was this reply helpful? Yes No
Luke G on at

Like (1)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Any resolution to this yet? I have been able to pass an array of values to the parameter using an excel table successfully then apply to each and creating an array, but I have still gotten an "Internal Server Error." Here's what it looks like:

Was this reply helpful? Yes No
Brian_F5 17 on at

Like (0)

Report
Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Yes. I have been using the Apply to Each as a Do Loop for Paginated Reports a ton.
First I'm getting a list from a Table from a Excel file on Sharepoint.
Next. Select Action "Export to File for Paginated Reports"
Select your Workspace. Report & Export Format.
Then type your ParameterValues name - (exactly. Casing matters)
Once you type the ParamaterValues name, automatically an Apply to each will appear on your Flow
Then click The Report Parameter value. And here is where Dynamic Content will pop up. Select your Parameter from the Excel List.
And you're done

The question I had was what to put in the i"Select an output from previous steps" field. Power Automate will automatically select "value" for you.

Was this reply helpful? Yes No
Luke G on at

Like (2)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Sounds like you already got your answer?
I finally figured this out and got it to run right. When you "List Rows" the "Value" is the list of rows... So you have to bring that whole list into the next step. Then in parameters or various other fields, you can select from the columns, so you can use different columns for different parameters. You can see "value" first, then a specific column for my parameter.

Was this reply helpful? Yes No
Brian_F5 17 on at

Like (1)

Report

Re: How to Loop a PBI Paginated Report based on a Report Builder Parameter?

Great stuff @Prof_G
Yep. that is it. I think I just needed to get familiar with Dynamic Content in Power Automate.
One thing to note is that Power Automate is looking for the Parameter Name. In Paginated Reports, you can change your Parameter Name which can be different from the Value. I do this to get my SQL to pull the Customers ID when the Parameter Name is the Customer Name.

Was this reply helpful? Yes No