Thursday 30 March 2017

JFree Statcked Bar Chart Example in Jasper Design Studio

This is a quick re-visit of JFree Stacked Bar chart in Jasper.

Query Format : 
select * from 
(
(
SELECT 
'Store Sales' as Sales, 
date(t.the_date) as date,
SUM(sf7.Store_sales) as SalesFact
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 25
)

UNION
(
SELECT 
'Unit Sales' as Sales,
date(t.the_date) as date,
SUM(sf7.unit_sales) as SalesFact  
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 25
)
) a ORDER BY a.date

Query Output:
Sales          date          salesfact

"Unit Sales";"2012-01-01";348.0000
"Store Sales";"2012-01-01";706.3400
"Unit Sales";"2012-01-02";635.0000
"Store Sales";"2012-01-02";1304.5300
"Store Sales";"2012-01-03";1294.1200
"Unit Sales";"2012-01-03";589.0000
"Unit Sales";"2012-01-04";20.0000
"Store Sales";"2012-01-04";42.8700
"Store Sales";"2012-01-05";1987.1900
"Unit Sales";"2012-01-05";966.0000


Chart Design : 

Final Output:


JRXML Code : 

Jasper Design Studio Version : Professional 6.3 (If you are using CE studio code may not work)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2017-03-30T02:48:52 -->
<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="BarChartCustomization_XAxisLabels" pageWidth="800" pageHeight="842" columnWidth="760" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="9e71469d-a266-48ee-9989-9c4913b1190f">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<queryString>
<![CDATA[select * from 
(
(
SELECT 
'Store Sales' as Sales, 
date(t.the_date) as date,
SUM(sf7.Store_sales) as SalesFact
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 5
)

UNION
(
SELECT 
'Unit Sales' as Sales,
date(t.the_date) as date,
SUM(sf7.unit_sales) as SalesFact  
FROM sales_fact_1997 sf7  JOIN time_by_day t 
ON sf7.time_id=t.time_id GROUP BY Sales,the_date ORDER BY date
limit 5
)
) a ORDER BY a.date]]>
</queryString>
<field name="sales" class="java.lang.String"/>
<field name="date" class="java.sql.Date"/>
<field name="salesfact" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<summary>
<band height="360" splitType="Stretch">
<stackedBarChart>
<chart evaluationTime="Report">
<reportElement x="0" y="0" width="760" height="360" uuid="f13a44b1-bb8f-49b9-8bd8-4b048ef7e26d"/>
<chartTitle/>
<chartSubtitle/>
<chartLegend/>
</chart>
<categoryDataset>
<categorySeries>
<seriesExpression><![CDATA[$F{sales}]]></seriesExpression>
<categoryExpression><![CDATA[$F{date}.toString()]]></categoryExpression>
<valueExpression><![CDATA[$F{salesfact}]]></valueExpression>
</categorySeries>
</categoryDataset>
<barPlot>
<plot labelRotation="60.0">
<seriesColor seriesOrder="0" color="#BF6B83"/>
<seriesColor seriesOrder="1" color="#129D10"/>
</plot>
<itemLabel/>
<categoryAxisFormat labelRotation="60.0">
<axisFormat/>
</categoryAxisFormat>
<valueAxisFormat>
<axisFormat/>
</valueAxisFormat>
</barPlot>
</stackedBarChart>
</band>
</summary>
</jasperReport>

1 comment:

  1. Thanks for sharing, nice post! Post really provice useful information!

    An Thái Sơn với website anthaison.vn chuyên sản phẩm máy đưa võng hay máy đưa võng tự động tốt cho bé là địa chỉ bán máy đưa võng giá rẻ tại TP.HCM và giúp bạn tìm máy đưa võng loại nào tốt hiện nay.

    ReplyDelete