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

Tuesday, November 10, 2015

Tip : Paranthesis Pattern for Negative BigDecimal Type fields in Jasper Reports (6.x)

Hi,
This tip is useful when exporting the report to excel with negative values.

1) Select the text field that is dragged to the designer.
   (Note that the type of this field is java.math.BigDecimal)

2) Click on it and go to the properties and apply below pattern.
    #,##0.00;(#,##0.00)

3) In above pattern the first piece is applied when the BigDecimal is non-negative and second is applied when the BigDecimal is negative. 

4) This type of pattern is useful when exporting the report to Excel. 
     i.e., Differentiate negative values surrounded by parenthesis.

NOTE : In excel export if you want to get the red color then give the pattern like below.
[Red]#,##0.00;[Red](#,##0.00)

The above pattern will display Red on the Jasper report viewer but when export the report it will give you red colored decimal with the pattern applied. 
  

http://community.jaspersoft.com/questions/525396/pattern-negative-amounts-conditional-style

Thursday, November 05, 2015

TIP : Show percentages on HTML-5(and conditional coloring) and JFree Pie Chart slices in TIBCO Jasper Reports (6.x)

Hi,

This post will give you the tips on displaying percentage values instead of usual category labels on HTML-5 pie chart and JFree chart.
There are many ways to get the percentage but this post is related with an use case.
Category and measure would be same field i.e,  count of same value/total count of field values.



Software Used for the sample in this post 
1) Jasper Design Studio Professional 6.0.1 (6.x)
2) PostgreSQL , a "test" database and a "DUMMY" table with the below shown data.

 

Use Case : 
% calculation on a single field count in below query.(Taking category and measure field as same).

Analysis : (23 is the total count of the field)
number of 1's -1 ---> 1/23
number of 2's- 2 ----> 2/23
number of 0's-10 ----> 10/23
number of 5's -2 ------> 2/23
number of 4's-2  -------> 2/23
number of 8's-2    ------>2/23
number of 10's -4   ------> 4/23

SELECT * FROM DUMMY
field1 field2
a    1
b    2
c    2
m    0
n    0
o    0
p    0
q    0
r    0
s    0
t    0
u    0
v    0
m1  5
n1  5
o1  4
p1 4
q1 8
r1 8
s1 10
t1 10
u1 10
v1 10

How to ? 
On HTML-5 Pie Chart.
On Title band 
1) Drag and drop HTML 5 pie chart to Title band and right click on the chart
      and be on "Chart properties" tab
2) Click on "Show Advanced Properties" tab at bottom right corner.
3) Click on "Add" button and give below property
   Property Name = plotOptions.pie.dataLabels.format
   Check "Use an expression" check box.
  Property Value= " {point.percentage:.0f} % "
4)  Click on Ok button and come back to main tabs of chart properties. i.e, click on "Chart Data"
   Click on Configuration tab 
   Under "Category Level" click on "Level1" and give below for this use case. 
 Expression : $F{field2}
 Value Class Name : java.lang.String 
 Order : Ascending 

 For conditional colors of slices - > Click on Bucket Properties and give below
 Click on Add
 Property Name : color
 Property Expression : 
$F{field2}.toString().equals("0") ? "#DEB887" : $F{field2}.toString().equals("1")? "#5F9EA0" : $F{field2}.toString().equals("2")? "#FF7F50" : $F{field2}.toString().equals("4")? "#006400" :
$F{field2}.toString().equals("5")? "#483D8B" : $F{field2}.toString().equals("8")? "#FF00FF" : "#ADFF2F"


5)  Now come to "Value section" 
Vale Definition Tab : 
Label Expression : "Measure label expression"
Calculation : Count
Value Expression : $F{field2}
Value Class Name : java.lang.Integer
Advanced  Properties 
Click on Add 
Contributor : SeriesItemProperty
Property Name : color
Use Bucket property value : Level1.color 

