Thursday, October 25, 2012

Jasper Report Bursting with Pentaho Data Integration (Kettle) using a REST service

Jasper Report Bursting with Pentaho Data Integration (Kettle) using a REST service

JasperReports Server 4.7 comes with some very useful REST services, one of them being a report creation service. The nice thing about this is that it is extremely easy to call this service.

Imagine our company has several regions and each regional managing director should receive a customized report. As the layout is the same for every region, we can just create one report that we parameterize so that we can display the data that is relevant for each region.

We have the contact details stored in a central database. As other projects already make use of Pentaho Kettle for data integration purposes, we decide to use it as well for our project. The outline of our report bursting process is as shown below:

So we first get all the contact details and then execute the report generation process for each one of these contacts.

As we want the whole process to be easily configurable we add these variables:

The transformation for retrieving the contact details is quite straightforward: Get the data from the database table, check if the email address is valid and copy rows to results:
Let's have a look now at the report generation process, where things get more interesting: 
So we set all the variables (the values are the contact details), we set the variable for the temp directory, then we create the report folder inside the temp directory if it doesn't exist already, after this we delete any files that could be in this report directory already. 

Now comes the important part: We use the HTTP job entry to call the JasperReports Server REST service.

In the General settings of this job entry we provide following URL details:

${VAR_JRS_URL}/rest_v2/reports/${VAR_JRS_DIR}/${VAR_REPORT_NAME}.${VAR_REPORT_OUTPUT_FORMAT_EXTENSION}?j_username=${VAR_JRS_USERNAME}&j_password=${VAR_JRS_PASSWORD}&CUSTOMER_ID=${VAR_CUSTOMER_ID}

As you can see this URL gets dynamically created by using some of the variables we set in the main job and also one report parameter value we retrieved from the contact details (VAR_CUSTOMER_ID). There are various output formats available for the report, in this example we use pdf, but it could also be xls, html etc.

The webserver reply should be stored in the target file (in example):
${VAR_TEMP_DIR}${VAR_REPORT_DIR}/${VAR_REPORT_NAME}-${VAR_CUSTOMER_ID}.${VAR_REPORT_OUTPUT_FORMAT_EXTENSION}


As there might be cases where for a certain parameter value there is no report data and hence no report created, we want to next check if there was actually a report created. If there is no report available, we just end this job gracefully, otherwise we execute the transformation which sends out the email:


In this transformation we get following variable values and assign them to fields:

The path to the report is dynamically created by using this Java expression:
TEMP_DIR + REPORT_DIR + "/" + REPORT_NAME +"-"+ CUSTOMER_ID + "." + REPORT_OUTPUT_FORMAT_EXTENSION

Finally the email settings are defined and the email is sent out with the report as attachment.

Most work in this process is dedicated to creating the report bursting functionality, accessing the JasperReports REST service is actually the easy part. The big advantage of this approach is also that you do not need a dedicated plugin to execute the Jasper report on the server and get the result back.



9 comments:

  1. Good solution; in my opinion jasper server, as modern bi platform, should include bursting and advanced delivery feature itself.

    ReplyDelete
    Replies
    1. Thanks a lot for your feedback. You can actually create a report bursting job as well in their white labeled ETL tool (Talend).

      Delete
    2. can you please give an example of report bursting job with Talend etl tool.

      Delete
    3. I found this via Google: http://community.jaspersoft.com/project/tjasperserverexec/resources

      Delete
  2. Hi can you please share the steps used in transformation

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Which transformation exactly? There are three transformations involved in this process.

      Delete
  3. Can you share all the transformations you used in this solution? Specifically I think I am missing something in your step where you call the REST service and receive the file back. Its not clear how you specify where the resulting file should be saved (I don't see any "output file location" parameter where you designate the TEMP location in any of the transformation steps...or maybe I am not picking the right type of steps. Any help would be greatly appreciated.

    ReplyDelete
    Replies
    1. The part where I get the report from the server is in a job not in a transformation. There isn't really much to it: In the HTTP job entry define where you want to get the file from (URL property) and where to save it (target file property), all described above starting from "Now comes the important part: We use the HTTP job entry to call the JasperReports Server REST service." I added a screenshot above to illustrate this in a better fashion.

      All the other bits and pieces are just the icing on the cake. The essential part is using the HTTP job entry.

      Delete