How to group dataset using JSON with Jasper Report?

Question

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.

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:

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

Hope that helps!

You should look at: [Jasperreports - how to get JSON to populate a subreport](http://stackoverflow.com/q/14941002/876298) — Alex K, Nov 17 '16 at 12:37
Another similar post: [How to create multiple tables in jasper report using json as a datasource?](http://stackoverflow.com/q/34603844/876298) — Alex K, Nov 17 '16 at 12:43
@AlexK Thank you for the links, but I don't understand it yet :( I'm sorry man, I'm new to Jasper Reports. — Valter Silva, Nov 17 '16 at 12:51
You can create group on *ShipCountry* and place the subreport to *groupHeader* band, for example — Alex K, Nov 17 '16 at 16:06
@AlexK Thank you so much for your suggestion! It worked! Here is the result: https://github.com/valterhenrique/reports/blob/stackoverflow_40654817/Demo/demo/samples/jsondatasource/reports/country/country_report.jrxml — Valter Silva, Nov 17 '16 at 17:06

Narcis · Accepted Answer · 2016-11-17T22:18:08.000

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>

But I don't want to group my orders by the countries of my customers, I want to group my orders by its countries. — Valter Silva, Nov 17 '16 at 15:31
@ValterHenrique BTW, is it better to include the source code to your question. At the present moment links are dead — Alex K, Nov 17 '16 at 15:54
@AlexK My question is updated :) In any case, this is the repository (with a branch only for this question) https://github.com/valterhenrique/reports/tree/stackoverflow_40654817 — Valter Silva, Nov 17 '16 at 16:04
Hey @narcis, thanks a lot for you code, it worked! Can you explain why is a overkill to put the subreport inside of the groupHeader ? — Valter Silva, Nov 18 '16 at 08:44
For those who might wanna try it, the code is available here: https://github.com/valterhenrique/reports/tree/try-narcis-suggestion — Valter Silva, Nov 18 '16 at 08:45
@ValterHenrique I have already explained why it is overkill: because you have all the data you need when selecting all orders in the main query, so there is no need to re-execute the filtering query in the subreport. Moving the logic from the subreport to the main report addresses this issue. On my machine, running the original report with subreport on the full nortwind.json takes on average 1.6 seconds, whereas the improved version that I have posted takes on average 0.35 seconds. These values are taken from the JasperSoft Studio report state output - the Report Execution Time. — Narcis, Nov 21 '16 at 11:07

How to group dataset using JSON with Jasper Report?

1 Answers1