Issue with subDataset and Charts with JSON

0

Hi All,

I am new to JasperReports Studio, can anyone of you help me with creating charts with the JSON data(simple and nested JSONs)? I have tried several solution posted on the community but not able to figure out the issue with this.

The Subdataset is showing the data in the Preview but not able to show the data in the table. Any documentation or tutorial for working with JSON and table/Charts will also help.

Thanks,

 

 

bhuwanesh2000's picture
Joined: Sep 23 2016 - 6:24am
Last seen: 2 years 10 months ago

Please give more details regarding what you want to achieve. Sample JSON data and JRXML template might come in handy.

narcism - 3 years 2 months ago

Hi Narcism,

 

I have a JSON file which somewhat looks like -

{

"companyDetails":[{

"CompanyName":"Company1",

"companyEmployeeDistribution": [{
            "financialYear": "2014",
            "HR": "10",
            "Admin": "10",
            "Finance": "5",
            "Sales": "40",
            "IT": "20",
           "FrontOffice": "5",
          "Others":"10"

       }]

}}

}

What I need to show the "companyEmployeeDistribution" in a Pie chart, All the values apart from financialYear is in percentage.

I have tried to create a subdataset which is showing data in the data preview, but when i attach the dataset with the chart and check the preview, tunfortunetly , there is no data in the page. I think doing some obvious mistake. Can you please guide me how can i achieve this.

 

Thanks,

 

bhuwanesh2000 - 3 years 2 months ago

I need to see the JRXML in order to tell what is wrong.

narcism - 3 years 2 months ago
show 3 more...

Hi,

 

Please find the JRXML as below-

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2016-09-28T12:47:09 -->
<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="Blank_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="cb8a2c5a-f2fc-4232-9322-1451c19b32fc">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSONAdapter"/>
    <subDataset name="Dataset1" uuid="5e6e2007-e1bc-40ca-a829-e68dedf94d22">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSONAdapter"/>
        <queryString language="json">
            <![CDATA[companyDetails.companyEmployeeDistribution]]>
        </queryString>
        <field name="financialYear" class="java.lang.String">
            <fieldDescription><![CDATA[financialYear]]></fieldDescription>
        </field>
        <field name="HR" class="java.lang.String">
            <fieldDescription><![CDATA[HR]]></fieldDescription>
        </field>
        <field name="Admin" class="java.lang.String">
            <fieldDescription><![CDATA[Admin]]></fieldDescription>
        </field>
        <field name="Finance" class="java.lang.String">
            <fieldDescription><![CDATA[Finance]]></fieldDescription>
        </field>
        <field name="Sales" class="java.lang.String">
            <fieldDescription><![CDATA[Sales]]></fieldDescription>
        </field>
        <field name="IT" class="java.lang.String">
            <fieldDescription><![CDATA[IT]]></fieldDescription>
        </field>
        <field name="FrontOffice" class="java.lang.String">
            <fieldDescription><![CDATA[FrontOffice]]></fieldDescription>
        </field>
        <field name="Others" class="java.lang.String">
            <fieldDescription><![CDATA[Others]]></fieldDescription>
        </field>
        
    </subDataset>
    <queryString language="json">
        <![CDATA[companyDetails]]>
    </queryString>
    <field name="companyEmployeeDistribution" class="java.lang.String">
        <fieldDescription><![CDATA[companyEmployeeDistribution]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="284" splitType="Stretch">
            <pieChart>
                <chart evaluationTime="Report">
                    <reportElement x="160" y="50" width="200" height="200" uuid="0b74a10a-8dc4-4234-a3e2-009aa36584b7"/>
                    <chartTitle/>
                    <chartSubtitle/>
                    <chartLegend/>
                </chart>
                <pieDataset>
                    <dataset>
                        <datasetRun subDataset="Dataset1" uuid="c564ca72-af83-4825-a67a-75eaba3be280">
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{HR}]]></keyExpression>
                        <valueExpression><![CDATA[$F{HR}]]></valueExpression>
                    </pieSeries>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{Admin}]]></keyExpression>
                        <valueExpression><![CDATA[$F{Admin}]]></valueExpression>
                    </pieSeries>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{Finance}]]></keyExpression>
                        <valueExpression><![CDATA[$F{Finance}]]></valueExpression>
                    </pieSeries>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{Sales}]]></keyExpression>
                        <valueExpression><![CDATA[$F{Sales}]]></valueExpression>
                    </pieSeries>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{IT}]]></keyExpression>
                        <valueExpression><![CDATA[$F{IT}]]></valueExpression>
                    </pieSeries>
                    <pieSeries>
                        <keyExpression><![CDATA[$F{FrontOffice}]]></keyExpression>
                        <valueExpression><![CDATA[$F{FrontOffice}]]></valueExpression>
                    </pieSeries>
                </pieDataset>
                <piePlot>
                    <plot/>
                    <itemLabel/>
                </piePlot>
            </pieChart>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="61" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>
 


 

