Valter Henrique Valter Henrique - 14 days ago 4
JSON Question

How to group dataset using JSON with Jasper Report?

I'm new to Jasper Report, and I'm using JSON as datasource. I have the following dataset, as a sample.

{"Northwind": {
"Customers": [
...
],
"Orders": [
{
"ShipPostalCode": 51100,
"ShippedDate": "1996-07-16",
"OrderDate": "1996-07-04",
"OrderID": 10248,
"Freight": 32.38,
"RequiredDate": "1996-08-01",
"ShipCity": "Berlin",
"ShipCountry": "Germany",
"EmployeeID": 5,
"ShipVia": 3,
"CustomerID": "ALFKI",
"ShipAddress": "59 rue de l'Abbaye",
"ShipName": "Vins et alcools Chevalier"
},
...
{
"ShipPostalCode": 44087,
"ShippedDate": "1996-07-10",
"OrderDate": "1996-07-05",
"OrderID": 10249,
"Freight": 11.61,
"RequiredDate": "1996-08-16",
"ShipCity": "Munich",
"ShipCountry": "Germany",
"EmployeeID": 6,
"ShipVia": 1,
"CustomerID": "RATTC",
"ShipAddress": "Luisenstr. 48",
"ShipName": "Martinez Gonzalez"
}


I'm passing the
ShipCountry
as parameter to my subreport, which is working as it should.

enter image description here

However, my template is showing the same data, twice, since it loops my dataset everytime for every object on my subset.
How group my orders by country ? How can I make to not show it duplicated ? Like this:

enter image description here

My country_orders_report.jrxml and country_order_list.jrxml code on GitHub. And my whole data, is very small :)

Hope that helps!

Answer

This happens because you have the same result for both queries in your main report and subreport.

In your main report - JsonCountryReport.jrxml - you have this query:

Northwind.Orders(ShipCountry == Germany)

that will produce 2 results. This implies rendering the subreport twice(for each query result).

Then, in your subreport - JsonCountryOrdersReport.jrxml - you have almost the same query:

Northwind.Orders(ShipCountry == $P{ShipCountry})

that will translate into the one above because you pass the same value for ShipCountry. The outcome should become obvious now.

You probably want to have:

Northwind.Customers(Country == Germany)

as your main report query with a Country field that you pass on to the subreport instead of ShipCountry.

EDIT: Since Orders' ShipCountry is only to be used, then you could use a ShipCountry group as @AlexK mentioned. However, placing the subreport in the groupHeader band is total overkill. You already have all the required data from your main query, so a subreport will not be necessary. You could restructure your main report as follows:

<?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="country_orders_report_new" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="50" bottomMargin="50" uuid="bbe115b5-a5a0-4b39-9b73-7092dc59ab6d">
  <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSON Data Adapter"/>
  <style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
  <style name="Sans_Normal_8" style="Sans_Normal" fontSize="8"/>
  <style name="Sans_Bold" fontName="DejaVu Sans" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
  <style name="Sans_Bold_8" style="Sans_Bold" fontSize="8"/>
  <queryString language="json">
    <![CDATA[Northwind.Orders]]>
  </queryString>
  <field name="ShipCountry" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="ShipCountry"/>
  </field>
  <field name="Id" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="OrderID"/>
  </field>
  <field name="OrderDate" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="OrderDate"/>
  </field>
  <field name="ShipCity" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="ShipCity"/>
  </field>
  <field name="Freight" class="java.lang.Float">
    <property name="net.sf.jasperreports.json.field.expression" value="Freight"/>
  </field>
  <sortField name="ShipCountry"/>
  <variable name="OrderNumber" class="java.lang.Integer" resetType="Group" resetGroup="ShipCountryGroup" calculation="Count">
    <variableExpression><![CDATA[0]]></variableExpression>
  </variable>
  <variable name="TotalFreight" class="java.lang.Float" resetType="Group" resetGroup="ShipCountryGroup" calculation="Sum">
    <variableExpression><![CDATA[$F{Freight}]]></variableExpression>
  </variable>
  <group name="ShipCountryGroup" isReprintHeaderOnEachPage="true">
    <groupExpression><![CDATA[$F{ShipCountry}]]></groupExpression>
    <groupHeader>
      <band height="21">
        <textField>
          <reportElement style="Sans_Bold" x="5" y="5" width="100" height="15" isPrintWhenDetailOverflows="true" uuid="0aaeed6e-7ba1-4ab9-be59-d6ca702995fc"/>
          <textFieldExpression><![CDATA[$F{ShipCountry}]]></textFieldExpression>
        </textField>
        <line>
          <reportElement x="0" y="20" width="515" height="1" isPrintWhenDetailOverflows="true" uuid="6d76c22c-329f-4e77-a886-8580d3cb6bc1"/>
        </line>
      </band>
      <band height="14">
        <frame>
          <reportElement mode="Opaque" x="0" y="2" width="356" height="10" forecolor="#CCFFFF" backcolor="#CCFFFF" uuid="e9af134f-31eb-48be-bd9b-292188f2554f"/>
          <staticText>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="0" y="0" width="48" height="10" backcolor="#CCFFFF" uuid="62e5e770-7b05-4ecd-a254-ab0c7f643a37"/>
            <textElement textAlignment="Right"/>
            <text><![CDATA[ID]]></text>
          </staticText>
          <staticText>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="54" y="0" width="87" height="10" backcolor="#CCFFFF" uuid="c472f825-47f4-4e16-a782-cc4b02572cb0"/>
            <textElement textAlignment="Center"/>
            <text><![CDATA[Order Date]]></text>
          </staticText>
          <staticText>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="146" y="0" width="108" height="10" backcolor="#CCFFFF" uuid="89b5edba-1606-4f5d-89cb-144042c1fcdd"/>
            <text><![CDATA[Ship City]]></text>
          </staticText>
          <staticText>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="259" y="0" width="92" height="10" backcolor="#CCFFFF" uuid="e7c6fbe3-ecb2-4c65-83d6-7c813448cec6"/>
            <textElement textAlignment="Right"/>
            <text><![CDATA[Freight]]></text>
          </staticText>
        </frame>
      </band>
    </groupHeader>
    <groupFooter>
      <band height="14">
        <frame>
          <reportElement mode="Opaque" x="0" y="2" width="356" height="10" forecolor="#33CCCC" backcolor="#33CCCC" uuid="084cfbb4-f390-4302-8bf5-2e65b34829b8"/>
          <staticText>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="160" y="0" width="67" height="10" backcolor="#33CCCC" uuid="06753d49-aed5-46c8-be06-b107f81d7c2f"/>
            <textElement textAlignment="Right"/>
            <text><![CDATA[Total :]]></text>
          </staticText>
          <textField>
            <reportElement style="Sans_Bold_8" mode="Opaque" x="227" y="0" width="27" height="10" backcolor="#33CCCC" uuid="d8edf4da-1e47-45ec-bbf3-f63b3bf0b93b"/>
            <textElement textAlignment="Right"/>
            <textFieldExpression><![CDATA[$V{OrderNumber}]]></textFieldExpression>
          </textField>
          <textField pattern="¤ #,##0.00">
            <reportElement style="Sans_Bold_8" mode="Opaque" x="259" y="0" width="92" height="10" backcolor="#33CCCC" uuid="a713b487-68a2-4391-a231-9bf9aa740090"/>
            <textElement textAlignment="Right"/>
            <textFieldExpression><![CDATA[$V{TotalFreight}]]></textFieldExpression>
          </textField>
        </frame>
      </band>
    </groupFooter>
  </group>
  <title>
    <band height="50">
      <line>
        <reportElement x="0" y="0" width="515" height="1" uuid="fc148b4e-50df-4a12-aa14-8505a4cfa6e1"/>
      </line>
      <staticText>
        <reportElement style="Sans_Normal" x="0" y="10" width="515" height="30" uuid="5bf7651c-cd6b-4eaf-b65a-1413d60faab0"/>
        <textElement textAlignment="Center">
          <font size="22"/>
        </textElement>
        <text><![CDATA[Country Orders Report]]></text>
      </staticText>
    </band>
  </title>
  <pageHeader>
    <band height="21">
      <staticText>
        <reportElement style="Sans_Bold" mode="Opaque" x="0" y="5" width="515" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="da0f1cad-f552-424b-bf19-b41cabbfa4ac"/>
        <text><![CDATA[Country Order List]]></text>
      </staticText>
    </band>
  </pageHeader>
  <detail>
    <band height="14">
      <textField>
        <reportElement style="Sans_Normal_8" x="0" y="2" width="51" height="10" uuid="ec54687d-3c95-4647-9db5-fa71a6e81009"/>
        <textElement textAlignment="Right"/>
        <textFieldExpression><![CDATA[$F{Id}]]></textFieldExpression>
      </textField>
      <textField isStretchWithOverflow="true" pattern="yyyy, MMM dd">
        <reportElement style="Sans_Normal_8" positionType="Float" x="54" y="2" width="87" height="10" uuid="a112ba7b-c321-467c-91ec-ffb513c23338"/>
        <textElement textAlignment="Center"/>
        <textFieldExpression><![CDATA[$F{OrderDate}]]></textFieldExpression>
      </textField>
      <textField isStretchWithOverflow="true">
        <reportElement style="Sans_Normal_8" positionType="Float" x="146" y="2" width="108" height="10" uuid="6a61edb3-239e-4791-a046-a6459343ac07"/>
        <textFieldExpression><![CDATA[$F{ShipCity}]]></textFieldExpression>
      </textField>
      <textField isStretchWithOverflow="true" pattern="¤ #,##0.00">
        <reportElement style="Sans_Normal_8" positionType="Float" x="259" y="2" width="92" height="10" uuid="61a8a117-6a43-46a7-9b96-10c5beb578ab"/>
        <textElement textAlignment="Right"/>
        <textFieldExpression><![CDATA[$F{Freight}]]></textFieldExpression>
      </textField>
    </band>
  </detail>
  <pageFooter>
    <band height="40">
      <line>
        <reportElement x="0" y="10" width="515" height="1" uuid="1371178a-a590-4616-affe-a4e1a24bcc84"/>
      </line>
      <textField>
        <reportElement x="200" y="20" width="80" height="15" uuid="3eb302d8-0855-4f82-a666-3c9628dce372"/>
        <textElement textAlignment="Right"/>
        <textFieldExpression><![CDATA["Page " + String.valueOf($V{PAGE_NUMBER}) + " of"]]></textFieldExpression>
      </textField>
      <textField evaluationTime="Report">
        <reportElement x="280" y="20" width="75" height="15" uuid="86f46fca-dbcb-4a60-b2f7-f8da6a4224f7"/>
        <textFieldExpression><![CDATA[" " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>
      </textField>
    </band>
  </pageFooter>
</jasperReport>
Comments