Need to set json data having list of list to show in table based on rowspan using jasper soft

[vakhariaaman]

Hi Team,

I am facing difficulty in creating the below solution usinf json data file. Please see below.

{
   "data":[
      {
         "jobRole:"Assistant Manager",
         "programmes":[
            {
               "programe":"Accountancy",
               "provider":"SIM University"
            },
            {
               "programe":"MBA",
               "provider":"Symboisis"
            },
            {
               "programe":"BA",
               "provider":"SMIT"
            }
         ]
      },
      {
         "jobRole:"Regional Manager",
         "programmes":[
            {
               "programe":"BTECH",
               "provider":"SIM University"
            },
            {
               "programe":"ARTS",
               "provider":"SD Bansal"
            }
         ]
      }
   ]
}

I have a json which contain list of jobroles and under each jobrole their is programmes list which contain programe and provider. I have to display it into the table using jasper soft. The reultant table should in below format.

<pre title="Double click to select all"><table class="tg"> <tr> <th class="tg-l711">Job Role</th> <th class="tg-l711">Programe</th> <th class="tg-l711">Provider</th> </tr> <tr> <td class="tg-l711" rowspan="3">Assistant Manager</td> <td class="tg-l711">Accountancy</td> <td class="tg-l711">SIM University</td> </tr> <tr> <td class="tg-l711">MBA</td> <td class="tg-l711">Symboisis</td> </tr> <tr> <td class="tg-l711">BA</td> <td class="tg-l711">SMIT</td> </tr> <tr> <td class="tg-us36" rowspan="2">Regional Manager</td> <td class="tg-us36">BTECH</td> <td class="tg-us36">SIM University</td> </tr> <tr> <td class="tg-us36">ARTS</td> <td class="tg-us36">SD Bansal</td> </tr></table></pre>

Like if job role has programmes list count 3 then job role column rowspan should be 3.

Please help how to use json data in tavble in jasper soft if json conatins list of list and we want to create table structur as I shown above.

[narcism]

In a very basic way you can achieve what you want like so:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.5.1.final using JasperReports Library version 6.5.1  -->
<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="JSONQL_no_table" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="98c9b141-c0bd-4f9f-a8d6-b9b3caaf615d">
    <queryString language="jsonql">
        <![CDATA[data.programmes.*]]>
    </queryString>
    <field name="JobRole" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^^jobRole"/>
    </field>
    <field name="Programe" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="programe"/>
    </field>
    <field name="Provider" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="provider"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </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="#D2FAF6" uuid="d82ddd57-6ddd-470f-b75a-3c7e02701dc7">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="90cf53a0-a0d3-4bfe-bf2b-bd79c533db73"/>
                </reportElement>
                <text><![CDATA[JobRole]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="185" y="0" width="185" height="30" backcolor="#D2FAF6" uuid="a7760d02-ea94-446c-a42a-49da6f2446e5">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="444f9be5-2968-463d-b008-68d4fb3d5317"/>
                </reportElement>
                <text><![CDATA[Programe]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="370" y="0" width="185" height="30" backcolor="#D2FAF6" uuid="0064f2ea-7529-4772-a3e7-83258c146ae9">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="c06960f0-e4bf-481e-9050-bdacd95bf764"/>
                </reportElement>
                <text><![CDATA[Provider]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="185" y="0" width="185" height="30" uuid="f9705c9f-75c8-476e-aae6-66655b790479">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="444f9be5-2968-463d-b008-68d4fb3d5317"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{Programe}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="370" y="0" width="185" height="30" uuid="37ca4922-b0fc-4588-8905-997e4fae4471">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="c06960f0-e4bf-481e-9050-bdacd95bf764"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{Provider}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="185" height="30" uuid="b2e61cda-d53c-4d7a-a123-3233548528a1">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="90cf53a0-a0d3-4bfe-bf2b-bd79c533db73"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{JobRole}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band height="54" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>