bhuwanesh2000 - 3 years 2 months ago

Thanks Narcism,

 

That was a great help.  Is this the same way we can populate the tables also? Coz they are also giving me the same issue.

Thanks again.

 

bhuwanesh2000 - 3 years 2 months ago

Right now, yes, by the means of a subDataset to access the nested array elements.

However, things are going to be simplified with the JasperReports v6.3.0 release when we will be introducing a new JSON query language(JSONQL) to overcome some of the issues of the actual JSON language.

narcism - 3 years 2 months ago

1 Answer:

1

Some notes:

  • Accessing the nested JSON should be done by subDatasource-ing the current node from the mainDataset
  • pie series need to have unique keys

The corrected JRXML:

<?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="Blank_A4" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="cb8a2c5a-f2fc-4232-9322-1451c19b32fc">
  <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSONAdapter"/>
  <subDataset name="Dataset1" uuid="5e6e2007-e1bc-40ca-a829-e68dedf94d22">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSONAdapter"/>
    <field name="financialYear" class="java.lang.String">
      <fieldDescription><![CDATA[financialYear]]></fieldDescription>
    </field>
    <field name="HR" class="java.lang.Double">
      <fieldDescription><![CDATA[HR]]></fieldDescription>
    </field>
    <field name="Admin" class="java.lang.Double">
      <fieldDescription><![CDATA[Admin]]></fieldDescription>
    </field>
    <field name="Finance" class="java.lang.Double">
      <fieldDescription><![CDATA[Finance]]></fieldDescription>
    </field>
    <field name="Sales" class="java.lang.Double">
      <fieldDescription><![CDATA[Sales]]></fieldDescription>
    </field>
    <field name="IT" class="java.lang.Double">
      <fieldDescription><![CDATA[IT]]></fieldDescription>
    </field>
    <field name="FrontOffice" class="java.lang.Double">
      <fieldDescription><![CDATA[FrontOffice]]></fieldDescription>
    </field>
    <field name="Others" class="java.lang.Double">
      <fieldDescription><![CDATA[Others]]></fieldDescription>
    </field>
  </subDataset>
  <queryString language="json">
    <![CDATA[companyDetails]]>
  </queryString>
  <background>
    <band splitType="Stretch"/>
  </background>
  <title>
    <band height="284" splitType="Stretch">
      <pieChart>
        <chart evaluationTime="Report">
          <reportElement x="60" y="40" width="460" height="230" uuid="2d51672a-3c03-4a78-bf57-c743cae11c27"/>
          <chartTitle/>
          <chartSubtitle/>
          <chartLegend/>
        </chart>
        <pieDataset>
          <dataset>
            <datasetRun subDataset="Dataset1" uuid="8fb957a1-b20d-4794-9bf4-15955502c45d">
              <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("companyEmployeeDistribution")]]></dataSourceExpression>
            </datasetRun>
          </dataset>
          <pieSeries>
            <keyExpression><![CDATA["HR"]]></keyExpression>
            <valueExpression><![CDATA[$F{HR}]]></valueExpression>
          </pieSeries>
          <pieSeries>
            <keyExpression><![CDATA["Admin"]]></keyExpression>
            <valueExpression><![CDATA[$F{Admin}]]></valueExpression>
          </pieSeries>
          <pieSeries>
            <keyExpression><![CDATA["Finance"]]></keyExpression>
            <valueExpression><![CDATA[$F{Finance}]]></valueExpression>
          </pieSeries>
          <pieSeries>
            <keyExpression><![CDATA["Sales"]]></keyExpression>
            <valueExpression><![CDATA[$F{Sales}]]></valueExpression>
          </pieSeries>
          <pieSeries>
            <keyExpression><![CDATA["IT"]]></keyExpression>
            <valueExpression><![CDATA[$F{IT}]]></valueExpression>
          </pieSeries>
          <pieSeries>
            <keyExpression><![CDATA["FrontOffice"]]></keyExpression>
            <valueExpression><![CDATA[$F{FrontOffice}]]></valueExpression>
          </pieSeries>
        </pieDataset>
        <piePlot labelFormat="{0} {2}" legendLabelFormat="{0} {1}">
          <plot/>
          <itemLabel color="#000000" backgroundColor="#FFFFFF"/>
        </piePlot>
      </pieChart>
    </band>
  </title>
  <pageHeader>
    <band height="35" splitType="Stretch"/>
  </pageHeader>
  <columnHeader>
    <band height="61" splitType="Stretch"/>
  </columnHeader>
  <detail>
    <band height="125" splitType="Stretch"/>
  </detail>
  <columnFooter>
    <band height="45" splitType="Stretch"/>
  </columnFooter>
  <pageFooter>
    <band height="54" splitType="Stretch"/>
  </pageFooter>
  <summary>
    <band height="42" splitType="Stretch"/>
  </summary>
</jasperReport>
narcism's picture
1096
Joined: Nov 22 2010 - 12:39am
Last seen: 7 hours 5 min ago
Feedback
randomness