How to sum by unsorted, multiplicated groups

[MartinS1981]

Hi folks,

my simplified data coming from CSV adapter is as follows:
 

Product	Count
A	10
A	5
B	7
A	1

 

I'd like to sum count of all products by group Product and later use it on a bar chart. Unfortunately, products are unsorted so A occurs multiple times so I cannot use a variable that sums it and resets on Product [Group]. How can I solve this problem/

[hozawa]

1. Open up "Dataset and Query Dialog".

2. In the bottom tab panel, select "Sorting".

3. Click on the "Add" button and select "Product" as the sort key. This will sort the csv file so you'll be able to group by Product.

 

[MartinS1981]

I should have mentioned, that my data source is a CSV adapter with a file containing 16 records. I've created a new dataset with sorting by field "Product" bu in "Data Preview" rows are duplicated (first 16 rows are in the usual order and then there are another 16 rows sorted accordingly to the Product field set in Sorting tab. Also When I hooked it up to a bar chart, my chart is completely empty.

My chart is configured as follows:

[hozawa]

>first 16 rows are in the usual order and then there are another 16 rows sorted accordingly to the Product field set in Sorting tab

You didn't set it up correctly. There should only be 16 sorted rows. I've tested csv adapter on Jaspersoft Studio 6.5.1.

Unfortunately, there's a bug in Data preview but when I print them in Details band, there's no duplicates.

Bar chart was alright too. Not sure why you have "Category" set to "0" instead of $F{PRODUCT} and also why you have Value set to $F{PRODUCT} instead of a variable containing the sum of the count. Try setting Reset on to "Report".

That is, create a variable such as SUM_COUNT.

Value Class Name: java.lang.Integer

Calculation: Sum

Expression: $F{COUNT}

Initial Value Expression: 0

Reset type: {Group]Produkt 

[MartinS1981]

Then clearly either I'm doing something wrong or there is something wrong with 6.8 version. I've tried the settings you proposed but the chart is still empty. Previous settings were remainings of try and error process to show anything on the bar chart with the new dataset. 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.8.0.final using JasperReports Library version 6.8.0-2ed8dfabb690ff337a5797129f2cd92902b0c87b  -->
<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="SampleCSV" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="98d50966-545a-4b68-af9d-c4062b452be9">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SampleCSV"/>
    <subDataset name="Dataset1" uuid="0ad0b1a5-3579-4a7a-a5e3-87ef75f824d7">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SampleCSV"/>
        <queryString language="csv">
            <![CDATA[]]>
        </queryString>
        <field name="Product" class="java.lang.String"/>
        <field name="Count" class="java.lang.Integer"/>
        <sortField name="Product"/>
        <variable name="Variable_1" class="java.lang.Integer" resetType="Group" resetGroup="Product" calculation="Sum">
            <variableExpression><![CDATA[$F{Count}]]></variableExpression>
            <initialValueExpression><![CDATA[0]]></initialValueExpression>
        </variable>
        <group name="Product">
            <groupExpression><![CDATA[$F{Product}]]></groupExpression>
        </group>
    </subDataset>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="Product" class="java.lang.String"/>
    <field name="Count" class="java.lang.Integer"/>
    <variable name="Variable_1" class="java.lang.Integer" resetType="Group" resetGroup="Product" calculation="Sum">
        <variableExpression><![CDATA[$F{Count}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <group name="Product">
        <groupExpression><![CDATA[$F{Product}]]></groupExpression>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <detail>
        <band height="267" splitType="Stretch">
            <barChart>
                <chart evaluationTime="Report">
                    <reportElement x="342" y="67" width="200" height="200" uuid="27426e64-8bce-4491-b3cd-5570ff9ce7bf"/>
                    <chartTitle/>
                    <chartSubtitle/>
                    <chartLegend/>
                </chart>
                <categoryDataset>
                    <dataset>
                        <datasetRun subDataset="Dataset1" uuid="d9db396e-3bcc-45e2-9ea2-d95d2bfad969">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                    <categorySeries>
                        <seriesExpression><![CDATA[$F{Product}]]></seriesExpression>
                        <categoryExpression><![CDATA[$F{Product}]]></categoryExpression>
                        <valueExpression><![CDATA[$V{Variable_1}]]></valueExpression>
                    </categorySeries>
                </categoryDataset>
                <barPlot>
                    <plot/>
                    <itemLabel/>
                    <categoryAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </categoryAxisFormat>
                    <valueAxisFormat>
                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>
                    </valueAxisFormat>
                </barPlot>
            </barChart>
        </band>
    </detail>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

 

[hozawa]

Yes. Jaspersoft Studio 6.8.0 is buggy so I usually don't use it. As I've replied earlier, I'm using version 6.5.1.

[hozawa]

 

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.5.1.final using JasperReports Library version 6.5.1  --><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="CsvSample" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="6d9f4558-bfc3-4004-8698-87524310ce70">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="SampleCsv"/>    <queryString>        <![CDATA[]]>    </queryString>    <field name="Product" class="java.lang.String"/>    <field name="Count" class="java.lang.Integer"/>    <sortField name="Product"/>    <variable name="ProductTotal" class="java.lang.Integer" resetType="Group" resetGroup="Product" calculation="Sum">        <variableExpression><![CDATA[$F{Count}]]></variableExpression>        <initialValueExpression><![CDATA[0]]></initialValueExpression>    </variable>    <group name="Product">        <groupExpression><![CDATA[$F{Product}]]></groupExpression>    </group>    <title>        <band height="500" splitType="Stretch">            <barChart>                <chart evaluationTime="Report">                    <reportElement x="0" y="0" width="560" height="500" uuid="b2907a7d-302d-4dcf-ba2f-4e216c49b760"/>                    <chartTitle/>                    <chartSubtitle/>                    <chartLegend/>                </chart>                <categoryDataset>                    <categorySeries>                        <seriesExpression><![CDATA["SERIES 1"]]></seriesExpression>                        <categoryExpression><![CDATA[$F{Product}]]></categoryExpression>                        <valueExpression><![CDATA[$V{ProductTotal}]]></valueExpression>                    </categorySeries>                </categoryDataset>                <barPlot>                    <plot/>                    <itemLabel/>                    <categoryAxisFormat>                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>                    </categoryAxisFormat>                    <valueAxisFormat>                        <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000"/>                    </valueAxisFormat>                </barPlot>            </barChart>        </band>    </title></jasperReport>

 

[MartinS1981]

@hozawa

Your solution works for me but you didn't create a separate dataset as you suggested in the first place. All you did was setting up a sortField in main report dataset. How can I achieve the same using separate subdataset?