Friday 29 July 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 


Monday 25 July 2016

Tip: Get two numbers after decimal point for Big Decimals or Doubles in Jasper Reports By converting to String

Hi,
This tip is useful to extract 2 numbers after decimal points in Jasper Reports

Syntax : 
 String.format("%.2f", (double)value);


 String.format("%.2f",(double)($F{UnitSales}.doubleValue()*$F{Rate}.doubleValue()))

456.325 * 2.376
Sample Result : 1084.2282 

After applying the above format in expression editor of text field in jasper 
The result would be : 1084.22

References :
http://stackoverflow.com/questions/50532/how-do-i-format-a-number-in-java