julio_12 Posted July 3, 2019 Share Posted July 3, 2019 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" } ] } } Link to comment Share on other sites More sharing options...
narcism Posted July 4, 2019 Share Posted July 4, 2019 It depends on how complex are your JSON data and the desired output. Could you detail more on that? Link to comment Share on other sites More sharing options...
julio_12 Posted July 4, 2019 Author Share Posted July 4, 2019 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. Link to comment Share on other sites More sharing options...
narcism Posted July 4, 2019 Share Posted July 4, 2019 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#jsonqlIf 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 Link to comment Share on other sites More sharing options...
julio_12 Posted July 4, 2019 Author Share Posted July 4, 2019 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now