cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryancascarano
Frequent Visitor

Power BI Paginated Report for each row in SharePoint Online List

I am having issues creating unique reports for each row in a SharePoint Online list. I am connected to the SPO as a data source and have created a data set from the list. The query is simple DAX produced by the query designer: EVALUATE SUMMARIZECOLUMNS function followed by a list of the list fields.

 

To provide some context, my SPO list essentially contains a list of fields that I want mapped into the paginated report to create a memo, where each row contains all of the data needed to populate a single memo. I've inserted these fields where they need to exist in my report and initial drafts produce things more or less how I want. However, the issue:

 

It seems no matter what I try, I can only get the report to generate for the first record in the list. If I execute the query in the query designer, I can see all of the data. I have tried creating a parameter to allow to select any individual record based on its unique ID in the list, but no matter what ID is selected, it still loads the same data. The only way I have found to get this to work is to explicility filter the query by a single ID and run the report, and it will pull in the fields from that row of data. However, this seems ineffective when I need to produce 100s of these reports.

 

I have tried using the Power Automate solution provided by Microsoft - Export a paginated report for each row in an Excel Online table or SharePoint list - and while my flow "works" - producing PDFs with unique names based on the unique ID in my list - every single file still contains the exact same data.

 

How can I make the paginated report run and produce a new report for each row in my list? Is there maybe an issue with how I am querying the data that is preventing it from iterating row by row? I cannot find any guidance on this issue online and am at a complete loss. I appreciate ANY assistance. Thanks.

4 REPLIES 4
v-kkf-msft
Community Support
Community Support

Hi @ryancascarano ,

 

I will test it according to your steps.

 

Best Regards,
Winniz

Thank you. I still have not come up with a way to make this work. 

Hi @ryancascarano ,

 

This is my paginated report data.

 

vkkfmsft_0-1630401246060.png     

 

I have an excel file online with data:

 

vkkfmsft_1-1630401513470.png


My objective is to export paginated reports with the corresponding data for each row of the ID column in excel. Here are my steps.

 

  • In report builder, I create a parameter "ID".

vkkfmsft_2-1630401552788.png

 

  • Create a table with ID column as row groups, and select "Between each instance of a group" in the group properties.

vkkfmsft_4-1630401818922.png

 

  • Then filter the value of the ID column = parameter.

vkkfmsft_3-1630401613287.png

 

  • Test if the parameter takes effect.

vkkfmsft_5-1630402025369.png

 

  • Publish a paginated report.
  • In Power Automate, select "Export a Power BI paginated report for each row in an Excel Online table". Follow the documentation. Note that the parameter value in "Export To File for Paginated Reports" is equal to the Excel ID column.

vkkfmsft_6-1630402104149.png    vkkfmsft_7-1630402197980.png

vkkfmsft_8-1630402321123.png

 

  • Run the flow. A paged report will be generated for each row of the ID column of the excel, with only one record in each paged report.

vkkfmsft_9-1630402936415.png

vkkfmsft_10-1630402977471.png     vkkfmsft_11-1630403110475.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks for the thorough reply @v-kkf-msft - I've replicated your steps exactly but the parameter still fails to change the data in the report.

 

Is there an issue with replicating these steps connecting to a SharePoint list over an Excel online?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!