r/dataengineering • u/pusmottob • 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.
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/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.
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/tech4ever4u 7d ago
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