More Jasper iReport Tips and Tricks

I've been working with iReport for about a month now and will share a few more tricks I have found. Today I will cover:

• Alternating row color or style
• Date calculation - such as adding days
• Title case
• Setting the maximum number of rows - useful for html report as they can crash the browser
• Html output - breaking words and cell padding

Alternating Row Style

I came across a few posts which talk about drawing rectangles to do this, however I found the best way is with Conditional Styles. This also work with html reports, unlike some other proposed solutions I tried.

In iReport simply right click on Style > Add > Style. Name the style AltBackgrd. Now right click on the new style and select Add Conditional Style. Under Condition Expression enter:

$V{REPORT_COUNT}.intValue() % 2 == 0

Now check Opaque and set the Backcolor color as needed, I'm using [234,234,234].

The xml definition of the style will look like this:

 <style name="AltBackgrd" fontName="DejaVu Sans" fontSize="10">
  <conditionalStyle>
   <conditionExpression><![CDATA[$V{REPORT_COUNT}.intValue() % 2 == 0]]></conditionExpression>
   <style mode="Opaque" backcolor="#EAEAEA"/>
  </conditionalStyle>
 </style>

Next under the Detail Band, select all the fields and set the Style to AltBackgrd. Your report should look something like this:

Date Calculation

I've also had to do some date calculation. The sql returned a set of dates indicating the start/end date of a financial week, I was then required to calculate the month end date from todays date. There is a nice post out there suggesting this can be done with a single statement which involved initializing a Calendar object, setting the date and then adding to it. In practice I was unable to get this to work. I then went down the simple route of adding hours as follows:

"Month End " + new SimpleDateFormat("MM/dd/yyyy").format(new Date($F{WEEK_END_DATE}.getTime() + ($V{oneDay} * 7 * ($F{ACCT_WEEKS_IN_MONTH}.intValue() - $V{AcctMonth_COUNT}))))

Where does oneDay come from you ask? Well that is just a Variable I defined, with the following Variable Expression:

24L*60*60*1000

Now I can easily do date calculations. This is sufficient for my purposes however do be careful around daylight saving times with this technique.

Title Case

Although this is a fairly obvious solution I though it would be worth mentioning to get fields in Title Case you can simply do this:

org.apache.commons.lang.WordUtils.capitalizeFully($F{MANAGER_NAME})

This of course relies on the fact that you have Apache WordUtils on your classpath ... don't you, of course you do.

Maximum Number of Rows

This was a very important feature. I was constantly getting bug requests because the screen would lock up or Chrome would crash, controls were sluggish and unresponsive, and why? Well because you're trying to view 50k rows in your browser. Now this solution was only needed for html as we did not want to limit the rows for pdf or csv. To do this I used the REPORT_MAX_COUNT parameter.

This is configured inside my controller as follows:

 if (format == null || "HTML".equalsIgnoreCase(format)) {
  mav.addObject("format", "html");
  mav.addObject(JRParameter.REPORT_MAX_COUNT, report.getMaxRowsHtml()); //Row limit only needed for Html
 } else {
  mav.addObject("format", format);
  if ("PDF".equalsIgnoreCase(format)) {
   mav.addObject(JRParameter.IS_IGNORE_PAGINATION, Boolean.FALSE); //Paging is only needed for pdf
  }
 }

The report object you see is simply the model representation of a Report table we use which lists all the reports and maxRowsHtml is one of the columns in the table which contains the per report html row limit. Another thing about our application is that the jrxml files are directly uploaded into the database as a clob. They are parsed, and the maxRowsHtml property is read from the jrxml. It is simply defined in the jrxml as a report property like this:

<property name="maxRowsHtml" value="2000"/>

The other piece of this was indicating to the user that there is a row limit enforced on the report, this would only be displayed on html. You can see it as (Max 2000). This can be achieved with a Text Field in iReport. Give it the following Text Field Expression:

"Rows: " + $V{REPORT_COUNT} + ($P{REPORT_MAX_COUNT} == null ? "" : " (Max " + $P{REPORT_MAX_COUNT} + ")")

Html Output

Finally I'll touch on how I prettied up the html output. When I first ran my reports they looked like this:

The lines were too tall and there was no gap between the right aligned Request Amt and the left aligned Reason. With one line of jquery, I was able to reduce the line height and put some padding between cells, this is called after the html is set on the report container through an ajax call:

$("#reportContainer table tr p").css({ 'margin' : '5px 3px', 'overflow' : 'visible'});

The other problem I had was very long descriptions were not wrapping at all, I fixed that with css:

table td
{
    word-break: break-all;
    word-wrap: break-word;
}

The final report looks something like this:

Jasper iReport with Java, Spring and Tomcat

