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, August 30, 2016

Whats new in 6.3 Jasper (Server and Studio) ? from release notes

Hi,
In this post you will see what is new in Jaspersoft 6.3 new release (enterprise and community)

JasperReports Server 6.3.0 includes these improvements:

* Dashboards - New enhancements include:
  - Dashboard and dashlet exporting.
  - Text and image dashlets now support hyperlinks.
  - More formatting options for dashboards and dashlets.
  - New time and date wildcards for dashlets.
  - Auto-complete for parameters and time and date wildcards.
  - Image dashlets have a Browse button for the respository.

* Scheduler - New enhancements include:
  - Support for scheduling Dashboard exports.
  - Support for SFTP with SSH key authentication as a transfer
    protocol.
  - Automatic validation of the path to the repository output
    folder when clicking Save.
  - Support for changing the default output folder by editing a
    configuration file.
 
* Data Connectivity - We continue to improve and refine data 
  access in JasperReports Server. This release adds:
  - New support for Microsoft SQL Azure.
  - New driver for Spark.
  - New Progress driver for Cassandra.
  - New JDBC driver for MongoDB.
  - Updated MongoDB Connector.
  - New Progress driver for Impala.
  - Updated JDBC driver for MariaDB.

* Jaspersoft Studio - New enhancements include:
  - Support for font sets, which let you group font extensions
    in supersets that can include several languages or character
sets and control which languages are used for each font
in the set.
  - Import/export for configuration resources, which lets you
    migrate global configurations, such as global data adapters,
composite elements, and Jaspersoft Studio preferences
between different instances of Jaspersoft Studio.
  - Improved interface for editing properties in HTML5 charts.

* Performance Improvements - JasperReports Server is faster and
  uses less memory when working with high-cardinality data in Ad
  Hoc views or reports based on Ad Hoc views.

* Security Improvements - In our on-going effort to improve the
  security of your data, JasperReports Server implements a new
  Cross-Site Request Forgery (CSRF) protection mechanism that is
  more effective and less intrusive. For more information, see
  the Security Guide.

Read full notes here : Download

Sunday, August 21, 2016

Tip : Conditional Image display in Jasper Reports 6.2 or later

Hi,

In this post you will see how to write conditional expression to compare two fields and based on return value display images.
Lets assume you have number1 and number2 fields of BigDecimal types. Write below expression in image path expression editor itself.



($F{NUMBER1}.intValue()!=0 && $F{NUMBER2}.intValue()!=0) &&
($F{NUMBER1}.intValue()==$F{NUMBER2}.intValue() )

? "C:/Users/sadakar.p/Desktop/image.jpg": "C:/Users/sadakar.p/Desktop/image2.jpg"


True case : 
SELECT 1234.45 as number1, 1234.45 as number2 FROM dual
False case :
SELECT 1234.45 as number1, 567.32 as number2 FROM dual

NOTE : 
1) Do not give empty quotes for the false section(true section) any where in the expression. If you give, you may stop with HTML 500 error message.
2) Make sure the image paths are given with "repo:<serverpath of the image>"  when report is published.
3) This example is developed using 6.2 Pro studio and oracle queries are used for demonstration.

JRXML Code : 
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-08-22T11:43:19 -->
<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_1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="aed847f7-ff38-4b72-9673-2ffa54e62469">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="ppmguru"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<queryString>
<![CDATA[SELECT 1234.45 as number1, 1234.45 as number2 FROM dual]]>
</queryString>
<field name="NUMBER1" class="java.math.BigDecimal"/>
<field name="NUMBER2" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<summary>
<band height="138" splitType="Stretch">
<image>
<reportElement x="80" y="0" width="235" height="138" uuid="fd567e43-fc8b-45fb-bbee-515dd7cb7ff5"/>
<imageExpression><![CDATA[($F{NUMBER1}.intValue()!=0 && $F{NUMBER2}.intValue()!=0) &&
($F{NUMBER1}.intValue()==$F{NUMBER2}.intValue() )

? "C:/Users/sadakar.p/Desktop/image.jpg": "C:/Users/sadakar.p/Desktop/image2.jpg"]]></imageExpression>
</image>
</band>
</summary>

</jasperReport>


Cheers.!
Sadakar Pochampalli 

Thursday, August 18, 2016

Examples of all fusion graphs in TIBCO Jaspersoft Design Studio 6.2 Professional

Hi,

Below are the full stack examples of all fusion graphs as of 6.2 Professional Studio.

