Using JasperReports with opentaps

From Opentaps Wiki

Jump to: navigation, search

Contents


Overview

JasperReports is a powerful reporting engine that provides a way to design a report once and generate output in numerous formats including PDF, plain text, HTML and Excel. The opentaps system provides a convenient way to embed and serve these reports from within an application. It also provides a means to automate the creation of input fields for Jasper parameters so that you can create dynamic reports on the fly. Reports can also be served as desired without having to use the features provided by opentaps.

Designing Reports with iReport

A JasperReport is defined in an XML language called JasperReport XML. The schema is complex and lengthly, particularily when it comes to defining the appearance and layout. Instead of writing these files by hand, it is highly recommended to use a report designer that writes this XML. The Jasper report designer is called iReport, and you can learn more about it with our tutorial on using iReports.

Editing the Report

After designing the report, you will most likely be making further modifications. Changing the layout and appearance of the report is best left to iReport. However, some things must be done by editing the XML directly. For example, to prevent the footer from being displayed in formats that are not paginated, such as an Excel spreadsheet or plain text file, a special directive must be inserted in the footer definition.

A typical development cycle for a report might be as follows,

  1. Set up a data source with sample data for the report in iReport
  2. Define the way the report is to be displayed using iReport
  3. Define parameters that should be dynamic and provide defaults in iReport
  4. Edit the XML directly to add special directives
  5. Trial run the report in opentaps
  6. Make further design adjustments in iReport and to XML directly

One thing to remember is that iReport will overwrite any changes you make directly to the XML. Save a backup whenever using iReport.

Serving a Dynamic Report with Opentaps

If your report has $P{param} parameters, opentaps can automatically generate input fields for them and serve the report in the available output formats without much effort. It also allows internationalization via labels.

Registering a Report

First, the report should be registered with opentaps using the following entity xml,

 <ReportRegistry reportId="MY_REPORT" shortName="My Report" description="This is a report about something."/>
 <ReportPart reportPartId="MY_REPORT_01" reportId="MY_REPORT"
     reportLocation="component://applicationName/path/to/MyReport.xml"/>

The location of the report is specified using the component:// notation, which allows you to put the report anywhere in the opentaps system.

If the report is to be located in the purchasing application, then you can add it to the purchasing report group,

 <ReportGroupMember reportGroupId="PRCH_ORDER" reportId="MY_REPORT"/>

Or you could define your own group in purchasing,

 <ReportGroup reportGroupId="MY_REPORTS" description="My Reports" application="purchasing"/>
 <ReportGroupMember reportGroupId="MY_REPORTS" reportId="MY_REPORT"/>  

The application field is required and restricts the reports to that particular application.

Displaying Available Reports

Once the report is registered, it will be shown in a list of reports. If you registered the report in the purchasing application, then by going to the /purchasing/control/manageReports page, you should see your report listed in the appropriate group. Clicking on it should take you to the automatically generated form for your report.

Managing Input Parameters

Input fields are generated automatically for all JasperReport XML parameter fields that have isForPrompting="true". If the parameter is a Timestamp, it will automatically use a time input field. A Boolean field will let you specify true or false.

To generate a lookup field for a party, product and so forth, you must use a parameter name from the following list,

  • productId - Creates a lookup for a Product
  • supplierId - Creates a lookup for a Supplier
  • facilityId - Creates a lookup for a Facility

If your report needs date ranges, you can use CommonFromDate and CommonThruDate, which will opentaps will turn into date widgets. You would need to add them to your jrxml like this:

       <import value="org.ofbiz.base.util.UtilDateTime"/>
       <parameter name="CommonFromDate" class="java.sql.Timestamp">
           <parameterDescription><![CDATA[Default report is for last 30 days.]]></parameterDescription>
           <defaultValueExpression><![CDATA[UtilDateTime.adjustTimestamp(UtilDateTime.nowTimestamp(), 
           Calendar.DATE, -30, $P{REPORT_TIME_ZONE}, $P{REPORT_LOCALE})]]></defaultValueExpression>
       </parameter>
       <parameter name="CommonThruDate" class="java.sql.Timestamp">
           <parameterDescription><![CDATA[Default report is for last 30 days.]]></parameterDescription>
           <defaultValueExpression><![CDATA[UtilDateTime.adjustTimestamp(UtilDateTime.nowTimestamp(), 
           Calendar.DATE, 0, $P{REPORT_TIME_ZONE}, $P{REPORT_LOCALE})]]></defaultValueExpression>
       </parameter>

