Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Thursday, July 28, 2016

Tip : Custom Pattern Expression example for Percentage type BigDecimal value in Jasper Design Studio 6.2 or later

Hi,

In this post you will see how to write a custom expression for pattern.

The usual way is to select pattern and give its format but there are situations as below

1) After exporting to excel you may see a warning message when you hover mouse on the value(cell)
2) After exporting to excel export you may get a "decimal point" as shown in below image
3) When you double click the cell, the decimal point may go off but its not the expected behavior.

To over come above all issues with usual pattern we can use pattern expression as shown in below image for the decimal text field.


Expression : (Write below expression in "Pattern Expression" section. 
$F{decim}.doubleValue()%1==0?"#,###%":"#,###.##%"

Sample Design screenshot : 


Sample Java Output : 

Sample Excel export output

JRXML : (Copy paste below JRXML and preview in your environment)
NOTE : This example is developed using 6.2 Pro Studio 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-07-29T08:34:18 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Blank_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="45e02059-4eaf-4f1d-af13-8e3ca89ad372">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart 6.2"/>
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<queryString>
<![CDATA[select 1323.34 AS decim
union all
select 1323 AS decim ]]>
</queryString>
<field name="decim" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="30">
<textField>
<reportElement mode="Opaque" x="0" y="0" width="555" height="30" forecolor="#FFFFFF" backcolor="#3E5DB5" uuid="08038b96-c6e1-4480-b299-1cd7f438f3a9">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
</reportElement>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="12" isBold="true"/>
</textElement>
<textFieldExpression><![CDATA["Custom Pattern Percentage Expression Example for BigDecimal value in Jasper Studio 6.2"]]></textFieldExpression>
</textField>
</band>
</title>
<detail>
<band height="29" splitType="Stretch">
<textField pattern="">
<reportElement x="0" y="0" width="250" height="29" uuid="517a6af4-6991-485d-a152-07b7897dfa0d"/>
<textElement verticalAlignment="Middle">
<font size="12"/>
</textElement>
<textFieldExpression><![CDATA[$F{decim}.doubleValue()/100.00]]></textFieldExpression>
<patternExpression><![CDATA[$F{decim}.doubleValue()%1==0?"#,###%":"#,###.##%"]]></patternExpression>
</textField>
</band>
</detail>

</jasperReport>


My special thanks to Abhi for exploring this topic. 

I hope it helps someone in the community. 

Cheers.!
Sadakar Pochampalli