JFree Chart : 
To implement the JFree Bar chart for this particular data set and use case it is not as simple as we did for HTML-5. 
We need to do below work around.
1) Create a group with $F{field2}
2) Create two variables (one is to find the total count of field2 and another is to find the count of same field when the group changes).
i.e., 
Variable_1
Name = Variable_1
Value Class Name = java.lang.Integer 
Calculation = Count
Expression = $F{field2}
Increment Type = None
Reset Type = Report
Variable_2
Name = Variable_2
Value Class Name = java.lang.Integer 
Calculation = Count
Expression = $F{field2}
Increment Type = None
Reset Type =[Group] Group1  (note that Group1 is the name of the group created)

3) Drag and drop JFree pie chart to "Title band"  and right click on it and click on "Edit chart properties". 
 Give below properties on the windows.
Value = $V{Variable_2}
Key =$F{field2}.toString()
Click on "Finish" button and go the properties window of this chart .
Among 6 properties tabs for this chart,  click on "Chart Plot" tab and go the "Labels" section. 
Give Label Format = ({0} ) {2} 

NOTE: 
# Make sure created group by band height =0 to avoid empty spaces in the report.  
#  This report is not tested in Jaspeer server view but it should work.


That's all. you are done.! 

Save the report and preview. 


JRXML Code : 
 
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.1.final using JasperReports Library version 6.0.0  -->
<!-- 2015-11-06T01:00:22 -->
<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="Graph" pageWidth="1000" pageHeight="842" columnWidth="960" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="77404ba2-af1b-4f3a-be4c-93002d1edd4d">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="test_postgres"/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
    <queryString language="SQL">
        <![CDATA[select * from dummy]]>
    </queryString>
    <field name="filed1" class="java.lang.String"/>
    <field name="field2" class="java.lang.Integer"/>
    <variable name="Variable_1" class="java.lang.Integer" calculation="Count">
        <variableExpression><![CDATA[$F{field2}]]></variableExpression>
    </variable>
    <variable name="Variable_2" class="java.lang.Integer" resetType="Group" resetGroup="Group1" calculation="Count">
        <variableExpression><![CDATA[$F{field2}]]></variableExpression>
    </variable>
    <group name="Group1">
        <groupExpression><![CDATA[$F{field2}]]></groupExpression>
        <groupHeader>
            <band height="18"/>
        </groupHeader>
    </group>
    <title>
        <band height="369">
            <pieChart>
                <chart isShowLegend="false" evaluationTime="Report">
                    <reportElement x="540" y="0" width="420" height="369" uuid="fc0b804b-6c86-4cc8-845b-da2b015145f1"/>
                    <chartTitle/>
                    <chartSubtitle/>
                    <chartLegend/>
                </chart>
                <pieDataset>
                    <keyExpression><![CDATA[$F{field2}.toString()]]></keyExpression>
                    <valueExpression><![CDATA[$V{Variable_2}]]></valueExpression>
                </pieDataset>
                <piePlot labelFormat="({0} ) {2}" legendLabelFormat="{1}">
                    <plot/>
                    <itemLabel/>
                </piePlot>
            </pieChart>
            <componentElement>
                <reportElement x="0" y="0" width="467" height="369" uuid="bb305ca9-82de-4b15-91c1-4e285adf5640"/>
                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="Pie" evaluationTime="Report">
                    <hc:chartSetting name="default">
                        <hc:chartProperty name="chart.zoomType">
                            <hc:propertyExpression><![CDATA["xy"]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.enabled">
                            <hc:propertyExpression><![CDATA[false]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.href">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="credits.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="legend.enabled">
                            <hc:propertyExpression><![CDATA[Boolean.FALSE]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="plotOptions.pie.dataLabels.format">
                            <hc:propertyExpression><![CDATA[" {point.percentage:.0f} % "]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="plotOptions.pie.showInLegend" value="false"/>
                        <hc:chartProperty name="title.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                        <hc:chartProperty name="yAxis.title.text">
                            <hc:propertyExpression><![CDATA[""]]></hc:propertyExpression>
                        </hc:chartProperty>
                    </hc:chartSetting>
                    <multiAxisData>
                        <multiAxisDataset/>
                        <dataAxis axis="Rows">
                            <axisLevel name="Level1">
                                <labelExpression><![CDATA["Level Label expression"]]></labelExpression>
                                <axisLevelBucket class="java.lang.String">
                                    <bucketExpression><![CDATA[$F{field2}]]></bucketExpression>
                                    <bucketProperty name="color"><![CDATA[$F{field2}.toString().equals("0") ? "#DEB887" : $F{field2}.toString().equals("1")? "#5F9EA0" : $F{field2}.toString().equals("2")? "#FF7F50" : $F{field2}.toString().equals("4")? "#006400" :
$F{field2}.toString().equals("5")? "#483D8B" : $F{field2}.toString().equals("8")? "#FF00FF" : "#ADFF2F"





]]></bucketProperty>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <multiAxisMeasure name="Measure1" class="java.lang.Integer" calculation="Count">
                            <labelExpression><![CDATA["Measure label expression"]]></labelExpression>
                            <valueExpression><![CDATA[$F{field2}]]></valueExpression>
                        </multiAxisMeasure>
                    </multiAxisData>
                    <hc:series name="Measure1">
                        <hc:contributor name="SeriesItemProperty">
                            <hc:contributorProperty name="color" valueType="Bucket" value="Level1.color"/>
                        </hc:contributor>
                    </hc:series>
                </hc:chart>
            </componentElement>
        </band>
    </title>
    <summary>
        <band height="33">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
        </band>
    </summary>
</jasperReport>
 




References :  
http://community.jaspersoft.com/wiki/html5-bar-chart-how-add-percent-sign-datalabel-and-change-its-font-size

http://community.jaspersoft.com/wiki/html5-pie-chart-percentage-display

http://community.jaspersoft.com/wiki/displaying-percentage-pie-chart-labels

http://davidhoppmann.com/2013/11/26/customizing-tooltips-in-jasperreports-html5-chart-components/

http://api.highcharts.com/highcharts#plotOptions.pie.dataLabels.format

Colors : http://www.w3schools.com/html/html_colornames.asp










 

Tuesday, November 03, 2015

Tip : Excel export font size is decreased by 1 in Jasper Reports

Hi ,
This post is about the issue while exporting the reports to Excel in Jasper Reports.

Problem Statement : 
Set font size of text field 10 and export it to excel from Jasper server. You will find size for text field as 9 instead of actual size.

Report Level settings :
In Report properties add this property
net.sf.jasperreports.export.xls.font.size.fix.enabled= false 

By default above property is true

Server Level settings : 
 # To set the fontSizeFixEnabled property to false.
 # It is available in xlsExportParameters bean that can be found in WEB-INF/applicationContext.xml file.
 # By default this property is set to true, causing the observed font decrease. 
# After restarting the server, fonts should appear with their actual size.


Reference :
http://community.jaspersoft.com/wiki/xls-exporter-decreases-size-arial-font-1-11-10how-fix

Tip : Adding Big Decimals in Jasper Reports

Hi ,

We can use below expression to add Big Decimals in Jasper Reports. 

new BigDecimal(
    ($F{unit_sales_1}==null ?new BigDecimal(0).doubleValue():$F{unit_sales_1}.doubleValue() )+
    ($F{unit_sales_2}==null ?new BigDecimal(0).doubleValue():$F{unit_sales_2}.doubleValue() )+
    ($F{unit_sales_3}==null ?new BigDecimal(0).doubleValue():$F{unit_sales_3}.doubleValue() )
)


References : 
http://docs.oracle.com/javase/7/docs/api/java/math/BigDecimal.html


Thanks,
Sadakar 

Monday, November 02, 2015

Tip : Tahoma bold font work around when exporting report to PDF from Jasper server 5.x or 6.x

Hello All,

Today, I came across an issue with Tahoma bold font issue while exporting Jasper reports into PDF.
After few hours of R&D, I have got two work around(s). I will be explaining 2 of them and hope this would be helpful if you face the same issue.

Problem Statement : 
PDF Export of bold Tahoma font before work around : 

PDF Export of bold Tahoma font after work around : 

Software used to Test this report : 
1) Jaspersoft Design Studio Professional 6.0.1  (can take 5.x or 6.x).
2) Jasperserver 6.0.1
3) Internet web browsers : IE 11.096 , firefox 41.0.2, Chrome 46.0.2490.80
4) Operating System : Windows 7