Hit the download link in the bottom and explore how to configure categories , series  and values.



note that only basic configurations are done.

Download: Click Me

Sample outputs of the above examples :

1) 3d pie chart

2) Line chart with 2 series 

3) Stacked Column(vertical) bar chart

4) Fusion 3D Bar Chart 

NOTE: Zip file is password protected - write to me for password. ( download )


Cheers.!
Sadakar Pochampalli

Wednesday, August 10, 2016

Examples of all HTML-5 graphs in TIBCO Jaspersoft Design Studio 6.2 Professional

Hi,

Below are the full stack examples of HTML-5 graphs as of 6.2 Professional Studio.

Hit the download link in the bottom and explore how to configure categories , series  and values.


note that only basic configurations are done for examples.

Download: Click Me

Sample outputs of the above examples :

1) Bubble Chart Example 

2) Time Series Area Spline Chart


3) Spider Area Chart


4) Stacked Column with Line



5) Multi Axis Spline Chart




NOTE: Zip file is password protected - write to me for password. ( download )


Cheers.!
Sadakar Pochampalli

Saturday, August 06, 2016

Tip : How to call an image inside HTML component in Jasper Studio Reports 6.2 or later

Hi,

In this post you can learn, how to call an image inside HTML component using HTML script and  how to apply style to it.

With in the Studio and Operating System Environment

Lets assume, you have an image on your desktop say : tibco.png and the path for the image is  C:\Users\sadakar\Desktop\tibco.png

If you want to use the same image to get in JRXML inside HTML component, convert the path of the image as file:///C:/Users/sadakar/Desktop/tibco.png


Drag and drop html component to be of your choice of band, for instance drag HTML component to summary band and double click on it to open its "Expression Editor" and write below HTML code along with styles.

"<p style='background-color:yellow;font-family:verdana;font-size:50px;'>Hi, I am through HTML CODE using HTML Component</p>"+"<br><br>"+
"<img src='file:///C:/Users/sadakar/Desktop/tibco.png' alt='Japser Architecture' height='1000' width='1800'>"


Sample screenshot of the above code : 


Sample output for the above design


Sample JRXML code: (Copy paste the below code and change image path of your choice - version supported for this example is 6.2 pro or later)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-08-06T16:53:41 -->
<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_1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="de083992-95ea-48ad-983f-f63b1b7885ab">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
<queryString>
<![CDATA[]]>
</queryString>
<title>
<band height="79" splitType="Stretch"/>
</title>
<summary>
<band height="381" splitType="Stretch">
<componentElement>
<reportElement x="0" y="0" width="555" height="381" uuid="c557a700-606d-4893-af98-ca8df921bef9"/>
<hc:html xmlns:hc="http://jasperreports.sourceforge.net/htmlcomponent" xsi:schemaLocation="http://jasperreports.sourceforge.net/htmlcomponent http://jasperreports.sourceforge.net/xsd/htmlcomponent.xsd" scaleType="RetainShape" horizontalAlign="Left" verticalAlign="Middle">
<hc:htmlContentExpression><![CDATA["<p style='background-color:yellow;font-family:verdana;font-size:50px;'>Hi, I am through HTML CODE using HTML Component</p>"+"<br><br>"+
"<img src='file:///C:/Users/sadakar/Desktop/tibco.png' alt='Japser Architecture' height='1000' width='1800'>"]]></hc:htmlContentExpression>
</hc:html>
</componentElement>
</band>
</summary>

</jasperReport>



NOTE: This example is specific for absolute path image with in the studio and operating system environment. It can not be deployed to Japser Server unless you have HTML component jar file installed in lib folder of tomcat.

Usually we go for this kind of implementation to represent web clocks showing different time zones on the report.

I hope it helps some one in community.

Cheers.!
Sadakar Pochampalli


Playing with variables for group level summations with Increment Type and Reset Type for multi level group aggregations

Hi, 

In this post you will see how to play with variables for group level summations with increment type for repeating "sales" value at detail level. 

The Use Case would be as follows
           4th group level display nothing
           3rd group level get only distinct values of "sales" from detail.
           2nd group level get the summation of 3rd group level values  
           1st group level get the summation of 2nd group level values and 
           in the detail level, display "customer name" and "sales" data. 
           
Software Details to Run this example
Example developed version : 6.2 Pro Studio, foodmart db of postgres in which a sample data populated in a table called test_jasper_2

For the sample data click on this postgresql backup file and restore it using pgAdmin-3 client tool on foodmart database. 


