Printed Json Values

Posted on October 19, 2018 at 1:46pm

Hi !
I trying to make a report in jasper with a Json as parameter, I send a json with the datas to print, but I don't know how I can print this datas.

I make the calcules in a array in php, I calculate the payments in a purchase, let's supose you buy a game and divide the payment in two times, I calculate your next payment, and ignore de weekend and holidays. After I made the calcules and before I send to jrxml I transform the array in a JSON and send to the jrxml file(I tried the use in a array, but the framework that I use don't convert the array in string, he made some error), but I don't know how printed the values in a JSON

I try use this:

    <parameter name="jsonString" class="java.lang.String">
        <defaultValueExpression><![CDATA["{\"a\": [ {\"b\": { \"val\": \"val1\"}}, {\"b\": { \"val\": \"val2\" }}]}"]]></defaultValueExpression>
    </parameter>
    <parameter name="JSON_INPUT_STREAM" class="java.io.InputStream">
        <defaultValueExpression><![CDATA[new java.io.ByteArrayInputStream($P{jsonString}.getBytes("UTF-8"))]]></defaultValueExpression>
    </parameter>

I tried put the JSON_INPUT_STREAM.tostring(), but don't work.

This is the JSON

{
    "order":"000000",
    "valueorder":"270.00",
    "payment":"CRED CARD 2X",
    "date":"2017-03-17",
    "nextpayment":[
                   "17\/03\/2017",
                   "02\/05\/2017"
    ],
    "valuenextpayment":[
                    "R$\u00a0135,00",
                    "R$\u00a0135,00"
]}

The other two JSON in the ORDER JSON I thinking break in another two JSONS, in JSON of nextpayments and a JSON of valuenextpayments, I think this to facilitate in jaspersoft, if this not help in nothing I will make of that form, one ORDER JSON with two JSON inside.

And one more thing, how I print the values like a table (xls) ?

Ex:

Order - Value - Payment - Date - Next Payments

0000 - 100,00 - Cred Card 2x - 10/10/10 - 10/11/10

1111 - 200.00 - Cred Card 2x - 10/10/10 - 10/11/10

Anyone can help me ?
Thanks!

Gabriel Nadoroski

Jaspersoft® Studio

gabriel.nadoroski

812

Joined: Oct 1 2018 - 2:05pm

Last seen: 4 years 9 months ago

That is not a valid JSON

narcism - 4 years 11 months ago

this is the json, the other i just tried make more beautiful

gabriel.nadoroski - 4 years 11 months ago

To make the JSON traversal easier, since you are in control of its generation, why don't you build something like this instead?:

{
"order":"000000",
"valueorder":"270.00",
"payment":"CRED CARD 2X",
"date":"2017-03-17",
"nextpayments": [
{
"date": "17/03/2017",
"value": "R$\u00a0135,00"
},
{
"date": "02/05/2017",
"value": "R$\u00a0135,00"
}

]
}

This way the data is logically grouped and easier to display.

narcism - 4 years 11 months ago

show 1 more...

I know, I thought I'd do it this way., but the others developers in my job said to do this way, they says is better.

I new in developer world, I just do what they say to me do.

Anyway, if I send to de jrxml file this Json, he print in report ?

how I print this ?

I send to the parameter jsonString, and I try print the JSON_INPUT_STREAM but doesn't work.

And one more thing, how I print the values like a table (xls) ?

Ex:

Order - Value - Payment - Date - Next Payments

0000 - 100,00 - Cred Card 2x - 10/10/10 - 10/11/10

1111 - 200.00 - Cred Card 2x - 10/10/10 - 10/11/10

I will edit the post again and put this in him, don't worry. LOL

gabriel.nadoroski - 4 years 11 months ago

add comment

1 Answer:

Posted on October 22, 2018 at 2:06am

You could check out my answer here: https://stackoverflow.com/a/37441567/5882963

It contains a sample JRXML that starts with XML data and then about JSON. In essence you need to pass the JSON data as a String parameter and then convert it to an InputStream and construct a special parameter that JasperReports picks up and fills the report:

<parameter name="jsonString" class="java.lang.String">
  <defaultValueExpression><![CDATA["{\"a\": [ {\"b\": { \"val\": \"val1\"}}, {\"b\": { \"val\": \"val2\" }}]}"]]></defaultValueExpression>
