Report generator to send performance pie charts to customers by email

Question

We need to send every X days reports to our customers, telling them about their performance these days. The report includes calculated numbers and pie charts and should be sent by email. Our data is stored on MySql.

I am looking for a good tool that will help me generate these emails.

Requirements

• Be able to get arguments from outside (timeframe, customer id, etc.)
• Be able to access the datasource, and perform some queries according to the arguments
• Be able to generate multiple reports in one click, and send them by email (or export to html, we have mail server)
• Be able to generate pie charts, according to the arguments.
• Optional: free tool.

I tried jasper, but beside many bugs it has, it was too complex to generate multiple emails according to arguments.

Context

We have a web application with REST API. So, we can have a standalone application that access the DB, or standalone application that access REST API.
In addition, it can be integrated in our web application (written in java, deployed on tomcat + linux). So we are flexible.

Answer 1

By what you describe i think you need to take a look at DBxtra, this is a BI tool that can generate reports easily and send them in an automated fashion every certain time (like daily, weekly monthly, and so on), regarding your requirements it can:

• Take arguments (parameters)
• Access the database directly (Direct connection to MySQL)
• Generate multiple reports in the same batch (Scheduled Jobs)
• Chart and everything is created from the data source (that can take parameters)
• Paid

Also, it requires the .NET Framework (and thus, a Windows computer) for it to run, but you can easily embed the reports in your web application if you want to, so your front end can be any technology you want.

P.S.: I'm a DBxtra evangelist.

Answer 2

Pentaho can do that. It is free and open source.

The steps would be:

• Add your MySQL database details to the Pentaho data sources
• Using Pentaho Report Designer, design your report using parameters and getting data from MySQL
• Every X days, call Pentaho Report Designer with the parameters you want, either from the command-line or from the Pentaho web interface or from a bigger Pentaho job.
• If you need improved performance, you might also pre-process the SQL data using Pentaho's business intelligence tools (this too would be a part of the "job").

Pentaho Report Designer (PRD):

Designing a job in Pentaho Spoon:

Adding a reporting step to a job. Note how you can define the parameters that you will be able to use when designing the report in PRD:

Adding a "Mail" step to a job:

Similarly, you can also add steps to retrieve data from you REST API if needed. It is rather easy to show the generated reports in a web interface too, or even integrate dynamic dashboards where people can zoom/filter/drill down/etc.