and then you can use it in your SQL like this:

AND PMT.EFFECTIVE_DATE BETWEEN $P{CommonFromDate} and $P{CommonThruDate}

Internationalization

Any $R{} parameter encountered in the JasperReport XML will automatically be replaced with a uiLabel map substitution. Hence, if you want to internationalize a title using the OrderReportPurchasesByOrganization label,

 <textFieldExpression class="java.lang.String"><![CDATA[$R{OrderReportPurchasesByOrganization}]]></textFieldExpression>

The automatic input fields can also be internationalized, but you'll have to give the corresponding JasperReport XML a name equal to the uiLabel you want to use. For example, if there is a from date that can be specified, the parameter should be named as follows,

 <parameter name="CommonFromDate" isForPrompting="true" class="java.sql.Timestamp"/>

This has a side effect of making SQL queries in the XML reliant on the uiLabel, which is something that will be fixed later.

Serving a Report Directly

Instead of using the above mechanism to publish a report, you may serve them directly and control the details of how the input form is to be rendered and interpreted.

First ensure that the following handler is defined in the controller.xml for the target application,

 <handler name="jasperreports" type="view" class="org.opentaps.common.reporting.jasper.JasperReportsViewHandler"/>

Next, define a view-map that will trigger the report generation. Use the content-type attribute to specify the output format. For example, if we want the URI MyReport to serve MyReport.jrxml as a PDF,

 <view-map name="MyReport" type="jasperreports" page="/reports/MyReport.jrxml" content-type="application/pdf" encoding="none"/>

The location of the .jrxml resource is relative to the webapp/ directory in your application.

The following output formats are available,

  • text/xml
  • text/html
  • text/csv (CSV comma delimited file)
  • application/rtf (RTF)
  • text/plain (Plain Text)
  • application/vnd.oasis.opendocument.text (Open Document Text)
  • application/vnd.ms-excel (Microsoft Excel Spreadsheet)

Using EntityEngine to Populate a Direct Report

When the report is run on the opentaps server, the data could be prepared by a beanshell (.bsh) script and generated using the entity engine. This would override the SQL in the jrxml itself. To use a beanshell script, change the request-handler in the opentaps controller.xml to reference it, like this:

<request-map uri="myReport">
    <security https="true" auth="true"/>
    <event type="bsf" path="/reports/" invoke="myReport.bsh"/>
    <response name="success" type="view" value="myReport"/>
    <response name="error" type="view" value="myReportSetup"/>
</request-map> 

In your beanshell script, if you put a jrDatasource into the request as an attribute like this:

 // set up my query
 iterator = delegator.findListIteratorByCondition("EntityXXXX", new EntityConditionList(whereConditions, EntityOperator.AND), null, selectList, null, null);
 jrDataSource = new JREntityListIteratorDataSource(iterator);
 request.setAttribute("jrDataSource", jrDataSource);

opentaps will use the jrDatasource and ask JasperReports to fill in the report with these parameters. Otherwise, it will use the JDBC connection for the "org.ofbiz" entity group, which is basically everything, as the JDBC connection for your report.

You can also reference internationalized your jasper reports by using $R{...} syntax, such as:

   $R{FinancialsReport.TotalSales.Title}

which would reference the FinancialsReport.TotalSales.Title in the FinancialUILabels.properties file. $R{...} is a resource bundle which uses the opentaps UtilMessages.getUiLabels(..) method to get the UI labels from opentaps. It can be used for text fields in Jasper reports.

References



© Open Source Strategies, Inc. Development of this documentation site is sponsored by Open Source Strategies, Inc.
Help support opentaps with a subscription to this documentation site.