Setting Up Event Driven Reporting in JasperServer Tutorial

Create the database in MySQL

• Create an empty database called gems_eventdrivenreporting
• MySQL command: create database gems_eventdrivenreporting
• Load the sample dump mysqldump.sql into the new database
• MySQL command: use gems_eventdrivenreporting
• MySQL command: source db_sample.sql

Set up the database connection in iReport

• Open iReport and set up the connection to the database

Deploy the Data Source to JasperServer using the iReport JasperServer plug-in

• To deploy the data source to JasperServer you will need to use the JasperServer Repository plug-in
• Navigate to the desired location within the JasperServer repository
  • Example: data sources folder
• Right-click the data sources folder and select Add > Data Source
• Specify the following information for the data source:
  • Name: gem_eventdrivenreporting
  • Label: gem_eventdrivenreporting
  • Select the Data Source Details tab and specify JDBC data source
  • Click on Import from iReport and select gem_eventdrivenreporting

Create a new empty report in iReport

Create a query selecting all fields from the customer table

• Example:  Select * from customer)

Add the following fields: customer_id, lname, fname, city, state, country to the detail band and design the report.

• Add a parameter named opened for the account opened date by right-clicking Parameters and indicating Add Parameter
• Enter java.util.Date, for prompt, with the default value expression new Date(90,0,1)

Change the report query and add a where clause containing the opened parameter

• The full query is in the in the query_1.txt file in the samples folder


WHERE
     customer.`date_accnt_opened` BETWEEN $P{opened} AND NOW() 


Add a title containing the parameters REPORT_COUNT and opened such as:

$V{REPORT_COUNT} + " new Customers since " + new SimpleDateFormat("dd/MM/yyyy").format($P{opened}) 


Tip: Ensure that you set the evaluation time for this field to Report.

Execute the report in iReport; it should look similar to the image below.

You can also view a sample of the report in the samples folder: theReport.jrxml.

Add the report as JRXML Document to the JasperServer repository

Add a Report Unit to the JasperServer repository using the deployed JRXML file as main JRXML as shown in the image below.

Add a local input control of type Date for the opened parameter

Log into JasperServer and run the report


Create a new empty report in iReport

Add a new parameter opened which is filled with the last date of the day one month ago, type java.util.Date, not for prompting, default value 



new Date(new Date().getYear(), new Date().getMonth() -1, new Date().getDate())

Add a subreport to the Detail band using the wizard

• Follow the steps, use the main report on your file system as a subreport resource
• Pass through the parameter opened for the subreport’s parameter opened


Add the report query counting the rows from the original query, and checking if the returned amount of rows is larger than 60.

Note: This query is testing for the number of newly created customer accounts being greater than 60. This query serves as an example and any desired criteria can be defined.

Example: The full query is available in the samples folder: query_2.txt select count(*) as cnt from (<original query>) as tmp having cnt > 60

Add a new header using the parameter opened and the field cnt such as:



$F{cnt} + " new customers since " + new SimpleDateFormat("dd/MM/yyyy").format($P{opened})

Execute the report.

• The report should return no pages as the resultset of the query is empty

Change the subreport expression to the repository URI of the main report’s JRXML such as:

/Event_Driven_Reporting/Common/edr_mainreport

Deploy the wrapper report to the repository as report unit


You can also view a sample report in the samples folder: wrapperreport.jrxml

Login to JasperServer and open the repository

Select the wrapper report and set up a scheduler for this report:

• Follow the wizard
• Set a simple recurrence which runs every 1 minutes
• Set up the output as PDF, which gets overwritten
• Set up email notification
  • Select the option Skip empty reports  (see image below)
• Save the settings

The report runs now every minute, if you check the snapshot folder .

Update 57 rows of the sample database by executing the statement in the samples folder: update_01.txt.

Or, type the following statement into your MySQL frontend:



update customer set date_accnt_opened = STR_TO_DATE(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-',DAY(date_accnt_opened)), '%Y-%m-%d') where YEAR(date_accnt_opened) = 1990


There will not be any notification in the report.

Reset the database to ensure that we get back into the initial state of the database, by executing this statement:



update customer set date_accnt_opened = STR_TO_DATE(CONCAT(1990,'-',MONTH(NOW()),'-',DAY(date_accnt_opened)), '%Y-%m-%d') where YEAR(date_accnt_opened) = YEAR(NOW())

Produce an email notification by changing 93 rows in the resultset; execute this statement, which is also available in the samples folder: update_02.txt



update customer set date_accnt_opened = STR_TO_DATE(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-',DAY(date_accnt_opened)), '%Y-%m-%d') where YEAR(date_accnt_opened) = 1993



You should get an email notification within the next few minutes.