Tutorial iReports

Overview

In this tutorial, we will cover the use of creating a document in iReports and integrating it with opentaps. Various techniques are covered to assist in creating a report that can be served as PDF, Excel and other formats. Also covered is how to allow users to enter date ranges, product IDs and other dynamic parameters to constrain the report.

Installation of iReport and configuration are not covered.

Imagine that we want to report the quantity ordered of each product in our store. This is a fairly straightforward report that involves aggregation, grouping and later on filtering by date and product.

Creating A New Report

There is a wizard to help in creating a report, but it hides important details about the anatomy of a report. Therefore, we will be covering report creation from scratch. First, create a new report using File -> New Document. You should get a blank page and some information in the Document Structure window on the left.

Report Bands

By default there are several sections on the page which are faintly labeled as title, pageHeader, columnHeader, detail, columnFooter, lastPageFooter and summary. These are called bands and they have special behavior with regards to how they print the contents on paginated formats such as PDF.

As you might have guessed, bands such as pageHeader, and footer are repeated every page. This is a good place for things that don't change much on a page by page basis, such as the title and page numbers. Usage of title, lastPageFooter and summary are for the title page and lst pages. Their use will not be covered here. For now we can hide the bands we are not going to work with by dragging on the borders and reducing the band height to 0. Alternatively, you can right click on a band and select Band Properties and reduce the size of the bands to 0 by hand.

For the purposes of this tutorial, we will be using only the pageHeader, columnHeader, detail and pageFooter bands. Collapse the other bands to size 0 until the page looks as follows.

Inserting Text

Next we're going to insert some static text for the pageHeader. To insert a static text element, either select it from the dropdown menu or click on the icon. Both are shown below.

Once you click on the desired location for the element, a re-sizable box appears. Move the box to the pageHeader band and resize it to take up the entire band. You'll notice that iReport provides snapping and hints when you're aligned with the edge. We will use these extensively when aligning data so that the output is tidy and suitable for spreadsheet output.

To change the text, you can right click the element and select Properties. Alternatively, you can double click on the element in the Document Structure window to bring up the same properties dialog. It has several tabs and allows you to specify all kinds of details from the font to the alignment details. For now let's just change the text as follows.

After closing this window, our header is now displayed. We can use the toolbar on top to change the alignment, font and other details.

Once you get it vertically and horizontally centered with a nice bold size, it should look something like this,

That is all there is to entering static text.

Creating A Query

Next let's load some data up for our report. We can use the opentaps database directly, which helps us ensure the report is correct. You can create a new data source from Data -> Connections/Data Sources. This is a standard JDBC data source setup that you might be familiar with from other tools. The key points are to select the correct JDBC driver for your database, make sure it is in the iReports classpath (or copy the driver .jar to the iReport lib/ directory), and specify the connection details from your entityengine.xml if you are writing a report for opentaps ERP + CRM or config/jdbc.properties if you are writing a report for opentaps analytics.

Once the data source is set up, you can create a query using Data -> Report Query. The tutorial query can be as follows,

At the bottom of this window you'll see that the selected fields were detected as String and Double types. If it doesn't understand the query, it will print an error instead. So when the fields are listed, you know you have a good query. This setup has the Automatically Retrieved Fields checked by default, so it will check your query as you type. Uncheck it if this is not desired.

MDX queries and Mondrian OLAP datasource

In order to use MDX, we have to configure a connection to Mondrian OLAP server first. This requires a configured JDBC connection pointing to the data and the URL of an XML file containing the definition of the scheme.

To create Mondrian OLAP connection follow these steps:

1. From Connection/Datasources dialog box click New and select Mondrian OLAP connection.
2. Select from the drop-down the connection pointing to the data and set the schema location.
3. You may click Test to verify connection.
4. Set the new OLAP connection as the active connection in Data -> Set Active Connection dialog or toolbar drop-down.

Once the connection is created we can continue with query. Open the Report Query dialog (Data -> Report Query) and set MDX as a query language in the Query language drop-down. Go to the text area query itself and click the button Read Fields. You should see something like the following in the window:

The tree in the right side of the window will be filled with measures, dimensions, and hierarchies of the query.

At this point we can start to define the field mapping. To begin defining the field mapping, proceed by creating the field to map the Unit Sold measures:

• Double-click the Unit Sold item in the tree under the COLUMNS branch.
• Set Field name to Unit Sold and Expression to Data([Measures].[Unit Sold],?).

The expression proposed by iReport is a typical expression to identify a specific cell in the MDX result collection. The syntax is Data(Measure, Tuple). The question mark in the example expression is a way to simply point to the current cell. Please note that iReport is not able to identify the field type. It will be your responsibility to set the correct type (text, numeric, date, or Boolean).

• Set the type to Numeric and click Add field to add the field to the fields list.

Repeat the preceding steps to add the rest of measures to the fields list too.

Now we can map all the values coming from the Product dimension to some other report fields. Start by adding a field to store the product name. When you deal with a dimension that does not contains measures, you have to define a field yourself. You start by setting the field name: ProductName. If you want to simply get the member name as a field value, double-click the desired member (the item product_name.name in the tree). The following expression Rows[Product][product_name.name] exactly represents the name of the product member labeled Product Name.

Repeat the steps to add to report fields all the hierarchy levels: brand.name, category.name, product_type.name.

Next, use the Report Group Wizard (Edit -> New Report Group Wizard) to create some groups to aggregate data. Starting from brand.name, add a group for each level in the hierarchy (excluding ProductName, as it’s the maximum detail that you can reach).

Finally, you want use MDX to calculate some subtotals. In effect, all this data is contained the OLAP cube, and it does not make sense to calculate it again using JasperReports variables.

Syntax to map a subtotal is as follows:

Data(set)(tuple)

