Presenting an Operations Schedule with Talend and Jaspersoft: Part 2

[The second in a two part series on loading operations schedule data using Talend Open Studio and writing a report using Jaspersoft's iReport Designer]

I've worked on and heard of a few cases where the data model used to store hours of operations isn't ideal for presentation.  This post contains instructions for creating a Jasper Report using iReport Designer to produce a condensed schedule based on such a data model.


Source Data

The first part of the series can be found here: Part 1.  It described a data model capturing hours of operations data for businesses.  Consider this data from the first post

Operations Schedule Data

It's easy to visualize a UI filled with checkboxes and comboboxes that let's a user input the data, but to present the data, we're expecting something more like this.

Report with Consolidated Ops Schedule Presentation

Report

To produce a report that transforms the data in this way, I followed two steps: write a query containing a group by and create a Java class for formatting the days of the week.

The Query

The query relies on treating the boolean data (TRUE, FALSE) as numeric which makes it eligible for SQL aggregate functions, in this case "MAX".  The group will include both business keys (hotels, restaurants, stores, etc.) and also the open and close time.  The open and close time will collapse a set of rows into a single row.

This query is a Microsoft Access query.  For other DBs, replace the IIF with a CASE statement.

Query Consolidating an Operations Schedule

 The query produces the right number of rows.  For HOTEL A, the 7 rows each share the same open and close time.  This is reduced to a single row.  For HOTEL B, the 7 rows use two different time bands.  Five -- the weekdays -- share a common time.  Saturday and Sunday for HOTEL B uses a second row.

Special Text Expression

I wrote a special function that will take a set of fields and produce a comma-separated list.  So, $F{Monday}, $F{Tuesday}, ${Wednesday}, etc. will form a String like "Mon,Tues,Wed" if all those are set.  If they aren't -- as is the case for the weekend rows -- they are omitted.  The text field containing the "fmt.joinArray" statement will use a special Java function.

Designer Screenshot

The contents of the text field expression are as follows

Calling joinArray() with an Array of Fields

Custom Java Class

This post uses Java for interpreting expressions, so make sure that Language=Java in the report properties.  Also, you'll need to point iReport Designer to the custom Java class using Options > Classpath.  Compile the following Java class and set the folder in the Classpath list (I use reloadable).

package reportutils;

public class fmt {

    public static String joinArray(String[] a) {
        String retVal = "";
        boolean initialized = false;
        for( String s : a ) {
            if( s != null ) {   
                if( initialized )
                    retVal += ", ";
                else
                    initialized = true;
                retVal += s;
            }
           
        }
        return retVal;
    }
}

One other step that's required is to set the "import reportutils.fmt".  This is also done on the report properties screen.

Often, the intake of data will not be ideal for presentation.  That's because the controls, validations, and business logic involved in inserting or updating data is generally more fragmented than the pure presentation because the presentation -- particularly in business intelligence (BI) -- requires reducing the amount of information.  This series transformed the data using a combination of a Talend Open Studio job, a view, and a query to get the data into a condensed format.  Then, a custom Java class added to a Jasper Report was used to improve the presentation even further.