The sample output will look like as shown in below image.  
(Click on image to get best view of the content)

Solution : (Assuming that you have basic idea of Studio bands, table component and variables creation )
1) Create a data for the above sample data with 4 groups
Group 1 : Region 
Group 2 : Country
Group 3 : State and 
Group 4 : Category

2) Design the JRXML as shown below using TABLE component ( Colours are given to differentiate each group)


3) The core part is to play with variables, its increment type and reset type. 

If you have a look at the sample output in first image, the details for "sales" field are repeating and display nothing at 4th group level(i.e., category group level). 

We need roll up aggregations starting from 3rd group and solution is as follows.

Display Distinct values at 3rd group level (i.e., State level)
Create a variable, say : var_StateLevel and give 
Expression=$F{sales}
Increment Type = None
Reset Type = State

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=State"


Display Summation values of 3rd group(State level) level in 2nd Group(Country level)
Create a variable, say : var_CountryLevel and give 
Expression=$V{var_StateLevel}
Increment Type = State
Reset Type = Country

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=Country"



Display Summation values of 2nd group(Country level) level in 1st Group(Region level)
Create a variable, say : var_RegionLevel and give 
Expression=$V{var_CountryLevel}
Increment Type = Country
Reset Type = Region

Once the variable is created drag and drop it to the place holder as shown in design image and give "Evaluation Time=Region"

4) Save the report and run. It will produce the output as shown in very first image. 

I hope it helps someone in community. (Example of JRXML is attached in the top download link)

Cheers.!
Sadakar Pochampalli 


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 


Monday, July 25, 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

Monday, June 27, 2016

Tip : Table Component Columns hiding based on Date Parameter in Jasper Reports (Dates Comparision in Jasper Reports)

Hi All,

In this post you will see a use case associated with Table Component in Japser Reports.

Use Case : 
Lets assume you have a table component that has 24 columns(months data).
Lets assume you have a parameter called "param_StartDate" =  "15-10-2015".
Lets assume there are 12 columns for "Unit Sales" and 12 columns for "Store Sales" alternatively.

The table component has to conditionally display "Store Sales" till last month and it has to display only "Unit Sales" starting from the current month.

i.e.,

Column1= Oct 2015 Unit Sales
Column2= Oct 2015 Store Sales

Column3 = Nov 2015 Unit Sales
Column4= Nov 2015 Store Sales

Column5= Dec 2015 Unit Sales
Column6= Dec 2015 Store Sales

Column7= Jan 2016 Unit Sales
Column8 = Jan 2016 Store Sales

Column9= Feb 2016 Unit Sales
Column10=Feb 2016 Store Sales

Column11= Feb 2016 Unit Sales
Column12=Feb 2016 Store Sales

-----------------------------------------
Column13= Mar 2015 Unit Sales
Column14= Mar 2015 Store Sales

Column15 = Apr 2015 Unit Sales
Column16= Apr 2015 Store Sales

Column17= May 2015 Unit Sales
Column18= May 2015 Store Sales

Column19= June 2016 Unit Sales ( Current Month)

Column21= Feb 2016 Unit Sales

Column23= Feb 2016 Unit Sales



Column22 and Column24 should be not displayed  as those falls in Current Month (June 2015) and next month.



Parameters Set : 
param_StartDate(java.util.Date)
Parameter1 = $P{param_StartDate} (java.util.Date)
Parameter2 = new Date() (java.util.Date)

Add below package in Report "Import" properties
<import value="org.apache.commons.lang.time.*"/>

NOTE: DateUtils and its methods are written in above package.

In the table component column headers write below expressions.

$P{Parameter1},0).getYear() = Current Month
$P{Parameter1},1).getYear() = Next Month
$P{Parameter1},2).getYear() = Next, next month.

Column1 Print When Expression on 1st Cell :

((DateUtils.addMonths($P{Parameter1},0).getYear()+1900)<($P{Parameter2}.getYear()+1900))
||
(
    (DateUtils.addMonths($P{Parameter1},0).getYear()+1900)==($P{Parameter2}.getYear()+1900)
    &&
    (DateUtils.addMonths($P{Parameter1},0).getMonth()+1)<($P{Parameter2}.getMonth()+1)
)


