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

Friday, May 08, 2015

Tip : custom pattern for decimal values for a field in Jasper iReport 5.x or Jasper Design Studio 6.x - Useful when exporting the report to Excel

This post will give you the tip of handling custom pattern for decimal values for a field values in Jasper reports at report level.

Even Though, you can handle it from SQL level some time we may not get handy with SQL for quick development of reports.

Problem Statement : 
If the decimal field value is 45.8900 then get the value as 45.890 (###0.###)
If the decimal field value is 5.0000 or 0.0000 then get it as 5 or 0 (###0)
The output pattern should be same on Jasperserver page output and when export to excel. 


USE CASE EXAMPLE : 

Let us assume your result of SQL query is as below

Sales
0.0000
23.9300
0.0000
5.0000
5.0000
6.9300
8.9400
9.1950

Final output in on Jasperserver web page & when export to Excel should be as follows. 

Sales
0
23.430
0
5
5
6.930
8.940
9.195

Without providing any pattern on filed you will get output as below 
On Jasper Server page output
0.0000
23.9300
0.0000
5.0000
5.0000
6.9300
8.9400
9.1950

When export to Excel ( You will get dots if the value is 0 or 5 and this should be avoided)
.
23.93
.
5.
5.
6.93
8.94
9.195


If you apply default pattern available on filed then the output on Jasperserver page and in Excel export would be as below 
pattern applied on field value is : ###0.000

0.000
23.930
0.000
5.000
5.000
6.930
8.940
9.195

Now, How to overcome the situation to get the output as shown below in both Jasperserver page and in excel export. 

0
23.430
0
5
5
6.930
8.940
9.195

Solution : 
Select the text field and in it's expression editor write expression as below

($F{Sales}-(int)$F{Sales}!=0)?new DecimalFormat("####0.000").format($F{Sales}):new DecimalFormat("####0").format($F{Sales}) 


General Example 
---------------------------------------------------------------------
double d = 77.7;
if((d-(int)d)!=0)
    
double d = 77.7;
if((d-(int)d)!=0)
    System.out.println("decimal value is there");
else
    System.out.println("decimal value is not there");
----------------------------------------------------------------------

That's all we have  to write custom pattern for decimal values. If the value is like 5.0000 then ignore all zeros and get only 5 and if the value is like 54.8970 then get get 54.897

I hope this helps someone :-)

Did you find it helpful ? Hit the g+ share button and let us spread it for community.

References:
http://stackoverflow.com/questions/15963895/how-to-check-if-a-double-value-has-no-decimal-part

Cheers..!!!

- Sadakar Pochampalli