</parameter>
<parameter name="JSON_INPUT_STREAM" class="java.io.InputStream">
  <defaultValueExpression><![CDATA[new java.io.ByteArrayInputStream($P{jsonString}.getBytes("UTF-8"))]]></defaultValueExpression>
</parameter>

Edit:

With a JSON like this:

{
    "order":"000000",
    "valueorder":"270.00",
    "payment":"CRED CARD 2X",
    "date":"2017-03-17",
    "nextpayment":[
        "17/03/2017",
        "02/05/2017"
    ],
    "valuenextpayment":[
        "R$\u00a0135,00",
        "R$\u00a0135,00"
]}

your report would look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="Report1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="16882c94-641b-47b6-bd2f-675edbc1f15a">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>
    <subDataset name="NextPaymentDateDataset" uuid="5337e69f-16e4-447d-a9e7-fbe6d25a479f">
        <field name="item" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="[0]"/>
            <fieldDescription><![CDATA[The Item in the Dates Array]]></fieldDescription>
        </field>
    </subDataset>
    <subDataset name="NextPaymentValueDataset" uuid="5337e69f-16e4-447d-a9e7-fbe6d25a479f">
        <field name="item" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="[0]"/>
            <fieldDescription><![CDATA[The Item in the Values Array]]></fieldDescription>
        </field>
    </subDataset>
    <parameter name="jsonString" class="java.lang.String">
        <defaultValueExpression><![CDATA["{\n" +
                "    \"order\": \"000000\",\n" +
                "    \"valueorder\": \"270.00\",\n" +
                "    \"payment\": \"CRED CARD 2X\",\n" +
                "    \"date\": \"2017-03-17\",\n" +
                "    \"nextpayment\": [\n" +
                "        \"17/03/2017\",\n" +
                "        \"02/05/2017\"\n" +
                "    ],\n" +
                "    \"valuenextpayment\": [\n" +
                "        \"R$\\u00a0135,00\",\n" +
                "        \"R$\\u00a0135,00\"\n" +
                "    ]\n" +
                "}"]]></defaultValueExpression>
    </parameter>
    <parameter name="JSON_INPUT_STREAM" class="java.io.InputStream">
        <defaultValueExpression><![CDATA[new java.io.ByteArrayInputStream($P{jsonString}.getBytes("UTF-8"))]]></defaultValueExpression>
    </parameter>
    <queryString language="jsonql">
        <![CDATA[]]>
    </queryString>
    <field name="order" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="order"/>
        <fieldDescription><![CDATA[order]]></fieldDescription>
    </field>
    <field name="valueorder" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="valueorder"/>
        <fieldDescription><![CDATA[valueorder]]></fieldDescription>
    </field>
    <field name="payment" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="payment"/>
        <fieldDescription><![CDATA[payment]]></fieldDescription>
    </field>
    <field name="date" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="date"/>
        <fieldDescription><![CDATA[date]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <staticText>
                <reportElement x="180" y="20" width="200" height="30" uuid="33c1adf9-dc58-4d35-a409-72de3d4f0347"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[Unrelated JSON arrays]]></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="80" height="30" backcolor="#BEFADB" uuid="5258fbf3-575f-486e-bede-26e83ec791a5">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <text><![CDATA[order]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="80" y="0" width="90" height="30" backcolor="#BEFADB" uuid="464a7d52-1801-4d67-9cda-c4b2d2e4526b">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <text><![CDATA[valueorder]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="170" y="0" width="100" height="30" backcolor="#BEFADB" uuid="c179e19a-efa9-4d27-947c-84b14d2639aa">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <text><![CDATA[payment]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="270" y="0" width="80" height="30" backcolor="#BEFADB" uuid="cb1829b9-c782-42a7-a3f4-0126db20cc6a">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[order date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="350" y="0" width="100" height="30" backcolor="#BEFADB" uuid="628c20fa-bbc9-43ea-8aa4-1486bf518e38">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="450" y="0" width="100" height="30" backcolor="#BEFADB" uuid="2b4c49a7-1984-48d4-aa92-9a6d859feda8">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment value]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="80" height="30" uuid="27c8ae6d-def5-4abd-b691-d350fd92c719">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{order}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="90" height="30" uuid="94342e9b-d545-441c-9350-9179e4cc2530">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{valueorder}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="170" y="0" width="100" height="30" uuid="8e5286de-d8ac-43a5-bc70-936d3b59db7d">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{payment}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="270" y="0" width="80" height="30" uuid="835948f8-45ba-4c4a-8306-c6ad70214ba0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{date}]]></textFieldExpression>
            </textField>
            <componentElement>
                <reportElement x="350" y="0" width="100" height="30" uuid="89f3fd2c-4be0-466c-9b22-c749e8b69d42"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="NextPaymentDateDataset" uuid="964313c4-37e8-408f-adca-e6c436a76d4b">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("nextpayment")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="100">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="30" uuid="143efbb1-c95a-49bf-adff-a618b5518c75"/>
                            <textFieldExpression><![CDATA[$F{item}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
            <componentElement>
                <reportElement x="450" y="0" width="100" height="30" uuid="d569f091-7eac-44db-8e12-d83837b0ddae"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="NextPaymentValueDataset" uuid="7e2aa9c7-6d1f-4722-8f78-1f52da462473">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("valuenextpayment")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="100">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="30" uuid="a71eae3c-0bb7-4286-a80c-714a59d9f869"/>
                            <textFieldExpression><![CDATA[$F{item}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

and produce the following output:

With a JSON like the one I've reccommended:

{
    "order":"000000",
    "valueorder":"270.00",
    "payment":"CRED CARD 2X",
    "date":"2017-03-17",
    "nextpayments": [
        {
            "date": "17/03/2017",
            "value": "R$\u00a0135,00" 
        }, 
        {
            "date": "02/05/2017",
            "value": "R$\u00a0135,00" 
        }
 
    ]
}

the JRXML will be more compact, with no lists and additional subDatasets:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="Report1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="16882c94-641b-47b6-bd2f-675edbc1f15a">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>
    <parameter name="jsonString" class="java.lang.String">
        <defaultValueExpression><![CDATA["{\n" +
                "    \"order\":\"000000\",\n" +
                "    \"valueorder\":\"270.00\",\n" +
                "    \"payment\":\"CRED CARD 2X\",\n" +
                "    \"date\":\"2017-03-17\",\n" +
                "    \"nextpayments\": [\n" +
                "        {\n" +
                "            \"date\": \"17/03/2017\",\n" +
                "            \"value\": \"R$\\u00a0135,00\" \n" +
                "        }, \n" +
                "        {\n" +
                "            \"date\": \"02/05/2017\",\n" +
                "            \"value\": \"R$\\u00a0135,00\" \n" +
                "        }\n" +
                "        \n" +
                "    ]\n" +
                "}"]]></defaultValueExpression>
    </parameter>
    <parameter name="JSON_INPUT_STREAM" class="java.io.InputStream">
        <defaultValueExpression><![CDATA[new java.io.ByteArrayInputStream($P{jsonString}.getBytes("UTF-8"))]]></defaultValueExpression>
    </parameter>
    <queryString language="jsonql">
        <![CDATA[nextpayments.*]]>
    </queryString>
    <field name="nextPaymentDate" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="date"/>
        <fieldDescription><![CDATA[next payment date]]></fieldDescription>
    </field>
    <field name="nextPaymentValue" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="value"/>
        <fieldDescription><![CDATA[next payment value]]></fieldDescription>
    </field>
    <field name="order" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.order"/>
        <fieldDescription><![CDATA[order]]></fieldDescription>
    </field>
    <field name="valueorder" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.valueorder"/>
        <fieldDescription><![CDATA[valueorder]]></fieldDescription>
    </field>
    <field name="payment" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.payment"/>
        <fieldDescription><![CDATA[payment]]></fieldDescription>
    </field>
    <field name="orderDate" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.date"/>
        <fieldDescription><![CDATA[order date]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <staticText>
                <reportElement x="180" y="20" width="200" height="30" uuid="33c1adf9-dc58-4d35-a409-72de3d4f0347"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[Restructured JSON]]></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="80" height="30" backcolor="#BEFADB" uuid="5258fbf3-575f-486e-bede-26e83ec791a5">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <text><![CDATA[order]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="80" y="0" width="90" height="30" backcolor="#BEFADB" uuid="464a7d52-1801-4d67-9cda-c4b2d2e4526b">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <text><![CDATA[valueorder]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="170" y="0" width="100" height="30" backcolor="#BEFADB" uuid="c179e19a-efa9-4d27-947c-84b14d2639aa">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <text><![CDATA[payment]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="270" y="0" width="80" height="30" backcolor="#BEFADB" uuid="cb1829b9-c782-42a7-a3f4-0126db20cc6a">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[order date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="350" y="0" width="100" height="30" backcolor="#BEFADB" uuid="628c20fa-bbc9-43ea-8aa4-1486bf518e38">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="450" y="0" width="100" height="30" backcolor="#BEFADB" uuid="2b4c49a7-1984-48d4-aa92-9a6d859feda8">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment value]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="80" height="30" uuid="27c8ae6d-def5-4abd-b691-d350fd92c719">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{order}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="90" height="30" uuid="94342e9b-d545-441c-9350-9179e4cc2530">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{valueorder}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="170" y="0" width="100" height="30" uuid="8e5286de-d8ac-43a5-bc70-936d3b59db7d">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{payment}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="270" y="0" width="80" height="30" uuid="835948f8-45ba-4c4a-8306-c6ad70214ba0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{orderDate}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="350" y="0" width="100" height="30" uuid="7bd69460-736a-4f02-b7c6-7aea7f6ae3e4">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="cdbb795a-808b-4e99-8f21-6666b54aa457"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{nextPaymentDate}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="450" y="0" width="100" height="30" uuid="770eaeb3-2c1e-4741-afee-4dc5b6c13058">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="dfbc2e2f-40e6-4809-b2fe-aa7033dbd0ea"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{nextPaymentValue}]]></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>

and produce the following output:

If you don't want repeating values, select all the textfields except the ones containing $F{nextPaymentDate} and $F{nextPaymentValue} and uncheck the "Print Repeated Values" in the Properties Tab

Edit #2:

With a JSON like:

{
    "1786616": {
        "order": "1786616",
        "valueorder": "330.00",
        "payment": "CART\u00c3O DE CR\u00c9DITO",
        "date": "2017-02-13"
    },
    "1786681": {
        "order": "1786681",
        "valueorder": "330.00",
        "payment": "CART\u00c3O CR\u00c9DITO 4C\/ACR\u00c9SCIMO)",
        "date": "2017-02-13",
        "nextpayments": [{
                "date": "13\/02\/2017",
                "value": "R$\u00a082,50"
            },
            {
                "date": "28\/03\/2017",
                "value": "R$\u00a082,50"
            },
            {
                "date": "11\/05\/2017",
                "value": "R$\u00a082,50"
            },
            {
                "date": "22\/06\/2017",
                "value": "R$\u00a082,50"
            }
        ]
    }
}

If you are only interested in orders that have the "nextpayments" key, you could modify the above sample's query from:

<queryString language="jsonql">
    <![CDATA[nextpayments.*]]>
</queryString>

to:

<queryString language="jsonql">
    <![CDATA[.*.nextpayments.*]]>
</queryString>

But if you also want the order that does not have the "nextpayments" key you need to resort to a list-based solution, something similar to the first sample. Here's a sample JRXML:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="Report1" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="16882c94-641b-47b6-bd2f-675edbc1f15a">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>
    <subDataset name="NextPaymentsDateDataset" uuid="5337e69f-16e4-447d-a9e7-fbe6d25a479f">
        <field name="nextPaymentDate" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="date"/>
        </field>
        <field name="nextPaymentValue" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="value"/>
        </field>
    </subDataset>
    <parameter name="jsonString" class="java.lang.String">
        <defaultValueExpression><![CDATA["{\n" +
                "    \"1786616\": {\n" +
                "        \"order\": \"1786616\",\n" +
                "        \"valueorder\": \"330.00\",\n" +
                "        \"payment\": \"CART\\u00c3O DE CR\\u00c9DITO\",\n" +
                "        \"date\": \"2017-02-13\"\n" +
                "    },\n" +
                "    \"1786681\": {\n" +
                "        \"order\": \"1786681\",\n" +
                "        \"valueorder\": \"330.00\",\n" +
                "        \"payment\": \"CART\\u00c3O CR\\u00c9DITO 4C\\/ACR\\u00c9SCIMO)\",\n" +
                "        \"date\": \"2017-02-13\",\n" +
                "        \"nextpayments\": [{\n" +
                "                \"date\": \"13\\/02\\/2017\",\n" +
                "                \"value\": \"R$\\u00a082,50\"\n" +
                "            },\n" +
                "            {\n" +
                "                \"date\": \"28\\/03\\/2017\",\n" +
                "                \"value\": \"R$\\u00a082,50\"\n" +
                "            },\n" +
                "            {\n" +
                "                \"date\": \"11\\/05\\/2017\",\n" +
                "                \"value\": \"R$\\u00a082,50\"\n" +
                "            },\n" +
                "            {\n" +
                "                \"date\": \"22\\/06\\/2017\",\n" +
                "                \"value\": \"R$\\u00a082,50\"\n" +
                "            }\n" +
                "        ]\n" +
                "    }\n" +
                "}"]]></defaultValueExpression>
    </parameter>
    <parameter name="JSON_INPUT_STREAM" class="java.io.InputStream">
        <defaultValueExpression><![CDATA[new java.io.ByteArrayInputStream($P{jsonString}.getBytes("UTF-8"))]]></defaultValueExpression>
    </parameter>
    <queryString language="jsonql">
        <![CDATA[.*]]>
    </queryString>
    <field name="order" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="order"/>
        <fieldDescription><![CDATA[order]]></fieldDescription>
    </field>
    <field name="valueorder" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="valueorder"/>
        <fieldDescription><![CDATA[valueorder]]></fieldDescription>
    </field>
    <field name="payment" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="payment"/>
        <fieldDescription><![CDATA[payment]]></fieldDescription>
    </field>
    <field name="date" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="date"/>
        <fieldDescription><![CDATA[date]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <staticText>
                <reportElement x="180" y="20" width="200" height="30" uuid="33c1adf9-dc58-4d35-a409-72de3d4f0347"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[Variable JSON arrays]]></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="80" height="30" backcolor="#BEFADB" uuid="5258fbf3-575f-486e-bede-26e83ec791a5">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <text><![CDATA[order]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="80" y="0" width="90" height="30" backcolor="#BEFADB" uuid="464a7d52-1801-4d67-9cda-c4b2d2e4526b">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <text><![CDATA[valueorder]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="170" y="0" width="100" height="30" backcolor="#BEFADB" uuid="c179e19a-efa9-4d27-947c-84b14d2639aa">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <text><![CDATA[payment]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="270" y="0" width="80" height="30" backcolor="#BEFADB" uuid="cb1829b9-c782-42a7-a3f4-0126db20cc6a">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[order date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="350" y="0" width="100" height="30" backcolor="#BEFADB" uuid="628c20fa-bbc9-43ea-8aa4-1486bf518e38">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="450" y="0" width="100" height="30" backcolor="#BEFADB" uuid="2b4c49a7-1984-48d4-aa92-9a6d859feda8">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <text><![CDATA[next payment value]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="35" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="80" height="30" uuid="27c8ae6d-def5-4abd-b691-d350fd92c719">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6b5c10f8-8390-4a95-a707-2f267d64349a"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{order}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="90" height="30" uuid="94342e9b-d545-441c-9350-9179e4cc2530">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a223b8db-9803-4257-baf6-cfbc758b6950"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{valueorder}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="170" y="0" width="100" height="30" uuid="8e5286de-d8ac-43a5-bc70-936d3b59db7d">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="166b5682-4aab-4c8a-b7d3-4e7b4be84db6"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{payment}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="270" y="0" width="80" height="30" uuid="835948f8-45ba-4c4a-8306-c6ad70214ba0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="b7a27377-1e06-43c4-afab-840efea597f3"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{date}]]></textFieldExpression>
            </textField>
            <componentElement>
                <reportElement x="350" y="0" width="200" height="30" uuid="89f3fd2c-4be0-466c-9b22-c749e8b69d42"/>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="NextPaymentsDateDataset" uuid="40421d98-9a67-451f-afe3-d835f0deaeaf">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("nextpayments")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="200">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="30" uuid="143efbb1-c95a-49bf-adff-a618b5518c75"/>
                            <textFieldExpression><![CDATA[$F{nextPaymentDate}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="100" y="0" width="100" height="30" uuid="16ce45fb-1631-4e0b-9875-9427f6544c7e"/>
                            <textFieldExpression><![CDATA[$F{nextPaymentValue}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

with this output:

narcism

6492

Joined: Nov 22 2010 - 12:39am

Last seen: 1 hour 31 min ago

Hi, thanks for the answer, this works, but don't work that same I need.

I don't know how to print the JSON in a text field, I put in ther the JSON_INPUT_STREAM.toString(), but don't work, what i did wrong ?

And I need put the results in a xlsx, so I need breack the lines afther the text, one afther anotherone, in php I divide the Json in parts, and this parts are organized, I just need print that JSON in the report, how I can made this ?

gabriel.nadoroski - 4 years 11 months ago

Please add more info to your question with sample data: how your json looks like, how you feed it to your report, what report output you expect

narcism - 4 years 11 months ago

Sorry, in my head this made sense and you understood my problem.
The report do a list of next payment for a purchase was divided in two, three, and more times, I calculate the dates of weekend, I'm studying how I can do the holidays, but this don't come the case
But, this is the JSON
{"ORDER XXXXXXXX":
{"order":"I can't show this",
"valueorder":"100.00",
"formpayment":"CRED CARD",
"date":"I can't show this",
"nextpayments":["23\/12\/2014","04\/02\/2015"],
"valuenextpayment":["R$\u00a0106,67","R$\u00a0106,67"]
},

Well I need print this in xls, I send to the jrxml this values, I'll divide the next payments and value next payments in others two arrays in JSON, I don't made this before because I need more calcules to do that (I will put on the dates of holidays, for now he does just the weekend) I'm studing a form of I can make that, anyway,
I will put in the xls of that form:
Order - Date - Payment - Value - Date next Payment - Value Next Payment

gabriel.nadoroski - 4 years 11 months ago

show 9 more...

Order - Date - Payment - Value - Date next Payment - Value Next Payment
Are columns in xls

gabriel.nadoroski - 4 years 11 months ago

I got the sense of what you are trying to achieve, but without specific info I cannot guide you through a proper solution. What calculations do you perform on your json data? How does "nextpayments" relate to "valuenextpayment". I requested that you edit your initial question with proper information so that others could benefit from this.

narcism - 4 years 11 months ago

Ok, I had not understood, thanks, I will edit

gabriel.nadoroski - 4 years 11 months ago

Thanks, you help me so much, now I just need translate to my native language and make works. LOL

gabriel.nadoroski - 4 years 11 months ago

I made a big mistake, or I'm just stupid, I using the jrxml you give me, it works but I send more than one json, and he just printed one of this jsons, this is a error mine, I didn't say it was more than a one json, I studi the code have you make and is help me, but I don't uderstando how he works completely, I don't know how I send more then one jason same time to him, you can help on this ?
When I put the code for you see, I just printed one code, nothing anymore, this is a big error mine, I send to you the correct json to see.
I will edit the post or make a new if necessary
this is the json:

{"1786616":{
"order":"1786616",
"valueorder":"330.00",
"payment":"CART\u00c3O DE CR\u00c9DITO",
"date":"2017-02-13"},
"1786681":{
"order":"1786681",
"valueorder":"330.00",
"payment":"CART\u00c3O CR\u00c9DITO 4C\/ACR\u00c9SCIMO)",
"date":"2017-02-13",
"nextpayments":[
{"date":"13\/02\/2017",
"value":"R$\u00a082,50"},
{"date":"28\/03\/2017",
"value":"R$\u00a082,50"},
{"date":"11\/05\/2017",
"value":"R$\u00a082,50"},
{"date":"22\/06\/2017",
"value":"R$\u00a082,50"}
]
}

gabriel.nadoroski - 4 years 11 months ago

See Edit #2 for all possible solutions

narcism - 4 years 11 months ago

Hi, I found a solution, I made some changes, I will make a post explaining everything and helping anyone that need
thanks!

gabriel.nadoroski - 4 years 11 months ago

Hi, I need put some jar in application to the java.io.ByteArrayInputStream?

gabriel.nadoroski - 4 years 10 months ago

Thanks it works well for me.

I did little change. Reading dinamically JSON types parameters in .jrxml

You can download the examples Unrelated_JSON_arrays, Restructured_JSON here:

git clone https://github.com/fatandazdba/parameterJsonUrl_JasperServer/tree/develop

img: https://raw.githubusercontent.com/fatandazdba/parameterJsonUrl_JasperSer...

fatandaz - 2 years 3 months ago

add comment

Main menu

Printed Json Values

1 Answer:

Tags

Main menu

You are here

Printed Json Values

1 Answer:

Tags