Tutorial iReports

From Opentaps Wiki

Jump to: navigation, search

Contents

[hide]

• 1 Overview
• 2 Creating A New Report
  • 2.1 Report Bands
• 3 Inserting Text
• 4 Creating A Query
  • 4.1 MDX queries and Mondrian OLAP datasource
• 5 Inserting Fields
  • 5.1 Operations on Fields
  • 5.2 Formatting a Field
• 6 Previewing The Report
• 7 Formatting The Report
  • 7.1 Adding Column Headers
  • 7.2 Adding Page Numbers
  • 7.3 Changing background for alternating lines of report detail
• 8 Final Report Preview
• 9 Adding Parameters
• 10 References

Overview

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

Installation of iReport and configuration are not covered.

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

Creating A New Report

There is a wizard to help in creating a report, but it hidesimportant details about the anatomy of a report. Therefore, we will becovering report creation from scratch. First, create a new report usingFile -> 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 faintlylabeled 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 footerare repeated every page. This is a good place for things that don'tchange much on a page by page basis, such as the title and pagenumbers. Usage of title, lastPageFooter and summary are for the titlepage and lst pages. Their use will not be covered here. For now we canhide the bands we are not going to work with by dragging on the bordersand reducing the band height to 0. Alternatively, you can right clickon 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 thepageHeader, columnHeader, detail and pageFooter bands. Collapse theother bands to size 0 until the page looks as follows.

Inserting Text

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

Once you click on the desired location for the element, are-sizable box appears. Move the box to the pageHeader band and resizeit to take up the entire band. You'll notice that iReport providessnapping and hints when you're aligned with the edge. We will use theseextensively when aligning data so that the output is tidy and suitablefor 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 Structurewindow to bring up the same properties dialog. It has several tabs andallows you to specify all kinds of details from the font to thealignment details. For now let's just change the text as follows.

After closing this window, our header is now displayed. We canuse 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 opentapsdatabase directly, which helps us ensure the report is correct. You cancreate a new data source from Data -> Connections/Data Sources.This is a standard JDBC data source setup that you might be familiarwith from other tools. The key points are to select the correct JDBCdriver for your database, make sure it is in the iReports classpath (orcopy 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 fieldswere detected as String and Double types. If it doesn't understand thequery, 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 MondrianOLAP server first. This requires a configured JDBC connection pointingto the data and the URL of an XML file containing the definition of thescheme.

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 begindefining the field mapping, proceed by creating the field to map theUnit 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 toidentify 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 tothe current cell. Please note that iReport is not able to identify thefield 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 dimensionto some other report fields. Start by adding a field to store theproduct name. When you deal with a dimension that does not containsmeasures, you have to define a field yourself. You start by setting thefield name: ProductName. If you want to simply get the member name as afield value, double-click the desired member (the itemproduct_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, adda group for each level in the hierarchy (excluding ProductName, as it’sthe maximum detail that you can reach).

Finally, you want use MDX to calculate some subtotals. Ineffect, all this data is contained the OLAP cube, and it does not makesense 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 beembedded 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 aband. In this case, we should drag them into the details band. Thedetails band is the location for data output and will flow into thenext 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 supportsthe 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 reportas described above, it should show your report, otherwise you get amessage about no pages created. An example of the output is provided asPDF format, which was itself generated using the iReport Executeaction,

• Image:TutorialReport1.pdf

If you look at this report, you'll notice that the pageHeader isrepeated every page and that the data is listed, but with an extremeamount 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 detailsband. If the report is to be tabular and published to a spreadsheet orplain text format, it is recommended to align them in a way that iseven. The following screenshot shows a better alignment for thetutorial report with better margin control.

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

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

Notice also how there is almost no space left over in thedetails band. This band represents a row and will get repeated over andover 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 wasadded as the first element in this band. You can position elementsrelative 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, youcan do it by putting a colored rectangle inside of the detail lineboundaries and assign Print when expression:

1. Insert rectangle in detail band with height equal to bandheight and required width. Usually it is equal to the page widthwithout 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.

• Image: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 asthe product, customer, or organization ID for a report, or the daterange of the report. To add a parameter to your report, go to theReport 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-handside in its properties box, set the default value. Note that if theparameter is of the type java.lang.String, then its default valueshould 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 willautomatically 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