I'm going to jump over to the Jasper reporting tools which I have been working with on my latest project. I had a hard time finding good documentation. I did find some helpful links which I'll post here, I also did a lot of trial and error and will talk about my experiences and what I've discovered. Topics I'll cover include:

• iReport dynamic sql syntax.
• Enable iReport sql logging to external file.
• Creating and registering (ireport and web app) your own custom $X functions.
• Extending the Spring JasperReportsMultiFormatView to provide dynamic report compilation when the jrxml file is changed on disk, as well as setting the output filename.
• Helpful MultiFormatView properties for controlling the output.

Ok so lets start with the dynamic sql syntax, I had a hard time finding good docs for this even the Ultimate guide barely touches this. I do recommend this guide for the built-in functions. So in this example the sql output is dependent on the value of the parameter passed in.

Start by defining the input parameter:

<parameter name="lastName" class="java.lang.String">
 <parameterDescription><![CDATA[Last Name]]></parameterDescription>
</parameter>

You will also need the dynamic query like this:

<parameter name="lastNameQuery" class="java.lang.String" isForPrompting="false">
 <defaultValueExpression><![CDATA[($P{lastName} == null ? "" : " AND usr.last_name = " +$P{lastName} +" ")]]></defaultValueExpression>
</parameter>

This reads: If the optional input filter (last name) is not provided, don't include it in the sql clause, else insert the sql given here. This uses the shorthand if notation (exp ? do1 : do2) it also passes in the value of the parameter with the $P{lastName} syntax.

The last part seen below is just to place the dynamic query param in the sql (Note the exclamation mark indicates this is part of the sql query to be executed):

WHERE
   ...
    $P!{lastNameQuery}
   ...
ORDER BY $P!{sortColumn} $P!{sortDirection}

Here is an example of using this technique for dynamic sorting:

<parameter name="sortColumn" class="java.lang.String" isForPrompting="false">
 <defaultValueExpression><![CDATA[" usr.id "]]></defaultValueExpression>
</parameter>
<parameter name="sortDirection" class="java.lang.String" isForPrompting="false">
 <defaultValueExpression><![CDATA[" ASC "]]></defaultValueExpression>
</parameter>

Some of these queries can get quite complicated so it is important to be able to view the sql output, unfortunately there is no easy way to do this in iReport. However here are the instruction you should follow to enable this.

In my reports I had a lot of optional filters often around 10, and I got tired of using 2 parameters each time to create the dynamic sql. I then looked into creating my own $X{...} functions. The first one I created was similar to the $X{EQUAL;col;param} built-in function, but if the param was null, then it would not be included in the sql, as opposed to the built-in functionality which would include the 'IS NULL' clause. I called it OPT_EQUAL and it is used like this:

AND $X{OPT_EQUAL; usr.last_name; lastName}

To write this I had to first write a JRSqlOptEqualClause.java like this, which on null includes the truism 0 = 0:

 
public class JRSqlOptEqualClause implements JRClauseFunction {

protected static final int POSITION_CLAUSE_ID = 0;
protected static final int POSITION_DB_COLUMN = 1;
protected static final int POSITION_PARAMETER = 2;

protected static final String CLAUSE_TRUISM = "0 = 0";

protected static final String OPERATOR_EQUAL = "=";

protected static final JRSqlOptEqualClause SINGLETON = new JRSqlOptEqualClause();

/**
 * Returns the singleton function instance.
 *
 * @return the singleton function instance
 */
public static JRSqlOptEqualClause instance() {
 return SINGLETON;
}

public void apply(JRClauseTokens clauseTokens, JRQueryClauseContext queryContext) {
  String clauseId = clauseTokens.getToken(POSITION_CLAUSE_ID);
  String col = clauseTokens.getToken(POSITION_DB_COLUMN);
  String param = clauseTokens.getToken(POSITION_PARAMETER);

  if (clauseId == null) {
   throw new JRRuntimeException("Missing clause name token");
  }

  if (col == null) {
   throw new JRRuntimeException("SQL OPT_EQUAL clause missing DB column token");
  }

  if (param == null) {
   throw new JRRuntimeException("SQL OPT_EQUAL clause missing parameter token");
  }

  Object paramValue = queryContext.getValueParameter(param).getValue();
  if (paramValue == null) {
   handleNullValue(queryContext);
  } else {
   StringBuffer sbuffer = queryContext.queryBuffer();
   sbuffer.append(col);
   sbuffer.append(' ');
   handleEqualOperator(sbuffer, param, queryContext);
  }
 }

 /**
  * Generate a SQL clause that will always evaluate to true (e.g. '0 = 0').
  *
  * @param queryContext the query context
  */
 protected void handleNullValue(JRQueryClauseContext queryContext) {
  queryContext.queryBuffer().append(CLAUSE_TRUISM);
 }
}