Column2 Print When Expression on 2nd Cell : 
((DateUtils.addMonths($P{Parameter1},1).getYear()+1900)<($P{Parameter2}.getYear()+1900))
||
(
    (DateUtils.addMonths($P{Parameter1},1).getYear()+1900)==($P{Parameter2}.getYear()+1900)
    &&
    (DateUtils.addMonths($P{Parameter1},1).getMonth()+1)<($P{Parameter2}.getMonth()+1)
)



 Column3 Print When Expression on 3rd Cell : 
((DateUtils.addMonths($P{Parameter1},2).getYear()+1900)<($P{Parameter2}.getYear()+1900))
||
(
    (DateUtils.addMonths($P{Parameter1},2).getYear()+1900)==($P{Parameter2}.getYear()+1900)
    &&
    (DateUtils.addMonths($P{Parameter1},2).getMonth()+1)<($P{Parameter2}.getMonth()+1)
)


Using  these expressions one can hide the columns on table component.


:-)

Tip : Data Set Variable Usage in Conditional Style for Table component in Jasper Reports

Hi,

Today, I wanted to have conditional colors based on aggregated value(s) of a data set on Table component.
Unless you add below property to the report the calculations can not be used for styling.

The property you have to add is :
 Property:
<property name="net.sf.jasperreports.style.evaluation.time.enabled" value="true"/>

Sample Screen shot :

JRXML
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0  -->
<!-- 2016-06-27T18:43:11 -->
<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="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true" uuid="4bdc9118-0a52-4a39-bed1-b1ee9813e230">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <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"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1" value="columnHeader"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <property name="net.sf.jasperreports.export.xls.freeze.row" value="2"/>
    <property name="net.sf.jasperreports.style.evaluation.time.enabled" value="true"/>
   
<style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Style1">
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{totalchildren1}>=200 &&$V{totalchildren1} <=1000]]></conditionExpression>
            <style backcolor="#D43511"/>
        </conditionalStyle>
        <conditionalStyle>
            <conditionExpression><![CDATA[$V{totalchildren1}>1000]]></conditionExpression>
            <style backcolor="#15C21B"/>
        </conditionalStyle>
    </style>

    <subDataset name="Dataset1" uuid="a03b4f51-6c4c-46cd-8545-7f8f7198dc1e">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
        <queryString>
            <![CDATA[SELECT state_province,member_card, SUM (total_children) totalChildren 
            FROM customer
GROUP BY state_province, member_card ORDER BY state_province ]]>
        </queryString>
        <field name="state_province" class="java.lang.String"/>
        <field name="member_card" class="java.lang.String"/>
        <field name="totalchildren" class="java.lang.Long"/>
        <variable name="totalchildren1" class="java.lang.Long" resetType="Group" resetGroup="Group1" calculation="Sum">
            <variableExpression><![CDATA[$F{totalchildren}]]></variableExpression>
        </variable>
        <group name="Group1">
            <groupExpression><![CDATA[$F{state_province}]]></groupExpression>
        </group>
    </subDataset>
    <queryString>
        <![CDATA[SELECT 1 as One ]]>
    </queryString>
    <field name="one" class="java.lang.Integer"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <summary>
        <band height="60" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="0" width="555" height="60" uuid="71b1af30-df05-4763-a3d7-f30a00ca334a">
                    <property name="net.sf.jasperreports.export.headertoolbar.table.name" value=""/>
                </reportElement>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="Dataset1" uuid="254a3472-9e0d-4b10-9082-19e671ae41da">
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:column width="210" uuid="4e78f62c-6fa9-45c7-bf0f-148898adb85e">
                        <jr:groupHeader groupName="Group1">
                            <jr:cell height="30">
                                <property name="com.jaspersoft.studio.unit.height" value="px"/>
                                <textField>
                                    <reportElement x="0" y="0" width="210" height="30" uuid="ff2110e4-2f89-4222-ae28-2ff85db11aec"/>
                                    <box leftPadding="5"/>
                                    <textElement textAlignment="Left" verticalAlignment="Middle">
                                        <font size="14" isBold="true"/>
                                    </textElement>
                                    <textFieldExpression><![CDATA[$F{state_province}]]></textFieldExpression>
                                </textField>
                            </jr:cell>
                        </jr:groupHeader>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement key="" x="0" y="0" width="210" height="30" uuid="b9713338-10ba-42b9-8794-786ea144c363"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="14" isBold="true"/>
                                </textElement>
                                <text><![CDATA[State]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30"/>
                    </jr:column>
                    <jr:column width="160" uuid="ea577695-22fb-4fa8-b7d7-55ae8f079797">
                        <jr:groupHeader groupName="Group1">
                            <jr:cell height="30"/>
                        </jr:groupHeader>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement key="" x="0" y="0" width="160" height="30" uuid="80e3a954-86db-4627-a841-7635786214a1"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="14" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Member Card]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="160" height="30" uuid="67a36de8-d542-4d5c-a352-d2270bb9facb"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <textFieldExpression><![CDATA[$F{member_card}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="185" uuid="115b86b4-ec3d-4d3b-b855-d5ef38e9c0d4">
                        <jr:groupHeader groupName="Group1">
                            <jr:cell height="30">
                                <textField evaluationTime="Group" evaluationGroup="Group1">
                                    <reportElement style="Style1" mode="Opaque" x="0" y="0" width="185" height="30" uuid="eae7a88f-2f46-4266-90d2-1b5f2a0dc451"/>
                                    <box>
                                        <topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                        <leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                        <bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                        <rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
                                    </box>
                                    <textElement textAlignment="Center" verticalAlignment="Middle">
                                        <font size="14" isBold="true"/>
                                    </textElement>
                                    <textFieldExpression><![CDATA[$V{totalchildren1}]]></textFieldExpression>
                                </textField>
                            </jr:cell>
                        </jr:groupHeader>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement key="" x="0" y="0" width="185" height="30" uuid="ffce4b15-e7ae-4b07-94a9-5f713725dd25"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="14" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Total Children]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement mode="Opaque" x="0" y="0" width="185" height="30" uuid="9080cc65-6f3d-4a2d-943f-6e4942c18710"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                                <textFieldExpression><![CDATA[$F{totalchildren}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </summary>
</jasperReport>


Reference : 

 http://stackoverflow.com/questions/12269736/using-variables-in-conditional-style

Monday, June 06, 2016

Tip : Move the entire row to next page when one or more text fields data overflows to next page

Hi,

Usually, Clients needs data to be displayed neatly at server UI level though it would be an excel extract or pdf extract.

This tip is useful when report text fields data overflows to next page with wired look and feel.

Solution : 
1) Remove "Detail Over flow" option for all the text elements that are kept on detail band if  the property is already applied.
2) Select the detail band and look into the properties of it.

    Chose Split Type = Prevent instead Stretch

Split type property specifies the band behavior to split the data.  

This will solve the data over flow problem and there would be no half text displayed in page and remaining text will be displayed in next page rather the entire row will be pushed to the new page.

References : 
http://stackoverflow.com/questions/10744920/avoid-split-of-text-field-to-multiple-pages

Monday, May 09, 2016

D3 Chord diagram visualization example using Custom Visualization Component(CVC) in Jaspersoft Design Studio 6.2 CE/Pro

Hi,

D3 Chord diagram visualization example using CVC component in J.D.S 6.2 or 6.2.1 CE/Pro


Development Credits : Abhi 

Working with passionate guys  always fun and results best solutions :-) 

