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

Wednesday, April 15, 2015

Creating Oracle JNDI or Actian Pervasive(PSQL) database JNDI connections in Jasper Server 6.X

Software : 
Oracle 11g , Jasper Server 6.0

Aim : To run the stored procedures. Below are the possibilities that may stop you to run the reports developed using SP.
1) Read Only databases when running SP reports in Jasper Server.
2) No Query executor for PLSQL(Not discussed in this post).

UPDATED with new content  :  16th April 2015Creating Actian Pervasive(PSQL) database JNDI connection in Jasper Server. 


Oracle JNDI connection creation : 

Step 1 : jdbc driver 

Copy the ojdb6.jar file to lib folder of tomact
Location of lib : C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\lib

NOTE: jdbc driver differs from Oracle version to version. Choose the suitable one in your case.

Step 2 :Add below lines of jdbc resource code in context.xml file

File location : 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\META-INF

   <Resource name="jdbc/oracledev" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="myusername" password="mypassword" 
        driverClassName="oracle.jdbc.OracleDriver"
        validationQuery="SELECT 1 FROM DUAL"
        testOnBorrow="true"
        url="jdbc:oracle:thin:@171.13.0.34:1523/mySID"

        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

IMP NOTE : 
I've got errors while giving url string  ( after oracle Port there should be forward slash but not colon).
For example :

Correct url string : url="jdbc:oracle:thin:@171.13.0.34:1523/mySID"
Wrong url string : url="jdbc:oracle:thin:@171.13.0.34:1523:mySID"

Explained at : 
http://community.jaspersoft.com/questions/851811/error-while-creating-oracle-jndi-connection-jasper-server#comment-811806

Step 3 :  Refer the resource added in step 2 in web.xml file 

File llocation : 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF

  <resource-ref>
    <description>Excers DEV database</description>
    <res-ref-name>jdbc/oracledev</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

Step 4 : 

Do all the changes from step-1 to step-3 by stopping the server.
Start or Restart the jasper server.

Create a folder and add a Data source of type JNDI and give name . In this example it is jdbc/oracledev and then click on Test button.

That's all we have to do to connect jdbc as JNDI in jasper server.


Actian Pervasive(PSQL) database JNDI creation in Jasper Server 

I'd like to share how I created a JNDI connection for pervasive db in Jasper Server.

1) Add pervasive database jar files to tomcat lib folder. 
Location of lib folder : C:\Jaspersoft\jasperreports-server-5.6.1\apache-tomcat\lib
JDBC diver file names : i) jpscs.jar ii) pvjdbc2.jar and iii) pvjdbc2x.jar 
NOTE : Add all the 3 jar files to the lib folder. (these 3 jar files are the JDBC drivers for pervasive db). 

2) Add resource code in context.xml file 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\META-INF\context.xml

<Resource name="jdbc/pervasiveDB" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="myUserName" password="myPassword
        driverClassName="com.pervasive.jdbc.v2.Driver"
        validationQuery="SELECT 1"
        testOnBorrow="true"
        url="jdbc:pervasive://11.0.1.11:1283/pervasiveDB"
        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

3) Refer the above created resource in web.xml file (Optional)

C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\web.xml
<resource-ref>
    <description>Pervasive pervasiveDB database</description>
    <res-ref-name>jdbc/pervasiveDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

4) Restart the Jasper Server
5) Create a datasource of type JNDI in Jasper Server by providing the JNDI name as jdbc/pervasiveDB and test the connection. 

References Pervasive db: 


Refer these links :
http://community.jaspersoft.com/questions/525672/postgresql-jasper-reporting-server-error

http://community.jaspersoft.com/wiki/how-add-new-jndi-connection-tomcat

http://community.jaspersoft.com/wiki/jndi-datasource-update-trouble-cannot-create-jdbc-driver-class-connect-url-null

https://community.jaspersoft.com/wiki/connecting-jasperreports-server-your-database

http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html

http://community.jaspersoft.com/wiki/data-source-troubleshooting