Solution 1 : 
1) Check the fonts in windows (Example : C:\Windows\Fonts)
2) Check for the Tahoma font (copy paste to some other location in the computer- to Desktop - you can see two font files copied - one is tahoma.ttf another is tahomabd.ttf )
 NOTE : If your development environment is of Linux flavour get a copy of these ttf files into it. 
3) Upload these files to a folder in Jasper server (Lets say your project folder > and a folder for fonts).

    Example :      
      
   How to ?

4) Note the path of the bold Tahoma font in the server : /Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf



In a notepad file text editor write and keep the path to use in JRXML as below
pdfFontName="repo:/Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf"

 4) Come back to JRXML file open the report in Source Mode and copy paste the above code for the text fields which you would like to get as bold when exporting to export. 
Before doing it, set font as bold, font type as Tahoma and etc using designer options

Example : 
<textField>
                <reportElement x="30" y="0" width="525" height="110" uuid="85182593-c9d2-419f-930d-3ee39eccaeaf"/>
                <textElement>
                    <font fontName="Tahoma" size="40" isBold="true" pdfFontName="repo:/Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf"/>
                </textElement>
                <textFieldExpression><![CDATA["Tahoma Font  size 40"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="30" y="80" width="524" height="50" uuid="0b480149-7f23-4687-bb5d-fa944e58880e"/>
                <textElement>
                    <font fontName="Tahoma" size="30" isBold="true" pdfFontName="repo:/Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf"/>
                </textElement>
                <textFieldExpression><![CDATA["Tahoma Font  size 30"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="30" y="170" width="228" height="30" uuid="2644d5be-3679-44dd-8369-eafed86dffd8"/>
                <textElement>
                    <font fontName="Tahoma" size="20" isBold="true" pdfFontName="repo:/Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf"/>
                </textElement>
                <textFieldExpression><![CDATA["Tahoma Font  size 20"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="30" y="240" width="228" height="30" uuid="16f612ed-ceb1-4a6a-b397-65f64d3f8b09"/>
                <textElement>
                    <font fontName="Tahoma" size="10" isBold="true" pdfFontName="repo:/Tahoma_Font_PDF__Bold_Example/Fonts/tahomabd.ttf"/>
                </textElement>
                <textFieldExpression><![CDATA["Tahoma Font  size 10"]]></textFieldExpression>
            </textField>

 5) Save the report and publish it the server and check the output by exporting into PDF.


 Solution 2: (Hmm...!!! No more screen shots).
1) Go to Preferences in Studio (Menu bar -> Winodw -> Preferences )
2) Under Jasper Studio -> fonts -> Under Fonts -> Click on Add
3) Browse for .ttf files of Tahoma that we saved on our desktop or from windows > fonts of computer location.
4) Check the option " Embbed this font in PDF export".
5) click on Ok then find the "Export" option and click on it. It will generate a jar file with the added .ttf files.
6) Add this jar file to class path of the studio to view the output in the studio ( Your project folder name -> Build path -> Add external jar file).
7) To view the same output in the jasper server, install the generated jar file in lib folder of jasper server  
Example : C:\Jaspersoft\jasperreports-server-6.0.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib
8) Restart the jasper server if it is already running.
9) Publish the report to the server and check the bold font output by exporting the report into PDF.

Another way of using the JAR file 
1) In some environments like where Jasper is integrated - lets say CA PPM Clarity on ondemand systems all the time we may not have the access of installed location of Jasper or Clarity.
In such cases, we have the option of deploying the JAR file directly to the Jasper server from its UI.
#) Right click on the folder > Add jar.
#) Reference the above jar to the report in its Resources.

Otherwise , directly add the JAR file as a resource to the report. ( I have tested it with 5.6 as well 6.x in which it worked for 6.x but not for 5.6).

NOTE : The same procedure we can apply for other font families which jasper directly not supporting as of 6.x version.

I hope this helps some one .!!!
If you like it do share for community developers :-)

Questions/Comments/Suggestions are always welcome :-) :-)

Happy Reporting

Cheers.!
Sadakar Pochampalli