Invoice master-detail in JSON report

[julio_12]

Hello all.

 

I'm very new with this tool, and I'm trying to create an invoice report. I can easily use JSON as data source and put the invoice data in page header, but I can't understand how can I show the items in details section. Must I use a subreport for that?

My JSON is something like this:

{  "invoice": [    {      "number": 1234      "items": [        {          "name": "Product name",          "value": "10.00"        },        {          "name": "Product name",          "value": "10.00"        }      ]    }  }     

[narcism]

It depends on how complex are your JSON data and the desired output. Could you detail more on that?

[julio_12]

Hi narcism.

It's not a complex report. The JSON data is almost simple as the exem above. I just want to show some Invoice main data in header (cutsomer name, customer VAT, date, customer address) and the items he bought in details (item name, quantity, unitary value and total value).

 

I just can't figured it out how to show the "items" part of JSON as details in the report.

[narcism]

The easiest way to accomplish your task would be to use the newer JSONQL language. It has proper support starting with Jaspersoft Studio 6.4.0.

With JSONQL you usually don't need subreports for deep JSON tree traversals.

For a JSON like this:

{    "invoice": [{        "number": 1234,        "items": [{                "name": "Product name1",                "value": "10.00"            },            {                "name": "Product name2",                "value": "11.00"            }        ]    }]}[/code]

this is what your report might look like:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3  --><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="Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="b737898e-fb0a-469b-8c73-efd525619536">    <queryString language="jsonql">        <![CDATA[..items.*]]>    </queryString>    <field name="invoiceNo" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="^^.number"/>        <fieldDescription><![CDATA[invoice No]]></fieldDescription>    </field>    <field name="name" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="name"/>        <fieldDescription><![CDATA[Product Name]]></fieldDescription>    </field>    <field name="value" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="value"/>        <fieldDescription><![CDATA[Product Value]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="79" splitType="Stretch">            <staticText>                <reportElement x="180" y="20" width="200" height="30" uuid="16ef1203-859b-48c4-bc78-61fa44c46903"/>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="14"/>                </textElement>                <text><![CDATA[basic JSONQL Traversal]]></text>            </staticText>        </band>    </title>    <pageHeader>        <band height="35" splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band height="30" splitType="Stretch">            <staticText>                <reportElement mode="Opaque" x="0" y="0" width="185" height="30" backcolor="#C3F3FA" uuid="ed0f872a-643f-4b75-8ab1-7eb5d379541f">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bbb61538-1d5b-4e54-864c-314aa6a4ac87"/>                </reportElement>                <text><![CDATA[invoice No]]></text>            </staticText>            <staticText>                <reportElement mode="Opaque" x="185" y="0" width="185" height="30" backcolor="#C3F3FA" uuid="505d42c7-ada6-42be-88dd-913efc637f58">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5bcddd82-bece-48bf-8d64-98092796d145"/>                </reportElement>                <text><![CDATA[Product Name]]></text>            </staticText>            <staticText>                <reportElement mode="Opaque" x="370" y="0" width="185" height="30" backcolor="#C3F3FA" uuid="a2439544-1784-4ef9-b216-2e862253ad72">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="645fb340-d87c-44ca-bfab-31d97554b215"/>                </reportElement>                <text><![CDATA[Product Value]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="30" splitType="Stretch">            <textField>                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="185" height="30" uuid="066ee7e6-9153-487b-b599-e7dfd706680d">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bbb61538-1d5b-4e54-864c-314aa6a4ac87"/>                </reportElement>                <textFieldExpression><![CDATA[$F{invoiceNo}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="185" y="0" width="185" height="30" uuid="d83517c6-4d51-490a-94e3-ae78e4e60693">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5bcddd82-bece-48bf-8d64-98092796d145"/>                </reportElement>                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="370" y="0" width="185" height="30" uuid="6c8b1a93-cc44-4a2d-b47b-ed860e9575c4">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="645fb340-d87c-44ca-bfab-31d97554b215"/>                </reportElement>                <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>            </textField>        </band>    </detail>    <columnFooter>        <band height="45" splitType="Stretch"/>    </columnFooter>    <pageFooter>        <band height="54" splitType="Stretch"/>    </pageFooter>    <summary>        <band height="42" splitType="Stretch"/>    </summary></jasperReport>[/code]

Some simple explanation regarding the JSONQL queries:

1. The query string "..items.*" simple traslates to: pick all the children(.*) of "items", wherever(..) the "items" key resides in the JSON tree.

2. The "^^.number" query traslates to: go back up twice in the JSON tree to reach the invoice item, then select the number key. An equivalent syntax for this query is: "^{2}.number"

3. More on the JSONQL query language here: http://jasperreports.sourceforge.net/sample.reference/jsonqldatasource/index.html#jsonql

If JSONQL is not an option for you, checkout the jsondatasource sample code from our repository here: https://github.com/TIBCOSoftware/jasperreports/tree/master/jasperreports/demo/samples/jsondatasource

[julio_12]

Thank you very much!

I got the concept and it was possible to reproduce. I understood that the key is "dislocate" the report to an internal node then "walk" through JSON structure to position other fields, right?

You were very helpful!