Table of Contents
Generating And Sending Reports By an Email On a Schedule (Any Datasource)
In this tutorial you will learn how to generate reports and send them by an email on a regular basis. MySQL database will be used as a datasource, but it is possible for all supported datasources.
The best way to achieve this goal is to use the Valentina Server, because:
- It can send emails
- It includes Valentina Reports engine
- It is able to attach generated reports to the emails
- It has a built-in event scheduler to perform all of these tasks regularly or at the fixed time in the future
During this tutorial you will go through the following steps:
- Prepare the Valentina Server for email sending and event scheduling
- Send email using the Valentina Server
- Prepare a database
- Prepare a project
- Prepare a datasource
- Prepare a source query
- Generate a report via SQL
- Create a new event which generates a report and sends it
The following applications should be already installed:
- Valentina Server
- MySQL Server
- Valentina Studio
Access to the SMTP server is required. It may be some third-party service or your local server, like mail server from OS X Server package, which was used to prepare this tutorial.
- PROJECT file - universal_report.vsp.zip - Download project archive file
- DATABASE - Sakila - Here you can find a link to archive of 'sakila' database for MySQL server.
Step 1: Prepare the Valentina Server
Mailing and scheduling features are disabled on the server by default, so the first thing to do is to check them.
- Start Valentina Studio
- Connect to the Valentina Server. You can click a Bonjour connection on the Start Page or on the left panel of the Schema Editor.
Also you can create a new connection, using a dialog, shown by Connect to… button:
- Click Open SQL Editor… button
- Execute the following command:
SHOW PROPERTIES OF SERVER
- MAILENABLE and SHEDULEENABLE should have '1' as a value, if it's true, you can move to the step 2
- Execute the following commands to enable required features:
SET PROPERTY MAILENABLE OF SERVER TO '1'; SET PROPERTY SCHEDULEENABLE OF SERVER TO '1';
- Changing the SCHEDULEENABLE property of a server requires a restart. So close Valentina Studio connection and restart Valentina Server
On OS X System Preferences panel can be used for restart:
- Open System Preferences application
- Click Valentina Server icon
- Click Stop and then Start
On Windows and Linux it is done via standard service-management interfaces.
Step 2: Send email using the Valentina Server
Valentina SQL MAIL command is intended for sending emails.
Valentina Server sends mail via external SMTP servers. Settings, used in the MAIL command, depend on the parameters of the target mail server.
You can see authentication settings for OS X mail server, used for this tutorial:
Let's send an email:
- Reestablish connection to the Valentina Server
- Open SQL Editor and execute the following code:
__MAIL __FROM 'firstname.lastname@example.org' __TO 'email@example.com' __SUBJECT 'Commercial Offer' __BODY 'Dear Mr. Jones, ...' __SMTP 'smtp://localhost' __PORT 25 __USER 'firstname.lastname@example.org' __PASSWORD '123456' __SSL TRUE;
You should see a log entry about result of the MAIL command:
You will receive a message shortly.
Step 3: Prepare a database
In order to generate a report, it is necessary to prepare some data for it. Example MySQL database “sakila” will be used as a datasource.
- Prepare SQL dump files for loading to the MySQL server.
- Connect to the MySQL server:
- From the connection context menu select “Load Dump…” and input a name of target database “sakila”
- Go through the wizard and load a schema file
- Now load a data dump
You have a database, ready for report generation:
Step 4: Prepare a project
- Select Valentina Server connection, select Projects in the drop-down list above the first column.
- Unarchive “universal_report.vsp.zip”, copy project into “projects” folder of the Valentina Server directory
- Return to the Valentina Studio and click refresh button at the main toolbar. New project will appear
- Double-click it to register
Step 5: Prepare a datasource
We've just loaded a new database to the server and it is good idea to check, if a report and a database work well together.
- Double-click a project to open it
- Select “valentina_sakila” datasource and click Edit…
- Select “sakila” database on MySQL server and click OK
- Open a context menu of a datasource and select Rename…, give a new name “mysql_sakila”
- Open “Report_PaymentsOfCustomers”, switch to Preview mode. Now data should be taken from MySQL server
Step 6: Prepare a query
- Back to Design mode, expand data node in the Property Inspector
- Query name is “Query_Customers”
- Close a report, select “Query_Customers” in list and click Edit…
- Copy the query text, it will be used in the next step
Step 7: Generate a report via SQL
To generate a report via SQL REPORT ... FROM command is used.
It requires a few important parameters:
- Project and report name: “universal_report”.“Report_PaymentsOfCustomers”
- A datasource string:
mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306
- Query text:
SELECT * FROM customer
- Output format: PDF
Now we have everything to generate a first report.
- Switch to the Schema Editor, select the Valentina Server connection and click “Open SQL Editor…”
- Type the following command and click “Execute…”:
REPORT "universal_report"."PaymentsOfCustomers" FROM 'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306' USING 'SELECT * FROM customer' AS PDF
- After some time a generated report will be returned in the result:
- Make a double click on the cell to view returned data:
- Save it to file and open
For attaching to the email a report must have BASE64 encoding, which is done adding ENCODE BASE64 keywords to the query:
REPORT "universal_report"."Report_PaymentsOfCustomers" FROM 'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306' USING 'SELECT * FROM customer' AS PDF ENCODE BASE64
Step 8: Create a new event
Create an event and schedule it to run in 2 minutes after creation:
- In the context menu of Valentina Server connection select Create→Event…
- Enter a name: FutureReport, database field leave blank
- Set interval to 2, units to Minute
- Check Enabled checkbox
- Check Preserve checkbox to save event for future work after completion
- Switch to the “Action” tab
- As report generation and email sending is a multiple-statement operation, let's start with BEGIN … END block:
- Declare query variable in it:
DECLARE query_report VARCHAR( 256 ) DEFAULT 'REPORT "universal_report"."Report_PaymentsOfCustomers" FROM \'mysql://host=127.0.0.1, user=root, password=root, dbname=sakila, port=3306\' USING \'SELECT * FROM customer\' AS PDF ENCODE BASE64';
- Declare cursor
DECLARE cursor_report CURSOR FOR query_report;
- Generate a report and save it into local variable:
OPEN cursor_report; FETCH FIRST cursor_report INTO @report_data; CLOSE cursor_report;
- Attach generated report and send it:
__MAIL __FROM 'email@example.com' __TO 'firstname.lastname@example.org' __SUBJECT 'The Film List' __BODY 'View attachment, please.' __ATTACH @report_data AS 'filmography.pdf' __SMTP 'smtp://localhost' __PORT 25 __USER 'email@example.com' __PASSWORD '123456' __SSL TRUE;
- Switch to SQL Preview tab and check generated query
- Click Create. A new event will appear in list.
- Wait for 2 minutes for event execution.
After a message has arrived, create a repeating event.
- In the context menu select Create→Event…
- Enter a name: DailyReport
- Switch Schedule from One-time to Repeat
- Set Interval to 1, Units to Day
- Check Enabled checkbox
- Switch to the Action tab and input the same query
- Switch to the SQL Preview tab and check generated query:
- Click Create
From now a fresh report will be delivered to recipient on a daily basis.