Jasper HTML5 multiline chart with group by result set

Question

I have sql query that do double group by(column 1 and column 2), result set is in the following format

Im trying to create multi line HTML5 chart in jasper studio/ireport in the following format:

xAxis(Category) will have column2 values (distinct). In this example three values on xAxis
yAxis (Measures) will have column3 values and for each value in column 1 it should have one line with data from column 3. So four lines for 4 different values in column 1.

Is this possible to achieve with this result set? I was trying to create Groups, add conditions for measures but no result.

dada67 dada67 · Accepted Answer · 2016-03-10T08:18:21

All you need to do is to use column2 as rows/category level, and column1 as columns/series level.

Like this:

<?xml version="1.0" encoding="UTF-8"?>
<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="Html5Chart" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <field name="column1" class="java.lang.String"/>
    <field name="column2" class="java.lang.String"/>
    <field name="column3" class="java.lang.Double"/>
    <summary>
        <band height="500">
            <componentElement>
                <reportElement x="0" y="0" width="802" height="500"/>
                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="Line">
                    <multiAxisData>
                        <multiAxisDataset>
                        </multiAxisDataset>
                        <dataAxis axis="Rows">
                            <axisLevel name="column2">
                                <labelExpression><![CDATA["column 2"]]></labelExpression>
                                <axisLevelBucket class="java.lang.String">
                                    <bucketExpression><![CDATA[$F{column2}]]></bucketExpression>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <dataAxis axis="Columns">
                            <axisLevel name="column1">
                                <labelExpression><![CDATA["column 1"]]></labelExpression>
                                <axisLevelBucket class="java.lang.String">
                                    <bucketExpression><![CDATA[$F{column1}]]></bucketExpression>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <multiAxisMeasure name="column3" class="java.lang.Double" calculation="Nothing">
                            <labelExpression><![CDATA["column 3"]]></labelExpression>
                            <valueExpression>$F{column3}</valueExpression>
                        </multiAxisMeasure>
                    </multiAxisData>
                </hc:chart>
            </componentElement>
        </band>
    </summary>
</jasperReport>

Jasper HTML5 multiline chart with group by result set

1 Answers