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

Thursday, January 14, 2016

StackedColumnLine or StackedColumn..SpLine highcharts example in Jasper Design Studio 6.x or later

Hi Folks,

This tutorial will talk about how to work with StackedCoulumnLine or StackedColumn..SpLine

Software used for this example
1) Jasper Design Studio 6.2 Professional
2) Jasper Server 6.2 Professional
3) PostgreSQL foodmart database

Chart engine implementations vary from tool to tool to visualize data on graphs hence we have to build our result set from querying technologies to adapt it by the graph.


Core parts:

Query Format : 
In jasper your query format for dual axis (Stacked bar + Line) should take below query format. 
i.e., the chart will take 3 measures . In this example I have taken 2 measures and caluculated 3rd mesure as profit by using first 2 measures.

 Graph to pull on Designer
  Drag and drop the StackedColumnLine or StackedCo..Spline chart and give chart data properties as shown in below two images. 
 

Chart Properties to Set :

Sample output in the server : 
Publish it the server and view the output. Sample output will look as shown in below image. 
 JRXML : 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-01-14T14:36:04 -->
<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_3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3a9c4e03-faeb-4a1e-b0b6-4eb2dcdd5af0">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="ireport.jasperserver.url" value="http://localhost:8081/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser"/>
    <property name="ireport.jasperserver.reportUnit" value="/Explore/Reports/Stacked_Bar_with_Line_Sample"/>
    <property name="ireport.jasperserver.report.resource" value="/Explore/Reports/Stacked_Bar_with_Line_Sample_files/main_jrxml"/>
    <queryString>
        <![CDATA[SELECT DISTINCT c.occupation,t.the_month,t.month_of_year,sum(sf7.store_sales) store_sales,sum(sf7.unit_sales) unit_sales FROM customer c
INNER JOIN sales_fact_1997 sf7 ON c.customer_id=sf7.customer_id
INNER JOIN time_by_day t ON t.time_id=sf7.time_id
WHERE t.the_year=2012
GROUP BY c.occupation,t.the_month,t.month_of_year
ORDER BY t.month_of_year]]>
    </queryString>
    <field name="occupation" class="java.lang.String"/>
    <field name="the_month" class="java.lang.String"/>
    <field name="month_of_year" class="java.lang.Integer"/>
    <field name="store_sales" class="java.math.BigDecimal"/>
    <field name="unit_sales" class="java.math.BigDecimal"/>
    <title>
        <band height="50"/>
    </title>
    <summary>
        <band height="314" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="0" width="555" height="314" uuid="6dd6cf77-5197-46b5-9691-8fd27d40b95d"/>
                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="StackedColumnLine">
                    <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="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="Month">
                                <labelExpression><![CDATA["Level Label expression"]]></labelExpression>
                                <axisLevelBucket order="None" class="java.lang.String">
                                    <bucketExpression><![CDATA[$F{the_month}]]></bucketExpression>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <dataAxis axis="Columns"/>
                        <multiAxisMeasure name="Store Sales" class="java.lang.Integer" calculation="Nothing">
                            <labelExpression><![CDATA["Store Sales"]]></labelExpression>
                            <valueExpression><![CDATA[$F{store_sales}]]></valueExpression>
                        </multiAxisMeasure>
                        <multiAxisMeasure name="Unit Sales" class="java.lang.Integer" calculation="Nothing">
                            <labelExpression><![CDATA["Unit Sales"]]></labelExpression>
                            <valueExpression><![CDATA[$F{unit_sales}]]></valueExpression>
                        </multiAxisMeasure>
                        <multiAxisMeasure name="Profit" class="java.lang.Integer" calculation="Nothing">
                            <labelExpression><![CDATA["Profit"]]></labelExpression>
                            <valueExpression><![CDATA[$F{store_sales}.intValue() -$F{unit_sales}.intValue()]]></valueExpression>
                        </multiAxisMeasure>
                    </multiAxisData>
                    <hc:series name="Store Sales"/>
                    <hc:series name="Unit Sales"/>
                    <hc:series name="Profit"/>
                </hc:chart>
            </componentElement>
        </band>
    </summary>
</jasperReport>