Developing Jasper Report Solutions for Opentaps
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.
The basis for this tutorial is the customer statement PDF that can be generated in the financials application.
It is generated from the Balances by Customer report in the receivables tab. There are many features built into this particular document, which makes it an ideal candidate for study.
The PDF contains one statement for each party that was selected. Each statement contains the following information,
- Customer's billing address (or print not on file)
- Company's logo, address, phone number and other contact information
- A list of invoices that have open (unpaid) amounts
- If the invoice has a due date, then show the number of days since (age date)
- All invoice payments made in the 30 days prior to the statement date
- If the payment is to an invoice that was already paid off, then display the closed invoice for reference
- Total open amount, which must be paid by customer (sum of open amounts)
- Date brackets showing how much is past due and when.
- If any amount is past due, a large PAST DUE will be displayed on the statement
- Layout designed for perforated page with perforation running down the right side
- Information repeated on both sides for purposes of customer and return receipt
As you can see, this is a complex set of information that must be further grouped by partyId. We will cover all the steps in creating this document.
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 event that reads the form via an HTTP request. The event is a method that will generate the report parameters and data set using a special convention. Next, we tell the controller to treat this request using the "jasperreports" view handler. At this point the jasper report will be generated by the system when the form is submitted.
From this point, you will be going back and forth between iReports and opentaps until the report is finished. At this time, there is no way for iReport to connect directly to the opentaps system to use your data generation method, so you will have to rely on the mock data to see the output in iReport.
Next, we will go over these steps 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,
Advanced iReport Tips
Before we begin, there are several general points about iReports that are not well documented:
- If not specified, the dimensions of an item are in pixels
- iReports uses a fixed 72 pixel per inch conversion
- Example: Edit the band properties and the height box will be in pixels
- This means you should convert your measurements to pixels using the 72 pixel an inch rate
- All elements can be positioned and sized precisely using the properties window -> Common tab.
- 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.