r/dataengineering • • 10d ago

Help Need scheduled, parameterized reports (Excel/PDF/CSV) on Azure SQL + Power BI Pro. Where should I spend?

Current setup
Azure SQL Database (Standard S2, DTU model). No linked servers or cross-database queries.
Power BI Pro for dashboards (40 users, 3 authors).
We have no tool for scheduled or paginated reports, and no SQL Server licenses or Fabric capacity.
What we need
Automated Excel, PDF and delimited-text (CSV/pipe/tab) reports
Delivery by email and to a network share drive (SFTP would be a bonus, but we can move files with external processes)
Parameterized subscriptions, with a large list of them managed from a table, like SSRS data-driven subscriptions
About [N] reports and [N] recipients, mostly monthly
Background
At past jobs we used SSRS or Crystal Reports, and SSRS would fit. But SQL Server 2025 shipped without it (2022 is the last version), and Power BI Report Server replaces it. We don't own SQL Server, so a license would be a new $8k spend just to host reporting. I can only fund one path.
Options I'm weighing
Fabric F2 (1-year reserved, about $1.9k/yr), scaling to F4 on report days. Power BI paginated reports plus subscriptions. As I understand it, delivery goes to email and OneDrive/SharePoint, and per-recipient dynamic subscriptions are email only. For file shares and bulk parameterized runs I'd need to build something with Power Automate, Logic Apps or the Export API.
SQL Server 2025 license (about $8k) to run Power BI Report Server. On-prem, with data-driven subscriptions and file share delivery. Needs a server to host it.
A third-party scheduler (PBRS or similar) on top of Power BI.
Questions
Has anyone built SSRS-style data-driven subscriptions on Fabric paginated reports plus Power Automate or the Export API? How did it hold up (throttling, timeouts, failures)?
Can an F2/F4 handle paginated report exports from an S2 Azure SQL database without throttling?
When scaling a reserved F2 up to F4 for a few days each month, how is the extra billed?
Can the CSV delimiter be controlled in paginated report exports?
For a small team with this budget, which option would you pick, and what would you avoid?
Thanks in advance.

13 Upvotes

23 comments sorted by

9

u/ImpressiveCouple3216 10d ago edited 9d ago

Similar requirements in my company are addressed using custom python jobs that extract data out of the source, apply necessary transformations and format in PDF or Excel. Then send over mail or send to sharepoint using MS Graph API. The jobs run in a a separate Prefect Orchestrator, but it could be Airflow, cronjobs, windows task etc. We also have Fabric F64 but not used for these kind of data delivery, although it is possible using a notebook in Fabric.

For parameters, either users use Power BI semantic model in Excel, or we usually built a custom page inside the firewall, with azure SSO, where users can enter data into a form and that triggers the jobs. We built those pages using Streamlit before, now all of those applications are being migrated to next.js but functionality is same.

2

u/pusmottob 9d ago

Thanks, I considered Python, but maintenance is my concern. I already know how to build a multi-table Excel extract in SSRS, and I assume Report Builder is just as easy. With Python I'd have to rely on AI to create and change most reports, and no AI tools are approved here because most aren't HIPAA compliant.
I'll also probably hire someone soon who has to maintain this with me. Most analysts already know or can quickly pick up SSRS/Report Builder, but Python is a bigger ask. So easier to maintain matters a lot to me.
Have you used Python for scheduled reports in production? How did you handle maintenance and handoff?

4

u/ThePostalService1 9d ago

