jsonjasper-reportssubreport
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.
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!
Solution
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>
- Need jolt spec for dynamic colum
- Jolt spec to lookup keys of an array
- Helm convert data for nginx template
- jqGrid not rendering data - Headers visible
- Performance parsing JSON on Android emulator vs physical device
- jsonpath in bigquery doesn't support @ for filter. Suggestions for alternatives?
- Create product in printful using API - Python
- Set string variable to dynamic list column value gives error: Input parameter of operation Set Variable contains invalid expression. PowerAutomate
- How to examine api without documentation?
- add JSON objects to specific index - Python
- What is the difference between a .JSON file and .JL file?
- How to get the POST values from serializeArray?
- How to parse JSON data with jQuery / JavaScript?
- How to Transform record using Json Jolt to expand lists while preserving index
- Postman Json Schema validation failed
- How to read the request body using orjson library in FastAPI?
- How to return data in JSON format using FastAPI?
- .NET return JSON without reformating
- How do I turn a C# object into a JSON string in .NET?
- .NET Core: Remove null fields from API JSON response
- Is there a quick way to convert a JavaScript object to valid JSON in the text editor?
- NumPy array is not JSON serializable
- JSON Response is being truncated
- How do I write JSON data to a file?
- How to extract multiple JSON objects from one file?
- How can I make sure Powershell will correctly render all non-latin characters into the output?
- integrating the objects of one array into a second array with JOLT
- parsing a JSON containing several stringified jsons using Batch and JQ
- How to read the json file in spark using scala?
- How to compare JSON documents and return the differences with Jackson or Gson?