http://stackoverflow.com/questions/18192521/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript


Cheers..!!!

Update : URL string properties : 
http://commons.apache.org/proper/commons-dbcp/configuration.html

Thursday, April 09, 2015

TIP : Handle divide by zero expression in Jasper Reports

CASE : 
0/0 or
23/0

In general,
something/zero will results infinity and causes to divide by zero error in jasper reports. We can handle to have expression value as 0 when denominator is zero.

Example :

$F{number_of_sales}.intValue()==0?0:$F{unit_sales}.intValue()/$F{number_of_sales}.intValue()

Ternary operator usage in Jasper Reports.

NOTE : If you fields are Decimal types, you need to work with methods.

Will update this page for decimals.


:-)

Tuesday, April 07, 2015

Tip : JFree Bar Chart Customization in Jasper Reports: Moving Bar Itemlabels ( some times values will not fully apper for -ve highest bars)

This post will provide you the code snippets to write in customizer method for JFree Bar Chart in
Jasper Reports

1) Remove space between 2 or  more series within a category.
2) Remove space between 2 or more categories for chart
3)  Remove horizontal and vertical grid lines for bar chart.
4) Fix overlapping of the highest -Ve bar value with category label. 
5) Fixed width for the bars

Problem Image : 


Solution Image :



/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author SADAKAR POCHAMPALLI
 */

package com.sadakar.jfree;
import net.sf.jasperreports.engine.JRAbstractChartCustomizer;
import net.sf.jasperreports.engine.JRChart;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.axis.CategoryAxis;

import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.ui.TextAnchor;

import org.jfree.util.UnitType;

public class JFree extends JRAbstractChartCustomizer{
@Override
public void customize(JFreeChart chart, JRChart jasperChart){
CategoryPlot categoryPlot=chart.getCategoryPlot();
BarRenderer renderer=(BarRenderer)categoryPlot.getRenderer();

//The space between 2 series within a category is controlled by the attribute itemMargin
renderer.setItemMargin(-0.5);
categoryPlot.setRenderer(renderer);

//The space between 2 categories is controlled by the categoryMargin attribute of the CategoryAxis //for the plot
 CategoryAxis domainAxis = categoryPlot.getDomainAxis();
 domainAxis.setCategoryMargin(0.3f);


// -Ve bar Item Labels positions - Below lines of code will not cut down the item label for the biggest //value of bar
//renderer.setSeriesNegativeItemLabelPosition(0, new //ItemLabelPosition(ItemLabelAnchor.OUTSIDE11,TextAnchor.BASELINE_CENTER));

renderer.setItemLabelAnchorOffset(0.1);
renderer.setBaseItemLabelsVisible(true);
renderer.setMaximumBarWidth(0.01);


categoryPlot.setRangeGridlinesVisible(false);
categoryPlot.setDomainGridlinesVisible(false);


}
}

You may be interested in similar posts in this site. 

1) JFree Bar Chart Customization in Jasper iReport - Decrease the size of bars & space between bars

2) JFree Line Chart Cusomization in Jasper iReport. Line to Dashed-line, line width,gap in the dashed lines

3) JFree Meter chart customization in Jasper iReport-- Removing values from the meter

4) JFree Bar Chart Customization in iReport.. Category axis labels overlapping is removed


Saturday, April 04, 2015

Tip : Convert Decimal to Integer and change its pattern using java expression on a text field in Jasper Studio 6.x or iReport 5.x (Or) Combine text with field value for dynamic space in Jasper Reports

This tip will be useful in below scenarios with Jasper Reports

Sometimes you may need to combine Text with a value coming from database field .
Generally you take two fields for this. Text field for printing text and Filed for field value and set pattern for it and adjust the positions of these two fields. But what if your value dynamically changes.

That is some times you may get 2 digit integer and sometimes you may get 5 digit integer. Do you really get dynamic space b/w two fields ( Text field and Database field).

Problem Image : 




Solution : 
Combine text with field using java expression