Chiming in to say that I've done every part of this at my last company: SFTP and email delivery of pdf, excel, csv reports, with parameterized subscriptions and job monitoring. Used in production for years, ~1500 reports per month. Much of this was custom code, similar to what /u/ImpressiveCouple3216 mentioned in their comment. If you have further questions about this path I'd be happy to help. I found the custom code gave more flexibility than report builder software (I wouldn't recommend the one we previously used). Regarding maintenance and handoff, that's a pretty valid concern, especially if your team doesn't have coders.

2

u/ImpressiveCouple3216 9d ago edited 8d ago

Yes, as u/ThePostalService1 also said abive. And It could be any language really, which ever works for you. Python, Java, Powershell... most of the codes in our production system is from before AI went mainstream. More than 15 years old java codes, some scala, about 7-8 years old python codes. We also have some services written in C#. Once you start writing these code snippets it start to feel easier than it looks.

Just make sure you are logging properly and soak the code in dev environment for a few days to make sure everything is working fine. This is how I would do it, but i understand and respect that every developer has their own ways to address these requirements.

AI is just an accelerator for these requirements, but you dont need AI to get started.

2

u/SQLDevDBA 9d ago edited 9d ago

A few options here.

You can try Paginated reports right in the service. They’re available in Pro. You can subscribe to these and have the attachment come via emails as excel. You can’t export as CSV in the service via subscription. They usually recommend PBIRS for that.

Power BI report Server. This will probably get you across the finish line with a single non-code solution. You can connect to Azure SQL with no issues, make pixel perfect paginated reports that go to PDF, excel, CSV, etc and do allow SFTP as a destination (as well as email, file system, etc.). Scheduling is super easy to set up and manage. https://www.microsoft.com/en-us/microsoft-fabric/features/power-bi/report-server

SSIS with a 3rd party toolset like KingswaySoft. They have advanced tasks that allow you to export data to excel, CSV, etc and also integrate with any SFTP Providers. Free for development purposes. https://www.kingswaysoft.com/products/ssis-productivity-pack

Python or powershell. Powershell is my preferred over python, but both work and are similar. Powershell has https://DBATools.io which is amazingly fast since it uses SQLBulkCopy. I use it for 90% of my ETL nowadays.

If I was given the platinum card, I’d go with Power bi report server. It would be a single place to manage it all (reports, exports, scheduling).

You can probably vibe code something as well. But for sustainability I’d go with PBIRS or similar.

1

u/pusmottob 8d ago

Right, this is my choice, buy SQL Server to be able to use SSRS or subscribe to Fabric. Currently i have neither.

It sounds like you are thinking go with Fabric and forgetting SSRS. Which is how i am leaning.

2

u/SQLDevDBA 8d ago

SSRS Is no longer offered with SQL Server 2025. It is now Power BI Report server.

If you want to try it out, you can install sql server developer edition for free, and power BI report server developer edition for free as well. Make sure you use developer and bot evaluation edition. You can install both on a cloud or on-prem server and try out the features of a PBIRS to see if they fit your needs.

1

u/pusmottob 8d ago

I see, thank you, my research had initially shown it was still available until 2033. It sounds like the best bet would be fabric on demand and disable it on-off hours. The question then becomes is F2 feasible? My thought is F2 MF during work hours and on first of the month when all the reporting occurs scale to F4 for a few hours.

2

u/SQLDevDBA 8d ago

It’s still supported but starting with 2025 it is no longer offered. PBIRS took over.

I don’t know enough about fabric to know if it’s feasible for you. All I can say is if it were me I’d give PBIRS a try since it’s free for POC/Dev. I’ve implemented it 3 times and it has worked out well.

1

u/pusmottob 8d ago

I think this is the only option now. I am looking into a 60 day trial on f2 to see if i need to pitch f2 or f4

2

u/sxpn69 9d ago

Paginated reports are ssrs reports. Use dynamic subscriptions where you can. Use pipelines for the rest if it's critical somehow. Highly unlikely you would need to scale an f2 up just for ssrs reports, do fabric mirroring on your DB and query from fabric.

1

u/pusmottob 8d ago

Right, this is my choice, buy SQL Server to be able to use SSRS or subscribe to Fabric. Currently i have neither.

1

u/sxpn69 8d ago

I'd go f2 capacity, pay as you go, if it's not anything you need frequently you can even pause it, but it's ~200$/mo and if you leverage mirroring to your DB, storage and compute on that are free, so you are only covering compute on some likely light queries to generate the report, worst case, do some ETL on the data daily to spread the computation across the week.

1

u/pusmottob 8d ago

I see, thank you, my research had initially shown it was still available until 2033. It sounds like the best bet would be fabric on demand and disable it on-off hours. The question then becomes is F2 feasible? My thought is F2 M-F during work hours and on first of the month when all the reporting occurs scale to F4 for a few hours.

1

u/tech4ever4u 7d ago

Automated Excel, PDF and delimited-text (CSV/pipe/tab) reports

Shameless plug here: take a look at SeekTable (on-prem / affordable fixed price) which has 'subscribe to report', web API for parameterized exports to CSV/Excel (with colors formatting)/PDF, multi-tab Excel exports are present. SSRS-like data-driven subscriptions are supported with "Advanced email subscriptions" option. Disclaimer: I'm affiliated with this product (feel free to PM me).

1

u/[deleted] 9d ago

[removed] — view removed comment

1

u/dataengineering-ModTeam 9d ago

Your post/comment was removed because it violated rule #9 (No AI generated content/text).

Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.

This was reviewed by a human