16 December 2009

Jasper Reports - Tricks and Tips

In the previous article, we modifed the rights to a set of Jasper Server reports.

To finish this set of articles, here are some tricks and tips.

Tomcat Server

It is possible to display the SQL used, along with its parameters by modifying the logging parameters in Tomcat. You need to modify the file:

C:\Program Files\jasperserver-3.5.0\apache-tomcat\webapps\jasperserver\WEB-INF\log4j.properties

# Global logging configuration
log4j.rootLogger=WARN, stdout, fileout
Change the WARN to DEBUG.

Calculated parameters

You can use calculated parameters. If you uncheck the ‘Use as a prompt’ checkbox, then you can enter an expression in the property Default Value Expression, such as:
"%" + $P{nom} + "%"
This is a JAVA expression.

To do the same thing in the XML view:
<parameter name="name" class="java.lang.String"/>
<parameter name="name_percent" class="java.lang.String" isForPrompting="false">
<defaultValueExpression>
<![CDATA["%" + $P{name} + "%"]]>
</defaultValueExpression>
</parameter>

For example, if you want to use a user-entered string as a LIKE in SQL, you could add ‘%’ at the end of the string:

<parameter name="name" class="java.lang.String"/>
<parameter name="name_percent" class="java.lang.String" isForPrompting="false">
<defaultValueExpression>
<![CDATA[
($P{name} == null) ? "%" : ("" + $P{name} + "%")
]]>
</defaultValueExpression>
</parameter>
<queryString language="SQL">
<![CDATA[
select * from INFORMATION_SCHEMA.TABLES WHERE table_name LIKE $P{name_percent}
]]>
</queryString>

Optional parameters

You can mark a parameter as obligatory or optional. If a parameter is optional, it is possible that the value be NULL in the report (rather than an empty string), so you need to protect all references to the value:

($P{name} == null) ? "%" : ("" + $P{name} + "%")


Also, if you try to dereference a parameter which has a value of null, you will get a NullPointerException, which is internally handled by JasperReports. This will result in a "null" string being displayed in place of the entire expression, or if the expression is part of the SQL select statement, an empty report.

So, for the expression : "front" + $P{name}.toString() + "back", if the value of $P{name} is NULL, then the text that will be displayed will be "null" NOT "frontnullback"

This is the last article in the Jasper Reports series. Hope this has been of use.
Post a Comment