Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UrsineWelles
New Member

How to replace an SSRS data-driven fileshare subscription in Power BI Service

Hi everyone,

I've got an SSRS data-driven subscription that uses an SSRS data source connecting to our on-premise SQL data warehouse to generate a list of IDs and filenames, and uses the IDs as the parameters for an SSRS report, and the filenames for what to save each pdf report as. They are saved to a fileshare and later moved from there to a SharePoint site.

 

I have published my report to our Power BI Service and I have seen examples of how to use Power Automate to save a paginated report to SharePoint but I am not sure what ways to replicate the data-driven SSRS subscription where it generates the list of IDs and filenames. Saving the files to SharePoint after the reports are generated I can do.

 

We don't have a Data Gateway set up to allow Automate to connect to our data warehouse at the moment so solutions that don't use one would be preferable currently. Could the Power BI Service be used to get around this?

 

Thanks!

1 ACCEPTED SOLUTION

Hi @UrsineWelles ,

 

Maybe you can try exporting a paginated report for each row in an Excel Online table or SharePoint list.

 

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

3 REPLIES 3
UrsineWelles
New Member

I didn't find this yesterday but a collague found it - I think it is a very similar question to mine. It doesn't have an answer yet.

How to Loop a PBI Paginated Report based on a Repo... - Power Platform Community (microsoft.com)

Hi @UrsineWelles ,

 

Maybe you can try exporting a paginated report for each row in an Excel Online table or SharePoint list.

 

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hi @v-kkf-msft,

 

Thank you for the clear guide. I was expecting the answer might involve saving a report to SharePoint to drive the loop over the list of outputs I need to extract. I will Accept your solution.

 

It is a shame that there isn't a way to manage this fully within PBI Service in the way that SSRS can do it, but hopefully this is something that Microsoft will develop in future.

 

🙂

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.