To calculate the total of Units Sold for all products, use the expression Data( Rows[Product][(All)] )([Measures].[Unit Sold],?) Add the subtotal of the Units Sold for the category level and the grand total of the measure:

• CategoryUnitsSold:

Data( Rows[Product][Product Category] )([Measures].[Units Sold],?)

• TotalUnitsSold:

Data( Rows[Product][(All)] )([Measures].[Units Sold],?)

Both the fields are of type java.lang.Number.

Finally, here is a sample of the report design. It includes one measure, Units Sold, and one aggregation level:

Inserting Fields

Once the query is accepted, save the file. The query itself will be embedded in the .jrxml file. We will be looking at this later. For now, let us return to the Document Structure window on the left and examine the contents of the Fields category. These are the fields that result from the query.

We can drag a field from the Document Structure onto a band. In this case, we should drag them into the details band. The details band is the location for data output and will flow into the next page if there is more than one page of data.

Tip: To avoid displaying null values, right-click on the text field's Properties and check the "Blank When Null" box.

Now that we've placed them on the details page, let's see how the report looks.

Operations on Fields

Since your fields are objects of Java classes, they also supports the operations operations of those Java classes. For example, if fieldA and fieldB are java.math.BigDecimal, thin one of your fields could be the difference between these two fields:

<textFieldExpression class="java.math.BigDecimal"><![CDATA[($F{fieldB}.subtract($F{fieldA}))]]></textFieldExpression>

Formatting a Field

You can format a field by using the Pattern property of a field, and then choosing from options such as date, currency, and number:

You can also modify the pattern manually, such as changing yyyy/mm/dd to yyyy-mm-dd

Previewing The Report

From the Build menu, select the JRViewer Preview. This will render the report in an iReport native way. Then we can run the report by selecting the Execute (with active connection) option either from the Build dropdown menu or from the toolbar. Both methods are shown below.

If you have some completed orders and have the tutorial report as described above, it should show your report, otherwise you get a message about no pages created. An example of the output is provided as PDF format, which was itself generated using the iReport Execute action,

• File:TutorialReport1.pdf

If you look at this report, you'll notice that the pageHeader is repeated every page and that the data is listed, but with an extreme amount of padding. We will cover how to format this better in the next section.

Formatting The Report

The excess space in the above PDF is due to the spacing around the field elements in the details band. If the report is to be tabular and published to a spreadsheet or plain text format, it is recommended to align them in a way that is even. The following screenshot shows a better alignment for the tutorial report with better margin control.

This was achieved by utilizing the zoom dropdown to magnify the details band to better see the alignment and work with the snapping and edge detection hints provided by iReports. It is helpful to use up the entire band when positioning the elements.

The field elements were vertically aligned using the same Align Vertical Axis toolbar item that was used to align the pageHeader. Each field element was also given a bottom border using the border selection widget in the same toolbar.

Notice also how there is almost no space left over in the details band. This band represents a row and will get repeated over and over until it spills into the next page.

Adding Column Headers

Next we can add some headers. This is as simple as adding static text to the columnHeader band.

To spice things up, a rectangle with a colored background was added as the first element in this band. You can position elements relative to each other by right clicking on the element name in the Document Struture window and moving them up or town relative to each other.

Adding Page Numbers

iReport provides us with some standard variables which are listed in the Document Structure. Using the $V{PAGE_NUMBER} variable, we can insert some text to print the page numbers. To do this, drag the PAGE_NUMBER variable over to the pageFooter band and edit its properties. You can change it say Page ${PAGE_NUMBER} like so,

Changing background for alternating lines of report detail

If you want to have a colored background for every other line, you can do it by putting a colored rectangle inside of the detail line boundaries and assign Print when expression:

1. Insert rectangle in detail band with height equal to band height and required width. Usually it is equal to the page width without margins. In its Properties dialog on Graphics Element tab set Pen to None and assign color in Common tab -> Background. You should set Common -> Stretch Type to "Relative to tallest object" as well.
2. Create new variable in Add/modify variable dialog (View -> Variables -> New). Set its attributes in the following way: Variable Name to any name, e.g. lineIndex, Variable Class Type to java.lang.Integer, Initial Value Expression to Integer.valueOf(0), Variable Expression to Integer.valueOf($V{lineIndex}.intValue() + 1).
3. Go back to background rectangle properties and assign expression Common tab -> Print when expression to Boolean.valueOf(!(($V{lineIndex} % 2) > 0)).

Save and run report. Each even line will has selected background.

Final Report Preview

Once all this is done, we have finished the report layout and formatting. The final report looks like this,

Here are the results in PDF.

• File:TutorialReport.pdf

And this is a screenshot of what the Excel output looks like.

Adding Parameters

Sometimes you will need to add parameters to your report, such as the product, customer, or organization ID for a report, or the date range of the report. To add a parameter to your report, go to the Report Inspector on the left-hand side, right click on Parameters, and add your parameter below. Then, reference it in your query using the format $P{...}. For example, here I have added a parameter called daysOfSales:

Then, you can click on the parameter, and then on the right-hand side in its properties box, set the default value. Note that if the parameter is of the type java.lang.String, then its default value should be in quotes, like "30":

When you preview your report, you will be prompted for the value of this parameter, with the default value already set:

Note that when you load your reports in opentaps, opentaps will automatically create input fields for your parameters. Further, parameters such as organizationPartyId are automatically set by opentaps in hot-deploy/opentaps-common/webapp/common/WEB-INF/actions/report/setupReport.bsh, so you do not need to prompt the user for it again. To turn off the prompting, use the isForPrompting flag for Jasper reports:

<parameter name="organizationPartyId" class="java.lang.String" isForPrompting="false">

References

• JasperReports: Reporting for Java Developers
• The Definitive Guide to iReport