Report distribution tool

Question

we are looking for a SW/tool which would help us to automate the reports distribution.

We use MS SQL Server 2016 and SSRS (Sql server Reporting Services), we have a knowledge of SQL coding.

Our current manual process:

We have a VIEW showing sales data (by invoice line, with customer details and salesrep name) which we run/refresh in Excel, put it in the Pivot Table, copy/paste in blank file as regular table and then email to a set of users. The format of such report see below.

Our goal

We want to send each user only his/her data (only those lines where he/she is identified as the salesman). This can be done in SSRS, but because we have 40 salesmen, it requires creating 40 jobs, which is hard to build and maintain.

SW requirements - ability to save report in many formats (XLSX, PDF etc)
- ability to combine several reports in 1 XLSX file (on different sheets)
- ability to export reports to emails, folders, webpage etc.
- ability to have multiple reports attached in 1 email
- ability to schedule reports generation or have it triggered by an event

Other info - either commercial or opensource sw is fine
- budget: we are willing to pay maximum "hundreds of usd" per perpetual licence
- we need to use this tool daily
- we will generate around 100 reports daily

Answer 1

Here's a SQL scripting tool that can do most of what you're looking for: https://execsql.readthedocs.io/en/latest/. Disclaimer: I wrote it. It has been in daily use by myself and my colleagues for several years. Of the features you are looking for, it can:

• Save database exports in 15 fixed formats plus writing reports in templating languages. It exports to .ODS instead of .XLSX. PDF files can be created by exporting to Markdown and using pandoc to convert them to PDF (see Example 8).

• Combine several reports in one ODS file on different sheets.

• Exports can be written to folders and written to HTML. It can send email, and attach report files that it has just created.

• It only supports one attachment per email, so if you want multiple reports per email, they should be on separate spreadsheet pages.

• It does not have an internal scheduler; scheduling should be done at the operating system level.

Eliminating the manual creation of the pivot table would probably be a big time savings; have you looked into using SQL Server's PIVOT command?

Creating separate reports for 40 salesmen is straightforward: Example 6 in the documentation illustrates how to do this.