jsonjasper-reports
Jaspersoft jsonql iterate over list to get next element
I am using jaspersoft studio 6.6.0 for my report, and i have json as a datasource for my report, in my report there is a field to display next port name based on condition of current port, but i am not able to display that, my json is as like this
"routingLocs": [
{
"callOrder": 1,
"locCode": "ZWTHJ",
"isCurrentLoc": "N"
},
{
"callOrder": 2,
"locCode": "TRYAR",
"isCurrentLoc": "Y"
},
{
"callOrder": 3,
"locCode": "AUABP",
"isCurrentLoc": "N"
},
{
"callOrder": 4,
"locCode": "RAJPI",
"isCurrentLoc": "N"
}
]
now i have to display locCode of object which is next to object that has isCurrentLoc = "Y", for this i have created one field with expression as
<![CDATA[routingLocs..locCode(^.isCurrentLoc == "Y")]]>
This expression is printing "TRYAR" instead of "AUABP"
My field definition in JRXML to display the value is
<field name="nextPort" class="java.lang.String">
<property name="net.sf.jasperreports.jsonql.field.expression">
<![CDATA[routingLocs..locCode(^.isCurrentLoc == "Y")]]>
</property>
<fieldDescription><![CDATA[nextPort]]></fieldDescription>
</field>
please help me to fix this expression
Thanks in advance for any kind of support
Solution
You cannot land on a sibling element with a jsonql query, but you can filter the JSON in a separate subDataset to achieve what you want.
Provided there is some order in your JSON data, like the ascending order of the callOrder, you can start creating your main dataset with your filtering query, something like:
<queryString language="jsonql">
<![CDATA[routingLocs.*(isCurrentLoc == "Y")]]>
</queryString>
Then create a subDataset that accepts a MAIN_CALL_ORDER parameter and that will use the same JSON data that your main does:
<subDataset name="nextPortDataset" uuid="a8b8a64f-4c6b-4c28-91db-e10f28cef52c">
<property name="net.sf.jasperreports.data.adapter" value="./DataAdapter.xml"/>
<parameter name="MAIN_CALL_ORDER" class="java.lang.Integer"/>
<queryString language="jsonql">
<![CDATA[routingLocs.*(callOrder > $P{MAIN_CALL_ORDER})[0]]]>
</queryString>
<field name="locCode" class="java.lang.String">
<property name="net.sf.jasperreports.json.field.expression" value="locCode"/>
<fieldDescription><![CDATA[locCode]]></fieldDescription>
</field>
</subDataset>
Here, the query will produce the first item that has the callOrder greater than the one sent from the main dataset.
Then this dataset should be linked to a list that will be passed the parameter like so:
<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="nextPortDataset" uuid="55c7173b-0b04-4d4f-bd80-48cfe1ef620c">
<datasetParameter name="MAIN_CALL_ORDER">
<datasetParameterExpression><![CDATA[$F{mainCallOrder}]]></datasetParameterExpression>
</datasetParameter>
</datasetRun>
<jr:listContents height="30" width="460">
<textField>
<reportElement x="230" y="0" width="115" height="30" uuid="213ab052-9cdc-419a-8d3d-45db3bf6ef90"/>
<textFieldExpression><![CDATA[$F{locCode}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
A basic working JRXML would look like this:
<?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="Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e9573346-c7d0-4b7a-a838-d2ff5b7b9512">
<property name="net.sf.jasperreports.data.adapter" value="./DataAdapter.xml"/>
<subDataset name="nextPortDataset" uuid="a8b8a64f-4c6b-4c28-91db-e10f28cef52c">
<property name="net.sf.jasperreports.data.adapter" value="./DataAdapter.xml"/>
<parameter name="MAIN_CALL_ORDER" class="java.lang.Integer"/>
<queryString language="jsonql">
<![CDATA[routingLocs.*(callOrder > $P{MAIN_CALL_ORDER})[0]]]>
</queryString>
<field name="callOrder" class="java.lang.Integer">
<property name="net.sf.jasperreports.json.field.expression" value="callOrder"/>
<fieldDescription><![CDATA[callOrder]]></fieldDescription>
</field>
<field name="locCode" class="java.lang.String">
<property name="net.sf.jasperreports.json.field.expression" value="locCode"/>
<fieldDescription><![CDATA[locCode]]></fieldDescription>
</field>
<field name="isCurrentLoc" class="java.lang.String">
<property name="net.sf.jasperreports.json.field.expression" value="isCurrentLoc"/>
<fieldDescription><![CDATA[isCurrentLoc]]></fieldDescription>
</field>
</subDataset>
<queryString language="jsonql">
<![CDATA[routingLocs.*(isCurrentLoc == "Y")]]>
</queryString>
<field name="mainCallOrder" class="java.lang.Integer">
<property name="net.sf.jasperreports.jsonql.field.expression" value="callOrder"/>
<fieldDescription><![CDATA[mainCallOrder]]></fieldDescription>
</field>
<field name="mainLocCode" class="java.lang.String">
<property name="net.sf.jasperreports.jsonql.field.expression" value="locCode"/>
<fieldDescription><![CDATA[mainLocCode]]></fieldDescription>
</field>
<field name="mainIsCurrentLoc" class="java.lang.String">
<property name="net.sf.jasperreports.jsonql.field.expression" value="isCurrentLoc"/>
<fieldDescription><![CDATA[mainIsCurrentLoc]]></fieldDescription>
</field>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="79" splitType="Stretch"/>
</title>
<pageHeader>
<band height="35" splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band height="32" splitType="Stretch">
<staticText>
<reportElement x="115" y="0" width="115" height="30" uuid="4ec73015-1c44-441c-a80e-bff448923a29">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bff05bbe-0c73-4e55-adec-461fd0f2ebff"/>
</reportElement>
<text><![CDATA[callOrder]]></text>
</staticText>
<staticText>
<reportElement x="230" y="0" width="115" height="30" uuid="018825e6-aa24-4a11-85a4-760fa6cf913c">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="cc7977d7-a541-4d3b-81d8-97be3451e130"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<text><![CDATA[locCode]]></text>
</staticText>
<staticText>
<reportElement x="345" y="0" width="115" height="30" uuid="16e13486-0a34-4c65-b512-00a4678c65f7">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="82e0773c-3e7d-4372-a44c-93a4bfcfd6b0"/>
</reportElement>
<text><![CDATA[isCurrentLoc]]></text>
</staticText>
<staticText>
<reportElement x="0" y="2" width="60" height="30" uuid="ad4e4685-2696-472c-a3b6-4a9961aeb6dc">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bff05bbe-0c73-4e55-adec-461fd0f2ebff"/>
</reportElement>
<text><![CDATA[Main Dataset]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="110" splitType="Stretch">
<textField>
<reportElement x="115" y="0" width="115" height="30" uuid="3bffc0f6-fb80-443a-b06d-ebbe2b669388">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bff05bbe-0c73-4e55-adec-461fd0f2ebff"/>
</reportElement>
<textFieldExpression><![CDATA[$F{mainCallOrder}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="230" y="0" width="115" height="30" uuid="1267f406-8492-4dd1-859a-a37f24ac545a">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="cc7977d7-a541-4d3b-81d8-97be3451e130"/>
</reportElement>
<textFieldExpression><![CDATA[$F{mainLocCode}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="345" y="0" width="115" height="30" uuid="522f3a23-a2b4-443d-9ca5-9abbd54a07df">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="82e0773c-3e7d-4372-a44c-93a4bfcfd6b0"/>
</reportElement>
<textFieldExpression><![CDATA[$F{mainIsCurrentLoc}]]></textFieldExpression>
</textField>
<componentElement>
<reportElement x="0" y="80" width="560" height="30" uuid="859af22b-90ec-41fb-acf0-36d4acf2b90a"/>
<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="nextPortDataset" uuid="55c7173b-0b04-4d4f-bd80-48cfe1ef620c">
<datasetParameter name="MAIN_CALL_ORDER">
<datasetParameterExpression><![CDATA[$F{mainCallOrder}]]></datasetParameterExpression>
</datasetParameter>
</datasetRun>
<jr:listContents height="30" width="560">
<staticText>
<reportElement x="0" y="0" width="100" height="30" uuid="6db9b791-aaac-45e9-af0f-34546254b9e9"/>
<text><![CDATA[Filtering List]]></text>
</staticText>
<textField>
<reportElement x="115" y="0" width="115" height="30" uuid="ee03e48b-38dd-4814-ad7d-2549810c0991">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
</reportElement>
<textFieldExpression><![CDATA[$F{callOrder}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="230" y="0" width="115" height="30" uuid="213ab052-9cdc-419a-8d3d-45db3bf6ef90"/>
<textFieldExpression><![CDATA[$F{locCode}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="345" y="0" width="115" height="30" uuid="a8ea32ea-150f-4d1f-8945-18c2b287c157"/>
<textFieldExpression><![CDATA[$F{isCurrentLoc}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
</band>
</detail>
</jasperReport>
and produce the following output:
This sample needs a file-based data adapter that is linked to both main dataset and subDataset with this property:
<property name="net.sf.jasperreports.data.adapter" value="./DataAdapter.xml"/>
If you don't have a file-based data adapter, you could create one and export it to file from the Repository Explorer inside Jaspersoft Studio.
- 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?
- Best way to store JSON in an HTML attribute?
- Zend2 read all JSON files content and return results to view
- Lowercase JSON key names with JSON Marshal in Go
- Does anyone know of a good JSON time server?
- How to parse json response for ajax request?
- Parse ajax json response
- How to remove mysterious "default" field added to JSON?
- Convert json data to a html table
- Parse Aws IoT message in python
- Laravel won't obey status code
- PHP "pretty print" json_encode
- Python: Error while reading and replacing String(with special characters) from file
- Oracle JSON_OBJECT how to have a missing key
- How to check non-constant array
- pdf2json Page Unit: What is it?
- Jest - TypeError: response.json is not a function
- Flutter: JSON response incomplete
- How to format JSON PubSub message against Avro schema with "map" type field
- Reading csv file converts it to json but it takes every row in it as a string in karate
- Formatting for JSON lite
- Protobuf to json array with no key for repeated objects