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}