how to group records in html chart 5 Jasper Studio

[dmartinez_4]

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>[/code]

 

[yama818]

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 hoursFROM test.tableGROUP BY request

 

I hope this is useful to you.