The next step was the RegistryFactory for this extension to be picked up:

public class CustomExtensionsRegistryFactory implements ExtensionsRegistryFactory {

 public static final String CLAUSE_ID_OPT_EQUAL = "OPT_EQUAL";
 public static final String CLAUSE_ID_OPT_LIKE_OR = "OPT_LIKE_OR";
 public static final String CLAUSE_ID_OPT_EQUAL_OR = "OPT_EQUAL_OR";

 private static ListExtensionsRegistry registry;

 static {
  StandardSingleQueryClauseFunctionBundle functions = new StandardSingleQueryClauseFunctionBundle(
    JRJdbcQueryExecuter.CANONICAL_LANGUAGE);

  StandardSingleQueryParameterTypesClauseFunctionBundle typesFunctions =
    new StandardSingleQueryParameterTypesClauseFunctionBundle(JRJdbcQueryExecuter.CANONICAL_LANGUAGE);

  functions.addFunction(CLAUSE_ID_OPT_EQUAL,
    new ParameterTypeSelectorClauseFunction(JRSqlOptEqualClause.POSITION_PARAMETER));
  typesFunctions.setFunctions(CLAUSE_ID_OPT_EQUAL,
    new StandardParameterTypesClauseFunction(JRSqlOptEqualClause.instance(), Object.class));
  functions.addFunction(CLAUSE_ID_OPT_LIKE_OR,
    new ParameterTypeSelectorClauseFunction(JRSqlOptLikeOrClause.POSITION_PARAMETER));
  typesFunctions.setFunctions(CLAUSE_ID_OPT_LIKE_OR,
    new StandardParameterTypesClauseFunction(JRSqlOptLikeOrClause.instance(), Object.class));
  functions.addFunction(CLAUSE_ID_OPT_EQUAL_OR,
    new ParameterTypeSelectorClauseFunction(JRSqlOptEqualOrClause.POSITION_PARAMETER));
  typesFunctions.setFunctions(CLAUSE_ID_OPT_EQUAL_OR,
    new StandardParameterTypesClauseFunction(JRSqlOptEqualOrClause.instance(), Object.class));

  registry = new ListExtensionsRegistry();
  registry.add(QueryClauseFunctionBundle.class, functions);
  registry.add(ParameterTypesClauseFunctionBundle.class, typesFunctions);
 }

 @Override
 public ExtensionsRegistry createRegistry(String registryId, JRPropertiesMap properties) {
  return registry;
 }
}

Finally in the scr root folder i needed a file called jasperreports_extension.properties which hooks in the extension and registers the class, it only needs one line:

net.sf.jasperreports.extension.registry.factory.sql.clause.functions.custom=com.myapp.report.jasperext.CustomExtensionsRegistryFactory

If you have trouble I would first recommend you first check that the new classes are registered by debugging the class net.sf.jasperreports.extensionsDefaultExtensionsRegistry method: loadRegistries()

Once that is ok if your extension still isn't called I'd check net.sf.jasperreports.engine.query.JRAbstractQueryExecuter method: resolveFunction(String id) and check you function is found. 

Next to get this working in iReport you must package the classes into a jar which I've included here then register it for use in iReport simply by adding it to the classpath under: tools > options > classpath > add jar

One very cool thing I found is the delimiter used in the report can be either a comma, a semi-colon or a pipe. This is very useful for more complicated $X functions such as this:

AND $X{IN;nvl(decode(type_code, 999, null, type_code), other_type_code);typeCode}

If you used a comma delimiter this would not be parsed correctly, but with the semi-colon it works just fine.

Another problem I had was the JasperReportsMultiFormatView, this did not dynamically compile my report when the jrxml file had changes without bouncing the app server. Since we were regularly uploading new version of report we needed dynamic compilation. I achieved this by creating my own DynamicJasperReportsMultiFormatView.java shown here:

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Properties;

import net.sf.jasperreports.engine.JasperReport;

import org.apache.log4j.Logger;
import org.springframework.web.servlet.view.jasperreports.JasperReportsMultiFormatView;

public class DynamicJasperReportsMultiFormatView extends JasperReportsMultiFormatView {

 private static final Logger LOG = Logger.getLogger(DynamicJasperReportsMultiFormatView.class);

 private static final String DATE_FORMAT = "MM-dd-yyyy";

 private static final String FILE_EXT_CSV = "csv";
 private static final String FILE_EXT_EXCEL = "xls";
 private static final String FILE_EXT_HTML = "html";
 private static final String FILE_EXT_PDF = "pdf";

 /**
  * The JasperReport that is used to render the view.
  */
 private JasperReport jasperReport;

