Developing Jasper Report Solutions for Opentaps

Overview

In this document we will cover the full development of a Jasper Report solution for opentaps. The goal is to cover enough information so that you can create any kind of report or document desired.

Example to be Used

There is a customer statement in the financials application that was made using Jasper Reports. Here is an example of its PDF output,

• File:CustomerStatementExample.pdf

To generate it, go to the Balances by Customer report in the receivables tab. You must have several invoices with unpaid amounts that are in the ready state. Run the balances as of today and you should be able to select a list of customers and print out statements for all of them.

There are many features built into this particular document which makes it an ideal candidate for study. Each statement contains the following information,

1. Customer's billing address (or print not on file)
2. Company's logo, address, phone number and other contact information
3. A list of invoices that have open (unpaid) amounts
   1. If the invoice has a due date, then show the number of days since (age date)
4. All invoice payments made in the 30 days prior to the statement date
   1. If the payment is to an invoice that was already paid off, then display the closed invoice for reference
5. Total open amount, which must be paid by customer (sum of open amounts)
6. Date brackets showing how much is past due and when.
   1. If any amount is past due, a large PAST DUE will be displayed on the statement
7. Layout designed for perforated page with perforation running down the right side
   1. Information repeated on both sides for purposes of customer and return receipt

As you can see, this is a complex set of information. It is further complicated by the fact that we have to print one statement for each customer in the same document. Yet, it is not a difficult task to do using Jasper Reports in conjunction with Opentaps. We will cover all aspects of developing this document. It should provide a sufficient foundation from which to develop any kind of report desired.

Development Process Overview

The first step in the development is to create a Jasper Report (.jrxml) document using JasperSoft iReport. The goal should be to create the basic layout and formatting for the report using a good set of mock data.

Once you have a report, you can write the form for it and a Java or Beanshell method that reads the form via an HTTP request. The method will be responsible for generating the report parameters and data set. Next, we tell the controller to treat this request using the "jasperreports" view handler. At this stage, the jasper report should be generated by submitting the form.

After this, you will be going back and forth between iReports and opentaps until the report is finished.

Next, we will be covering this development process in detail.

Creating the Initial Jasper Report Document

This section assumes basic familiarity with iReports. You may want to read our introductory tutorial to iReports first.

Initially we should set up enough mock data so we can format the various fields ahead of time. The simplest technique is to set up mock parameters that either have default values or are set up for prompting so you can enter values when the report is run.

Of course, some of the parameters will be used in the final report. These should be set up for prompting. For instance, the customer statement takes the date of the statement and a set of fields that produce the organization logo and address.

For reference, you may wish to load the Jasper Report document for the customer statement in iReports. It can be found in your opentaps installation,

hot-deploy/financials/webapp/financials/reports/repository/CustomerStatement.jrxml

Advanced iReport Tips

Before we begin, there are several general points about iReports that are not well documented:

1. If not specified, the dimensions of an item are in pixels
   1. iReports uses a fixed 72 pixel per inch conversion
   2. Example: Edit the band properties and the height box will be in pixels
   3. This means you should convert your measurements to pixels using the 72 pixel an inch rate
2. Elements
   1. All elements can be positioned and sized precisely using the properties window -> Common tab.
   2. You can find all features supported by an element by clicking on the All tab of the element's properties window

Generating a Logo Image

One way to place the logo on a report is to have Jasper Reports fetch it from the internet. (Only works if your opentaps instance has internet access!) The logo URL is specified as a String parameter. In the case of the customer statement, the parameter is logo_url and by default we pass it the opentaps logo http://www.opentaps.org/images/opentaps_logo.png.

To place the image on the document, use the Image tool and draw a box. This box will be the boundaries of the image.

Next, edit the properties of the image and change the width and height to the desired size. The image will be scaled to fit. Once you do this, you can align the image with respect to the other document elements.

Finally, we need to tell the image to use our logo_url parameter. In the properties box, click on the Image tab. We are interested in creating a URL object. The image element will use this to fetch the image automatically.

Next time you run the report the images should be generated.

Mock Data Set for Detail Band

A basic Jasper Report will use a SQL query to extract a data set for the detail band. In the case of customer statement, the data for the detail band is too complex for a SQL query. Later on we will create a program that will build this data set, but for now we can rely on a SQL query that returns a mock data set. The query will simply select data from the Invoice table while grouping the results by the customer partyId.

SELECT 
    party_id, 
    'INV' AS type, 
    invoice_id, 
    invoice_date AS transaction_date, 
    due_date, 100.0 AS invoice_total, 
    100.0 AS open_amount, 3 AS age_date, 
    invoice_id AS invoice_id_2
FROM invoice
ORDER BY party_id

Note that the query does an order by party_id instead of grouping. There are two reasons for this. First, the list we are going to be generating from our program will not be grouped due to the nature of the interface we will be using. Second, the Jasper Report can handle the grouping as we will see shortly.

Also note we are selecting some made up data for some of the fields. Our data generation program will be handling these fields in the future, but we need to see the data now so we can format it.

Finally, the invoice ID is selected twice into different field names because there are cases when we need to print the invoice ID on the receipt portion but not on the return portion. Our data generation program will decide which field will be filled in.

If you opened the customer statement in iReport, go ahead and enter this query in. You will need to change the fields that are BigDecimal to Double, and Integer to Long. (The data set generation program creates Doubles and Longs for these fields.)

Grouping an Ordered Data Set

If you have a data set that is ordered according to one field, such as party_id in this case, then we can group them in Jasper Reports. Click on the Report Groups tool,

Add a new group and specify the party_id field as the expression.

There are also options that control whether page numbers should be reset each group and so on. They were checked according to what the statement report needs.

The report should now be grouped by party_id.