how to group records in html chart 5 Jasper Studio

Posted on September 24, 2021 at 7:57am

I have the following records. 

request hours
1000 4
1000 8
1000 10
1001 20
1002 18
1002 30
1003 75
1004 86

Total Tickets 5

And I need to make a chart column html5, that shows the number of requirements whose time in hours is less than or equal to 1 day, or 2 days, or 3 days and so on.

Y axis, Measure: number of requirements X axis, Category: days consumed

The graph remains..

days count
1 5
2 1
4 2

total ticket 8

And this is wrong because it should be.

days count
1 2
2 1
4 2

total ticket 5.

I tried with a variable that counts when the record changes in request, but I couldn't take the total per request.

I tried assembling groups and although I was able to assign the total by groups to a variable, I cannot tell the graph that it only counts one. and I can't tell you how to add only the last record of the group.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 7.5.0.final using JasperReports Library version 6.11.0-0c4056ccaa4d25a5a8c45672d2f764ea3498bebb  -->
<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="0e6ff0a6-42fa-4b32-8d63-7860b3cdd7c1">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="EXCEL"/>
    <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="246"/>
    <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="747"/>
    <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w1" value="386"/>
    <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="600"/>
    <queryString>
        <![CDATA[SELECT * FROM ]]>
    </queryString>
    <field name="request" class="java.lang.Integer"/>
    <field name="hours" class="java.lang.Integer"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <summary>
        <band height="171" splitType="Stretch">
            <componentElement>
                <reportElement x="10" y="10" width="520" height="150" uuid="ec0aa409-27df-40f2-a562-b4abd9361366"/>
                <hc:chart xmlns:hc="http://jaspersoft.com/highcharts" xsi:schemaLocation="http://jaspersoft.com/highcharts http://jaspersoft.com/schema/highcharts.xsd" type="Column">
                    <hc:chartSetting name="default">
                        <hc:chartProperty name="title.text" value=""/>
                        <hc:chartProperty name="credits.enabled" value="false"/>
                        <hc:chartProperty name="credits.href" value=""/>
                        <hc:chartProperty name="credits.text" value=""/>
                        <hc:chartProperty name="yAxis.title.text" value=""/>
                        <hc:chartProperty name="chart.zoomType" value="xy"/>
                        <hc:chartProperty name="plotOptions.column.dataLabels.enabled_customSimpleMode" value="true"/>
                        <hc:chartProperty name="plotOptions.column.dataLabels.enabled">
                            <hc:propertyExpression><![CDATA[true]]></hc:propertyExpression>
                        </hc:chartProperty>
                    </hc:chartSetting>
                    <multiAxisData>
                        <multiAxisDataset>
                            <dataset resetType="Report"/>
                        </multiAxisDataset>
                        <dataAxis axis="Rows">
                            <axisLevel name="Level1">
                                <labelExpression><![CDATA["Level Label expression"]]></labelExpression>
                                <axisLevelBucket class="java.lang.Comparable">
                                    <bucketExpression><![CDATA[$F{hours}<=24?1:
    $F{hours}<=48?2:
       $F{hours}<=72?3:
           $F{hours}<=96?4:null]]></bucketExpression>
                                </axisLevelBucket>
                            </axisLevel>
                        </dataAxis>
                        <dataAxis axis="Columns"/>
                        <multiAxisMeasure name="Measure1" class="java.lang.Number" calculation="Count">
                            <labelExpression><![CDATA[]]></labelExpression>
                            <valueExpression><![CDATA[$F{request}]]></valueExpression>
                        </multiAxisMeasure>
                    </multiAxisData>
                    <hc:series name="Measure1"/>
                </hc:chart>
            </componentElement>
        </band>
    </summary>
</jasperReport>

Jaspersoft® Studio
dmartinez_4's picture
dmartinez_4
2
Joined: Aug 23 2021 - 10:19am
Last seen: 1 year 8 months ago

1 Answer:

Posted on September 24, 2021 at 7:36pm

This may not be the best way to do it, but I was able to achieve it with the following method.

 

Image after changing settings

 

* Change SQL


 

SELECT
 request
 ,SUM(hours) AS hours
FROM
 test.table
GROUP BY
 request

I hope this is useful to you.

yama818's picture
yama818
6707
Joined: Aug 17 2018 - 3:48pm
Last seen: 1 month 3 weeks ago
Feedback