New reporting platform - Suggestions, Usecases?

Other SQL Server Topics
, ,
1

I would like to present the following use case and ask for suggestions on the best approach we can use in this situation:

My company provides customized reports on demand to our customers.
Customers buy new customized reports on our database. They will be available in the administrative environment of each client on our site. These reports are developed by us and implemented in VBA (Word and Excel). Usually, we develope a stored procedure that will provide data to the report and VBA is used to run the stored procedure and supply data to a spreadsheet or word document. VBA is dealing with a report queue and sending e-mail to customers with these reports attached. This structure has existed for about 12 years and we believe that it is necessary to improve this proccess. The main problem is the VBA instability to the volume of cases, high volume of decentralized code and difficult to maintain (typical procedural code).

We would like to implement improvements that will improve the quality and delivery time of these projects. Especially ensuring stability in the processing of reports and better error handling, among other expectations we have. And I would like to read suggestions and use cases that can assist us in making decisions.

Our doubts and ways we think of tread:

  1. Replacement of VBA technology for a modern platform that adds time and quality in the generation of reports, since VBA is quite unstable. Including the SERVER and also SUBMITTED REPORT. We thought of many options: Jasper Reports, Pentaho, Crystal .. and continue research, but still no conclusive results, since I must ensure that the new technology could replace the VBA in everything he does;
  2. The creation of stored procedures for data extraction for the report is the same as the best one? Or should we start from for BI tools that can assist in data extraction more efficiently? Something to help us to configure the data extractions easier.

Our plataform:

  1. SQL SERVER;
  2. Windows machine with VBA runing (control the report queue and proccess/generate reports).

We are open to platform changes. But with respect to use another database management system, it can be a little tricky… as in most cases, I believe.

What do you recommend? What usually employ on your company? Your suggestions will be most welcome!

thank you!

2

SSRS is the logical choice

3

I have to agree with gbritton. SSRS is a great choice. Easy to write, maintain, it's fast, extensible, and perhaps best of all, free depending on how you implement it.

4

Agreed, Reporting Services is the best choice. You can even set up reports to do "drill downs" based on passing values from one report to another. Of course it's not a custom tool or custom code, so others would be able to duplicate what you wrote.

You could pair it with SSIS if you need to copy/convert/transform data for reporting.