Sadakar Pochapmalli  

Friday, May 06, 2016

D3 Pie Chart visualization using Custom Visualization Component (CVC) in Jasper Design Studio 6.2 CE/Pro

Hi,

D3 Pie Chart Visualization Example 


Populate SQL result set as JSON format using Java Script :
  
               var data = [];

               var series0 = instanceData.series[0];
              
                for (var index = 0; index < series0.length; ++index) {
                   
                             var record = series0[index];                  
                             data.push({label:record.testprop, value: record.testvalue});
                    }


Development Credits : Sadakar 

Sadakar Pochampalli 

D3 Sankey diagram Visualization example using Custom Visualization Component (CVC) in Jasper Design Studio 6.2 (CE/Pro)

Hi,

D3 Sankey Diagram Visualization example in Jasper Reports.


(Please click on image)


Example Development Credits : Abhi & Sadakar

Coming together is a beginning; keeping together is progress; working together is success. 

Yes, we did it using CVC component.

Special thanks to my senior & fellow buddy for continuous encouragement and guidance.

( Oopps..! Yet, I have to understand the java script)


- Sadakar Pochampalli 

D3 three level collapsible Tree View Visualization example using Custom Visualization Component(CVC) in Jasper Design Studio (6.2/6.2.1 CE or Pro)

Hi,

D3 three level Collapsible Tree View Visualization Example

(Please click on Image to get the original view of the screenshot)

Development Credits : Abhi & Sadakar

- Sadakar Pochampalli 

D3 Tilford Tree 3 level visualization using Custom Visualization Component (CVC) in Jasper Design Studio 6.2 CE/Pro

