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

[UrsineWelles]

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!

[UrsineWelles]

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)

[v-kkf-msft · 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.

     

I have an excel file online with data:

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".

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

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

• Test if the parameter takes effect.

• 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.

    

• 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.

     

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.

[UrsineWelles]

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.

🙂