1) Get the integer value first by rounding.
 (Generally a double have type java.math.BigDecimal in report tool)
(int) ( $F{sales} + 0.5)

2) Get the comma separated integer from above integer using below syntax
NumberFormat.getIntegerInstance().format(<Integer Value>)

3) Combining both steps 1 & 2

Orders : 70 for $19,436  (Now there will not be any space when combining text with field value)

Example : 
"invoices for"+" "+"\$"+ NumberFormat.getIntegerInstance().format((int) ( $F{sales} + 0.5))


NOTE: 
1) In Jasper reports do display $ sign in an expression one should use backslash character combined in double quotes ( i.e., "\$")

I hope this helps someone :-)

Cheers..!!


Wednesday, April 01, 2015

Dates in Jasper iReport or Jasper Studio


1) Current Date (java.util.Date )
   new Date()

2) Yesterday (java.lang.String )
  new SimpleDateFormat("yyyy-MM-dd").format(new Date().getTime() - 1* 24 * 60 * 60 * 1000)

3) Year (java.lang.String or java.lang.Integer)
new SimpleDateFormat("yyyy").format($P{CurrentDate})

4) Month - (java.lang.String or java.lang.Integer)
new SimpleDateFormat("MM").format($P{CurrentDate})

NOTE : All are parameter expressions 


Output:


JRXML

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0  -->
<!-- 2015-04-02T11:41:40 -->
<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="Dates" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a16f4b6d-d5e0-4149-a914-a9c6f9a2e3f7">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <import value="org.apache.commons.lang.time.*"/>
    <parameter name="CurrentDate" class="java.util.Date">
        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <parameter name="YesterDay" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[ new SimpleDateFormat("yyyy-MM-dd").format(new Date().getTime() - 1* 24 * 60 * 60 * 1000)]]></defaultValueExpression>
    </parameter>
    <parameter name="Year" class="java.lang.String" isForPrompting="false">
        <parameterDescription><![CDATA[]]></parameterDescription>
        <defaultValueExpression><![CDATA[new SimpleDateFormat("yyyy").format($P{CurrentDate})]]></defaultValueExpression>
    </parameter>
    <parameter name="Month" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[new SimpleDateFormat("MM").format($P{CurrentDate})]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="130" splitType="Stretch">
            <textField>
                <reportElement x="180" y="0" width="100" height="30" uuid="a2b61b2f-e891-415e-b49e-e6c4d4fe62fe"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$P{CurrentDate}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="180" y="30" width="100" height="30" uuid="2732b9cc-8dcc-4a64-9da6-2ca5273ff95b"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$P{YesterDay}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="180" y="60" width="100" height="30" uuid="a19d13b2-ef38-4e9f-a0c7-9bd78d6b7968"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$P{Year}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="180" y="90" width="100" height="30" uuid="3f2ee16c-a9eb-4530-a274-e806a11a1d91"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$P{Month}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="100" height="30" uuid="6449e356-b6db-4a93-8b4a-f60aa1b66ae0"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA["Current Date"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="30" width="100" height="30" uuid="ca7a0a48-71cd-4551-9214-127e7a60966b"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA["Yesterday"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="60" width="100" height="30" uuid="a85e016c-eec8-41f4-82f5-f9ff189712be"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA["Year"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="90" width="100" height="30" uuid="07aba093-af79-4752-a6fd-07e4a0bb90bf"/>
                <textElement textAlignment="Left" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA["Month"]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>

You may be interested in exploring below

1) Date Parameters in iReport Designer OR Date Input Controls in JasperServer Repository 

2) Dates in Jasper iReport - Expression for default values and couting the number of months b/w given two dates. 

3) Jasper Server Calendar Date input control date fomat change - Change date format from yy-mm-dd to mm/dd/yyyy 

4) Date input control default value in jasper iReport - Giving date value in default expression. 

5)  Easy way of Substracting,Adding Months or Days from parameter date in Jasper studio or iReport : Ex : Substracting 2 months from current date