Hi,

D3 Tilford Tree 3 level visualization using CVC component.


(Please click on image for original screenshot view - that looks better)

Development Credits : Abhi & Sadakar

Coming together is a beginning; keeping together is progress; working together is success. 

Yes, we did it using CVC component.


(Ooops..!!, Yet I have to understand how 3rd level java script added in js file). 


Sadakar Pochampalli 

D3 Individual months Calendar View Visualizaiton using Custom Visualization Component(CVC) in Jasper Design Studio 6.2(CE/Pro)

Hi,

Individual months Calendar View Visualization example using CVC component.

 1990 Data :(Please click on image to get the best view)

1991 Data :(Please click on image to get the best view)
  
Development Credits : Sadakar

Coming together is a beginning; keeping together is progress; working together is success. 

:-) knowledge transfer made me to develop this visualization :-)  





D3 Calendar view visualization example using Custom Visualization Component(CVC) in Jasper Design Studio 6.2 CE/Pro

Hi,

Calendar View Visualization example in Jasper 6.2 CE/Pro Studio


(Please click on image for best view) 

(2008 -2010 data)


 (1990-1993 data)


Control report parameters using some cascading or some logic in default value expressions. 
If not it will generate empty blocks for the years. 




Coming together is a beginning; keeping together is progress; working together is success. 

Yes, we did it using CVC component.

Special thanks to my senior & fellow buddy for continuous encouragement and guidance.


Credits : Sadakar & Abhi. 

- Sadakar Pochampalli

Sunday, May 01, 2016

Simple Grid Report Example using Basic Oracle Stored Procedure in Japser Design Studio 6.1 Pro

Hi,
In this post you will see how to work with a basic oracle stored procedure.

Software Setup : 
1) Oracle SQL developer  Version 4.0.3.16
2) Japsersoft Design Studio 6.1 Professional
3) Jasperserver 6.2 professional 
4) Oracle Version : Express Edition

Aim of the report : Display employee data as a grid 

1) Connecting to XE schema in database client tool ( i.e., Oracle SQL developer)

NOTE :  Default username and password while installing Oracle Expression
USER NAME : SYS
PASSWORD : AS SYSDBA

conn sadakar/sadakar
Syntax : conn schemaName/Password

2) Writing Stored Procedure on "emp" table ("emp" table comes with oracle 11g installation). 


create or replace procedure "REF_CURSOR_SAMPLE"
(emp_cursor  IN OUT SYS_REFCURSOR)
as
begin
  open emp_cursor for
    select * from emp;
end ref_cursor_sample;


Execute Stored Procedure in Oracle SQL developer:
NOTE : Make sure set serveroutput on (i.e., run this command in SQL developer before trying to print the SP result set)


VARIABLE emp_data REFCURSOR;

EXECUTE REF_CURSOR_SAMPLE(:emp_data);

PRINT :emp_data;





3) Create a simple JRXML and in the Query editor change the language type to "plsql" and call the above stored procedure as follows 
{       call REF_CURSOR_SAMPLE($P{ORACLE_REF_CURSOR}       } 

Syntax : { call procedureName(param1,param2,$P{ORACLE_REF_CURSOR})}

 4) Create a parameter called "ORACLE_REF_CURSOR" in parameters Outline section and write its type as java.sql.ResultSet

5) In the query editor manually type the field names and provide types to them as shown in below image

  
NOTE : If you click on "Read fields" button you may see or stop with below error message
net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:

{call REF_CURSOR_SAMPLE($P{ORACLE_REF_CURSOR})}


Caused by: java.sql.SQLException: Invalid column type: 2000 

 6) Design the report and save then preview it. 



7) Japsersoft Design studio preview  of the report. 

Report output in Jasper server:

Configure PL/SQL for Japserserver (5.5 or later)
It can be found at http://community.jaspersoft.com/wiki/config-stored-procedures-jasperreports-server-55

After successful configuration publish the report to the server and check the output.



NOTE : Create oracle "XE" data source connection before publishing the report.  Sample connection is shown in below image. 


Download : 
Sample JRXML : click me.!

References : 
1) http://community.jaspersoft.com/wiki/ireport-calling-stored-procedures
2) http://community.jaspersoft.com/wiki/how-execute-oracle-stored-procedure-jasper-report-ireport-editor-environment
3) http://community.jaspersoft.com/wiki/calling-microsoft-sql-stored-procedures-ireport

Learn more about SP's here :
1) http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/
2) https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm
3) https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm