Zahlensalat: JasperReports, iReports und andere Wege, sich den Tag zu versauen (Start iReport FAQ) +

Gleich zu Beginn: JasperReports ist super. In Zeiten von Ajax, Flex und Co nicht mehr so ganz zeitgemäß, aber eben doch schnell eingesetzt. Nur habe ich heute den ganzen Tag (und es kam mir noch wesentlich länger vor) Reports erstellt. Schöne Tabellen, schicke Charts. Feine Sache. Aber es ist wirklich langweilig.

Und zwar auch so offensichtlich langweilig, dass sich kein anderer dauerhaft erbarmen wollte, mich zu erlösen und den Part übernommen hätte (team spirit, pah! :)). Und am Ende habe ich auch noch den Fehler gemacht, ein Buch zu iReport – dem Jasper Designtool – zu kaufen (fünf Minuten durchblättern, dann festgestellt, dass der Autor das Ding auf meinem Wissensstand geschrieben hatte…super). Mir ging’s hierbei ähnlich wie bei meinen kurzen Ausflügen zu Pentaho (“full-blown” Business Intelligence Suite): Reporting/BI-Tools haben einen anderen Nutzerkreis.

Sie wollen so einfach zu nutzen sein, dass man keine Programmierkenntnisse haben muss, können’s aber nicht so simpel machen, dass man es mit ausgeschabtem Schädelinneren schafft. In Summe musste ich halb mit iReports die Grundlagen erstellen, um später die andere Hälfte von Hand als XML für Jasper tunen zu können. Das sind nebenbei allerdings Frustrationen, die ich auch locker auf alle SOA-Oberflächen-Tools anwenden kann (ja, ich schaue in Eure Richtung Sopera, Progress & Co). Marketingtechnisch sehr überzeugend, aber in der Praxis – ich halte mich hier zurück – nicht zu 100% tauglich (ja, schon richtig, mehr als “besser als nichts”, aber kein Ersatz für Grundlagenwissen).

Simon war allerdings so lieb, ein kleines Tutorial für iReport zu erstellen (in English)…das hält die Frustrationen in Grenzen…ok, Tutorial ist übertrieben…FAQ passt besser…

iReport

iReport is a report designer for JasperReports – it let’s you edit reports visually with a nearly WYSIWYG UI. It let’s you work on JasperReports “Source”-Files.

Actually in JasperReports there are two types of files

• .jrxml
• .jasper

jrxml-Files are xml-Files which describe how the report looks like and how data should be accessed (e.g. via an SQL query). jasper-Files are compiled and can’t be changed any more. Like a Java source file (*.java) and a compiled class (*.class). So in an essence: you edit *.jrxml-Files in iReport.

More about the file types in iReport:
http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Files_viewlet_swf.htm

Define the data source

You need a data source to execute your report within iReport. A data source could be a JDBC connection, a XML file or something else. But let’s just stick to JDBC for now.
To define a JDBC data source you need the following steps:

• Start iReport
• Go to Data -> Connections/Data Sources
• New -> Database JDBC Connection
• Insert connection data (example screenshot for a local Postgresql database. Don’t forget to insert the database driver as described in the FAQ)

To get more information about how to configure the datasource, watch the screencast http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Datasource_viewlet_swf.htm

Bands

If you create a new report you’ll notice that the sheet is divided into different “bands”: title, page header, column header, detail, column footer, page footer, last page footer, summary.
Each has a distinct function:
Whatever you put into the title will be displayed once at the start of the document, what’s in the page header once every page, column header is used as a static element for the following detail band. The detail band is used for the “real” report data – variable fields should be placed here – it’s for any kind of tabular data. In the same way column footer, page footer, last page footer bands are used. Uhm, actually quite self-explanatory (once understood and fully digested…after a few weeks).

But then it’s quite easy to start formatting fields and designing a complete report.

Formatting Fields

http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Formatting_viewlet_swf.htm

Groups

http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Groups_viewlet_swf.htm

Parameters

http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Parameters_viewlet_swf.htm

Report Wizard

http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Report_Wizard_viewlet_swf.htm

Subreports

http://www.jasperforge.org/images/stories/Documentation/ireports/swf/Subreport_viewlet_swf.htm

Create charts

To create charts you need to click on the chart icon in the middle of the first button line. The cursor on the workspace is changing to a cross and you can span a rectangle in the size of the chart.
If you release the left button, a dialog appears, where you can select the type of chart.

Pie (3d) chart

If you select the Pie chart, you can configure it in the following way:

• Key Expression
  • Insert the expression for the key, which belongs to the value
  • Example: if it is a SQL query it could be a delivery machine

    $F{delivery_machine_identification}

• Value Expression
  • Insert the expression which results in the share of the pie
  • Example: if it a SQL query it could be something like

    $F{share}

• Label Expression
  • Insert the expression which should be assigned to the labels of the pie chart
  • Could be empty, than the key is displayed or could be any other expression. Remember that Strings need to be Java Strings

Bar chart

• Series Expression
  • The data of the x-Axis
• Category Expression
  • The label of the x-Axis
• Value Expression
  • The data of the y-Axis
• Label Expression
  • Used nowhere (set data but couldn’t find it in the chart)

FAQ

There is no chart generated

Charts are not working in every band of the report. Detail or Summary bands are ok.

Exception when accessing data source

If you install iReport from scratch your database driver is missing. For Postgres download it from http://jdbc.postgresql.org/download.html. If you need to access e.g. an oracle database or something else, try to find it in the internet. Normally you find matching drivers here: http://developers.sun.com/product/jdbc/drivers

You just need to place the driver in the directory iReport-2.0.3/lib/ and restart iReport.

Handling multi value expression (select x from y where z in ..)

First: Define multi-value variable as Collection:

Then: add a \$X{} expression with values IN or NOTIN, column name, parameter name:

select identification,

box_size,

sum(a_configured) as tech_capacity,

-sum(a_defect) as defect,

-sum(a_soiled) as soiled,

sum(a_configured - a_defect - a_soiled) as available,

-sum(a_occupied) as occupied,

-sum(a_reserved) as reserved,

sum(a_configured - a_defect - a_soiled - a_occupied - a_reserved) as free,

-sum(a_buffer) as buffer,

sum(a_configured - a_defect - a_soiled - a_occupied - a_reserved -a_buffer) as freeToReserve

from 	some_table

where   validTo > ($P{validFrom})    -- kleinere Wert

and     validFrom <=  ($P{validTo}) -- größere Wert

and     $X{IN, machine_identification,machineList}

This gets expanded to:

select identification,

box_size,

sum(a_configured) as tech_capacity,

-sum(a_defect) as defect,

-sum(a_soiled) as soiled,

sum(a_configured - a_defect - a_soiled) as available,

-sum(a_occupied) as occupied,

-sum(a_reserved) as reserved,

sum(a_configured - a_defect - a_soiled - a_occupied - a_reserved) as free,

-sum(a_buffer) as buffer,

sum(a_configured - a_defect - a_soiled - a_occupied - a_reserved -a_buffer) as freeToReserve

from 	some_table

where   validTo > ($P{validFrom})    -- kleinere Wert

and     validFrom <=  ($P{validTo}) -- größere Wert

and     machine_identification in (values of machineList)

Bei Interesse erweitern wir’s gerne…ich kann aber auch ein paar Tage ohne iReport und Jasper auskommen :)