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,
1 Answer:
Posted on September 28, 2016 at 1:19am
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>
Please give more details regarding what you want to achieve. Sample JSON data and JRXML template might come in handy.
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,
I need to see the JRXML in order to tell what is wrong.
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>
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.
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.