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 :
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>