Scheduling Report Generation

WSO2 SP allows you to generate reports with visualizations of processed data as explained in Generating Reports.  You can also schedule these reports to be generated and sent to the required stakeholders when required. WSO2 SP integrates with the Jasper Reports technology in order to provide this feature. 

To create a Siddhi application that generates reports and sends them as email attachments to the required addresses in a scheduled manner, follow the steps below.

1. Start WSO2 Stream Processor in the editor mode my issuing one of the following commands from the <SP_HOME>/bin directory.
   
   • For Windows: editor.bat
   • For Linux: ./editor.sh

   Then access the Stream Processor Studio via the URL displayed in the start up logs.

   The default URL is http://localhost:9390/editor

2. Click New to start creating a new Siddhi application. 
3. In the new Siddhi file that opens, enter a name and a description for the application as shown in the example below.
   

4. Enter the following data to create a basic Siddhi application

   define stream ProductionStream (name string, amount long);

   


   define table ProductionData(name string, totalProduction long);

   


   @info(name='ProductionTotalQuery')
from ProductionStream
select name, sum(amount) as totalProduction
group by name
insert into ProductionData;

   
   

   In the above Siddhi application, basic production data (i.e., the name of the product and the amount produced) is captured via the ProductionStream input stream. Then the total production for each product is calculated via the sum() function, and the results are inserted into the ProductionData database table.

5. To publish the information you processed and saved in the ProductionData table in a report, you need an output stream to which the sink via which the information is published can be connected. Define the output stream as follows.

   define stream ProductionStream (name string, amount long);

   define stream ProductionTotalStream(name string, totalProduction long);



   define table ProductionData(name string, totalProduction long);

   


   @info(name='ProductionTotalQuery')
from ProductionStream
select name, sum(amount) as totalProduction
group by name
insert into ProductionData;

6. To publish the information to be included in the report, connect a sink to the output stream as follows.

   define stream ProductionStream (name string, amount long);



   @sink(type = 'report',title=’Weekly Production’, outputpath='/home/XXX/Projects/Jasper/Reports',@map(type = 'json'))
define stream ProductionTotalStream(name string, totalProduction long);

   


   define table ProductionData(name string, totalProduction long);


    

   @info(name='ProductionTotalQuery')
from ProductionStream
select name, sum(amount) as totalProduction
group by name
insert into ProductionData;

   Here, the sink connected needs to be of the report type. The parameters and annotations configured for this sink are as follows.

   Parameter/Annotation	Description
   title	The title of the report. In this example, it is Weekly Production.
   outputpath	The path to the directory in your macxhine where the generated report needs to be saved.
   @map	The format in which the report needs to be published. In this scenario, you need to generated a report based on a chunk of events. To enable this, the map type needs to be JSON. If not, a report is generated per event.

   For information about all the possible parameters and annotations that can be configured for the report sink type, see documentation for the siddhi-io-report extension.

7. To trigger the periodical generation of reports, define a trigger as follows.

   define stream ProductionStream (name string, amount long);



   @sink(type = 'report',title=’Weekly Production’, outputpath='/home/XXX/Projects/Jasper/Reports',@map(type = 'json'))
define stream ProductionTotalStream(name string, totalProduction long);

   


   define table ProductionData(name string, totalProduction long);



   define trigger SetupTrigger at '0 00 00 ? * SAT';



   @info(name='ProductionTotalQuery')
from ProductionStream
select name, sum(amount) as totalProduction
group by name
insert into ProductionData;

   This trigger polls the ProductionData table at midnight every Saturday to get the latest production data available at that time.

8. To schedule the generation of reports, add another query to the Siddhi application as follows.

   @info(name='TriggerQuery')
from SetupTrigger#rdbms:query('SweetFactoryDB',
"SELECT * FROM ProductionData;", 'name string, totalProduction long')
select name,totalProduction
insert into ProductionTotalStream;

   Here, the SetupTrigger trigger you previously defined is applied to the ProductionData table. An RDBMS query gets all the data in this table and inserts that into the ProductionTotalStream output stream. This is the stream to which the sink of the report type is connected. Therefore, when the Siddhi application is executed, this information extracted from the table and then inserted into the output stream are published in a report.

The complete Siddhi application looks as follows:

@App:name("ProductionReportApp")
@App:description("Scheduled Production Reports")

-- Please refer to https://docs.wso2.com/display/SP400/Quick+Start+Guide on getting started with SP editor. 

define stream ProductionStream (name string, amount long);

@sink(type = 'report', title='Weekly Production', outputpath='/home/XXX/Projects/Jasper/Reports', @map(type = 'json'))
define stream ProductionTotalStream(name string, totalProduction long);


define table ProductionData(name string, totalProduction long);

define trigger SetupTrigger at '0 00 00 ? * SAT';

@info(name='ProductionTotalQuery')
from ProductionStream
select name, sum(amount) as totalProduction
group by name 
insert into ProductionData;


@info(name='TriggerQuery')
from SetupTrigger#rdbms:query('SweetFactoryDB', 
"SELECT * FROM ProductionData;", 'name string, totalProduction long')
select name, totalProduction
insert into ProductionTotalStream;

Using an external JRXML file to generate reports

If you want to use to use an external JRXML file to generate reports, you must include the following in the sink definition.

• The path to the required JRXML file.
• The dataset parameter that is defined in the JRXML file.

e.g., In this example, the path to the JRMXL file is defined as template='/home/../scheduled-reporting/template.jrxml'. The dataset parameter is included as dataset='TableDataSource'.

Including multiple charts in a report

To enable multiple query support, the sink definition should include the following parameters:

• The query.mode parameter needs to be set to true.  
• The datasource.name parameter needs to be included. The value should be the same as the datasource you defined under datasource configurations in the <SP_HOME>/conf/editor/deployment.yaml file when seeting up the database for your scheduled report generation scenario.

Then the queries can be defined as a JSON string as shown in the example below.

@sink(type='report',
  	outputpath='/abc/example.pdf',
  	query.mode='true',
  	datasource.name='POPULATION_DATA',
  	queries="""[{"query":"SELECT * FROM SampleTable;","chart":"table"},
              	{"query":"SELECT Value, Selection FROM SampleTable;","chart":"line","series":"Value","category":"Selection",
               	"chart.title":"Sample chart"}]""",
  	@map(type='json')
  	)

The queries should be provided in the JSON format where the query, chart type the query data should represent, the series and category column names, the chart title.