Switch to: V12V11V10V9V8V7V6V5

Generating And Sending Reports By an Email On a Schedule (Valentina Datasource)

Overview

In this tutorial you will learn how to generate reports and send them by an email on a timely basis.

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

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 source query
  • Create a new stored procedure to generate and send a report
  • Create a new event

Requirements

The following applications should be already installed:

  • Valentina Server
  • Valentina Studio

Archives

The following database and project should be registered on the Valentina Server:

Note, that reports use 'sakila' database with Foreign Key links.

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';
You can also modify these properties via the INI file, located in the Valentina Server installation folder.
  • 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.

Needed features should be enabled now, open a connection again and run SQL Editor for it.

Step 2: Send email using the Valentina Server

To send an email, MAIL command is used. For example:

__MAIL 
    __FROM 'support@example.com' 
    __TO 'client@example.com'
    __SUBJECT 'Commercial Offer'
    __BODY 'Dear Mr. Jones, ...' 
    __SMTP 'smtp://localhost' 
    __PORT 25
    __USER 'support@example.com'
    __PASSWORD '123456'
    __SSL TRUE;

Some settings are optional, it depends on the settings of the target mail server.

Digest-MD5 authentication mechanism is currently not supported.

For example, authentication settings for OS X mail server looks like:

You should see a log entry about result of the MAIL command:

If everything is alright, you will receive a message shortly.

Step 3: Prepare a source query

Report_Filmography will be used in this tutorial.

To generate a report it is necessary to know a source query.

  • Open valentina_sakila_reports server project

  • Open Report_Filmography

  • Source query name can be obtained via a Property Inspector, it is Query_Actors
  • Move mouse over the name and you'll see the query text in tooltip:

  • Note query text and switch back to the Schema Editor

Step 4: Create a procedure to generate and send a report

Generation of report and sending it in an email is a multiple-statement operation. Event definition since version 6.2 allows it, but we'll create an intermediate stored procedure that will be called by the event, it makes possible to use one procedure in multiple events.

  • In the database context menu select Create→Function.
  • Give a name for a new procedure: SendReport

  • Switch to the Text tab. Body will contain three parts.
  • Declare source query and cursor. Query will return generated PDF report in Base64 encoding to attach to the message:
DECLARE query1 VARCHAR( 256 ) DEFAULT 'SELECT * FROM actor FOR REPORT valentina_sakila_reports.Report_Filmography AS PDF ENCODE BASE64';
DECLARE cur1 CURSOR FOR query1;
  • Generate a report and save it into variable:
OPEN cur1; 
FETCH FIRST cur1 INTO @report1_data; 
CLOSE cur1; 
  • Attach generated report and send it:
__MAIL 
    __FROM 'support@example.com' 
    __TO 'client@example.com'
    __SUBJECT 'The Film List'
    __BODY 'View attachment, please.' 
    __ATTACH @report1_data AS 'filmography.pdf'
    __SMTP 'smtp://localhost' 
    __PORT 25
    __USER 'support@example.com'
    __PASSWORD '123456'
    __SSL TRUE;

  • Click Create, a new procedure will appear in the list:

  • Now you can execute this procedure via a context menu

Message should arrive shortly.

Step 5: Create a new event

Create an event in the valentina_sakila database and schedule it to run in 2 minutes after creation:

  • In the context menu select Create→Event…
  • Enter a name: FutureReport
  • Set interval to 2, units to Minute
  • Check Enabled checkbox

Note, you can click down arrow list button to select date and time from calendar:

  • Switch to the Action tab and input:
CALL SendReport()
  • 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
  • Click Create

From now a fresh report will be delivered to recipient on a daily basis.