 /**
  * The last modified time of the jrxml resource file, used to force compilation.
  */
 private long jrxmlTimestamp;

 @Override
 protected void onInit() {
  jasperReport = super.getReport();
  this.setContentDispositionHeader(jasperReport.getName());
  try {
   String url = getUrl();
   if (url != null) {
    jrxmlTimestamp = getApplicationContext().getResource(url).getFile().lastModified();
   }
  } catch (Exception e) {
   e = null;
  }
 }

 @Override
 protected JasperReport getReport() {
  if (this.isDirty()) {
   LOG.info("Forcing recompilation of jasper report as the jrxml has changed");
   this.jasperReport = this.loadReport();
  }
  return this.jasperReport;
 }

 /**
  * Determines if the jrxml file is dirty by checking its timestamp.
  *
  * @return true to force recompilation because the report xml has changed, false otherwise
  */
 private boolean isDirty() {
  long curTimestamp = 0L;
  try {
   String url = getUrl();
   if (url != null) {
    curTimestamp = getApplicationContext().getResource(url).getFile().lastModified();
    if (curTimestamp > jrxmlTimestamp) {
     jrxmlTimestamp = curTimestamp;
     return true;
    }
   }
  } catch (Exception e) {
   e = null;
  }
  return false;
 }

 /**
  * This will configure the content disposition mapping with the report name.
  */
 protected void setContentDispositionHeader(String reportName) {
  Properties mappings = new Properties();
  DateFormat df = new SimpleDateFormat(DATE_FORMAT);
  String reportDate = df.format(Calendar.getInstance().getTime());
  String contentDisp = "inline; filename=" + reportName + "_" + reportDate + ".";
  mappings.put(FILE_EXT_CSV, contentDisp + FILE_EXT_CSV);
  mappings.put(FILE_EXT_HTML, contentDisp + FILE_EXT_HTML);
  mappings.put(FILE_EXT_PDF, contentDisp + FILE_EXT_PDF);
  mappings.put(FILE_EXT_EXCEL, contentDisp + FILE_EXT_EXCEL);
  setContentDispositionMappings(mappings);
 }
}

As you can see the class checks when the report timestamp and recompiles if needed. It also configures the file name, with the name of the report and a date. This is configured in the app context as usual:

  <bean id="viewResolver" class="org.springframework.web.servlet.view.jasperreports.JasperReportsViewResolver">
  <property name="prefix">
   <value>WEB-INF/reports/</value>
  </property>
  <property name="viewClass">
   <value>com.myapp.report.DynamicJasperReportsMultiFormatView</value>
  </property>
  <property name="suffix">
   <value>.jrxml</value>
  </property>
  <property name="jdbcDataSource">
   <ref bean="testDataSource" />
  </property>
  <property name="exporterParameters">
   <map>
    <entry key="net.sf.jasperreports.engine.export.JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN" value="false"/>
    <entry key="net.sf.jasperreports.engine.export.JRHtmlExporterParameter.SIZE_UNIT" value="pt"/>
    <entry key="net.sf.jasperreports.engine.export.JRHtmlExporterParameter.IGNORE_PAGE_MARGINS" value="true"/>
    
    <entry key="net.sf.jasperreports.engine.export.JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS" value="true"/>
    <entry key="net.sf.jasperreports.engine.export.JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS" value="true"/>
    <entry key="net.sf.jasperreports.engine.export.JRXlsExporterParameter.IS_DETECT_CELL_TYPE" value="true"/>
    <entry key="net.sf.jasperreports.engine.export.JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN" value="true"/>
   </map>
  </property> 
  <property name="headers">
   <map>
    <entry key="Content-Disposition" value="inline; filename='report'"/>
   </map>
  </property> 
 </bean>

I'll also talk about some of the export parameters used here:

• SIZE_UNIT - this is helpful to provide consistent text size across Excel and html output, I found the html text was very small (default pixels) until I changed this.
• IS_COLLAPSE_ROW_SPAN - this will remove cells that span multiple rows in the Excel output making sorting difficult.
• IS_DETECT_CELL_TYPE - this will let Excel automatically set the type of units like currency or date and seems to work quite well.

I've covered a lot on Jasper Reports today, please ask comments if I went too quick and you need clarification on anything or more detailed examples.

Update March 5, 2013
I have updated the custom function jar with some new tools, it now contains the following procedures, documentation on usage and examples is contained within the jar.

• X{OPT_EQUALS, column, param}
• X{OPT_LIKE_OR, column1, column2, param}
• X{OPT_EQUAL_OR, column1, column2, param}
• X{IS_EQUAL, sql, compareValue, param}
• X{OPT_NAME_CODE, sql1, sql2, param}