How to fill table with subdataset json ?

Posted on April 11, 2017 at 7:14am

HI everyone,  I hope that you understand me.

I have looked for how to work with json but there are no examples that guide me. I need to create a table with this json:

{
  "report": {
    "fecha": "15/02/2017-21/02/2017",
    "periodo": "12",
    "cod": "12",
    "Datos": {
      "baseDatosItem": [
        {
          "baseDatos": "first",
          "variacionSin": {
            "variacion": "458",
            "variacionPorcentaje": "82.08"
          },
          "variacionPositiva": {
            "variacion": "47",
            "variacionPorcentaje": "8.42"
          },
          "variacionNegativa": {
            "variacion": "53",
            "variacionPorcentaje": "9.50"
          }
        },
        {
          "baseDatos": "second",
          "variacionSin": {
            "variacion": "486",
            "variacionPorcentaje": "87.10"
          },
          "variacionPositiva": {
            "variacion": "30",
            "variacionPorcentaje": "5.38"
          },
          "variacionNegativa": {
            "variacion": "39",
            "variacionPorcentaje": "6.96"
          }
        },
        {
          "baseDatos": "Third",
          "variacionSin": {
            "variacion": "557",
            "variacionPorcentaje": "100.00"
          },
          "variacionPositiva": {
            "variacion": "0",
            "variacionPorcentaje": "0.00"
          },
          "variacionNegativa": {
            "variacion": "0",
            "variacionPorcentaje": "0.00"
          }
        }
      ]
    }
  }
}

I need that:

fecha :            |periodo:                |cod:             |

------------------------------------------------------------

base de datos|  sinVariacion | variacionPositiva |

first                     458 - 82.5        47 - 8.42

second              486 - 87.10        etc

Third                         etc              etc

 

please you help, thanks!

Jaspersoft® Studio
asd.backup's picture
asd.backup
108
Joined: Mar 21 2017 - 7:53am
Last seen: 8 months 3 weeks ago

Do you want "fecha", "periodo" and "cod" to be columns along with "base de datos", "sinVariacion" and "variacionPositiva"? Please post a complete fragment with the desired output. This would help us better understand what you want to achieve

narcism - 6 years 5 months ago

I want to "fecha", "periodo", "cod" in the header. the others are the report detail. I had a problem with the connection or configuration in the element "table". specifically in "show properties - Dataset". I had chosen "Use same jdbc connection used to fill the master report" but the solution (In this case with the format Json), I choose "Use a JRDDatasource expression" and I wrote the following: 

((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("RootNode.Array")

I've been watching this case all day for Json, since the configuration with XML is different.

I hope that this solution will serve an others people.

Regards and thanks for answering!

asd.backup - 6 years 5 months ago
add comment

1 Answer:

Posted on April 12, 2017 at 7:52am

This is one way to do it:

With current JSON language:

<?xml version="1.0" encoding="UTF-8"?>

<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2017-04-12T17:46:12 -->
<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="b77d3e27-0685-44d2-979e-24e002daa629">
  <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
    <box>
      <pen lineWidth="0.5" lineColor="#000000"/>
      <topPen lineWidth="0.5" lineColor="#000000"/>
      <leftPen lineWidth="0.5" lineColor="#000000"/>
      <bottomPen lineWidth="0.5" lineColor="#000000"/>
      <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
  </style>
  <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
    <box>
      <pen lineWidth="0.5" lineColor="#000000"/>
      <topPen lineWidth="0.5" lineColor="#000000"/>
      <leftPen lineWidth="0.5" lineColor="#000000"/>
      <bottomPen lineWidth="0.5" lineColor="#000000"/>
      <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
  </style>
  <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
    <box>
      <pen lineWidth="0.5" lineColor="#000000"/>
      <topPen lineWidth="0.5" lineColor="#000000"/>
      <leftPen lineWidth="0.5" lineColor="#000000"/>
      <bottomPen lineWidth="0.5" lineColor="#000000"/>
      <rightPen lineWidth="0.5" lineColor="#000000"/>
    </box>
  </style>
  <subDataset name="TableDS" uuid="1701ce05-88ab-4435-85c5-3ad38e0b941b">
    <parameter name="fecha" class="java.lang.String">
      <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="periodo" class="java.lang.String">
      <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <parameter name="cod" class="java.lang.String">
      <parameterDescription><![CDATA[]]></parameterDescription>
    </parameter>
    <field name="baseDatos" class="java.lang.String">
      <property name="net.sf.jasperreports.json.field.expression" value="baseDatos"/>
    </field>
  </subDataset>
  <subDataset name="Variacion" uuid="bd0a274a-9331-446a-9cb5-08cc09ab4afa">
    <field name="variacion" class="java.lang.String">
      <property name="net.sf.jasperreports.json.field.expression" value="variacion"/>
    </field>
    <field name="variacionPorcentaje" class="java.lang.String">
      <property name="net.sf.jasperreports.json.field.expression" value="variacionPorcentaje"/>
    </field>
  </subDataset>
  <queryString language="json">
    <![CDATA[report]]>
  </queryString>
  <field name="fecha" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="fecha"/>
    <fieldDescription><![CDATA[fecha]]></fieldDescription>
  </field>
  <field name="periodo" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="periodo"/>
    <fieldDescription><![CDATA[periodo]]></fieldDescription>
  </field>
  <field name="cod" class="java.lang.String">
    <property name="net.sf.jasperreports.json.field.expression" value="cod"/>
    <fieldDescription><![CDATA[cod]]></fieldDescription>
  </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"/>
  </columnHeader>
  <detail>
    <band height="90" splitType="Stretch">
      <componentElement>
        <reportElement x="0" y="0" width="510" height="90" uuid="7ba50af3-d61a-4e0a-8890-0f8434bb2eda">
          <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
          <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
          <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
          <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
        </reportElement>
          <datasetRun subDataset="TableDS" uuid="8d9fd1f1-0d8f-40bf-9120-f0b34202ccba">
            <datasetParameter name="fecha">
              <datasetParameterExpression><![CDATA[$F{fecha}]]></datasetParameterExpression>
            </datasetParameter>
            <datasetParameter name="periodo">
              <datasetParameterExpression><![CDATA[$F{periodo}]]></datasetParameterExpression>
            </datasetParameter>
            <datasetParameter name="cod">
              <datasetParameterExpression><![CDATA[$F{cod}]]></datasetParameterExpression>
            </datasetParameter>
            <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("Datos.baseDatosItem")]]></dataSourceExpression>
          </datasetRun>
          <jr:column width="190" uuid="6ae8fd3f-e502-465f-9d66-1a6d561812c6">
            <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>
            <jr:tableHeader style="Table_TH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="50" height="30" uuid="7789d8b2-b87a-4d4c-9d0e-0f062957ba76"/>
                <text><![CDATA[Fecha:]]></text>
              </staticText>
              <textField>
                <reportElement x="50" y="0" width="140" height="30" uuid="3e2bbfcc-3a67-4e38-a345-e9b768b9b4d9">
                  <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$P{fecha}]]></textFieldExpression>
              </textField>
            </jr:tableHeader>
            <jr:columnHeader style="Table_CH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="190" height="30" uuid="167fde39-59c1-4c8c-ab22-3af32ef84886"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Base de datos]]></text>
              </staticText>
            </jr:columnHeader>
            <jr:detailCell style="Table_TD" height="30">
              <textField>
                <reportElement x="0" y="0" width="190" height="30" uuid="1e9c4826-daee-4230-b502-9018d60771ec"/>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{baseDatos}]]></textFieldExpression>
              </textField>
            </jr:detailCell>
          </jr:column>
          <jr:column width="130" uuid="31d06a8f-cf78-4095-b8ea-36f3f24bb042">
            <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column2"/>
            <jr:tableHeader style="Table_TH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="50" height="30" uuid="47b829d8-ea31-4fae-bd6c-04524ade2f17">
                  <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <text><![CDATA[Periodo:]]></text>
              </staticText>
              <textField>
                <reportElement x="50" y="0" width="80" height="30" uuid="3e2bbfcc-3a67-4e38-a345-e9b768b9b4d9">
                  <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$P{periodo}]]></textFieldExpression>
              </textField>
            </jr:tableHeader>
            <jr:columnHeader style="Table_CH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="130" height="30" uuid="b64ee20e-b949-4046-b3f6-3df2151d14d9"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Variacion Sin]]></text>
              </staticText>
            </jr:columnHeader>
            <jr:detailCell style="Table_TD" height="30">
              <componentElement>
                <reportElement x="0" y="0" width="130" height="30" uuid="91c1cccc-5630-4d88-bb93-dd4bd8134e41"/>
                <jr:list printOrder="Vertical">
                  <datasetRun subDataset="Variacion" uuid="ccc4d8b8-59c8-4c15-9e8c-d81d3bed45b3">
                    <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("variacionSin")]]></dataSourceExpression>
                  </datasetRun>
                  <jr:listContents height="30" width="130">
                    <textField>
                      <reportElement x="0" y="0" width="130" height="30" uuid="c2f18909-9f0c-4c15-b652-e5f3d1247058"/>
                      <textElement verticalAlignment="Middle"/>
                      <textFieldExpression><![CDATA[$F{variacion} + " - " + $F{variacionPorcentaje}]]></textFieldExpression>
                    </textField>
                  </jr:listContents>
                </jr:list>
              </componentElement>
            </jr:detailCell>
          </jr:column>
          <jr:column width="180" uuid="bdea3ff1-ff9d-42df-8b09-c65c0dbd81c2">
            <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column3"/>
            <jr:tableHeader style="Table_TH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="50" height="30" uuid="47b829d8-ea31-4fae-bd6c-04524ade2f17">
                  <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <text><![CDATA[Cod:]]></text>
              </staticText>
              <textField>
                <reportElement x="50" y="0" width="130" height="30" uuid="3e2bbfcc-3a67-4e38-a345-e9b768b9b4d9">
                  <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$P{cod}]]></textFieldExpression>
              </textField>
            </jr:tableHeader>
            <jr:columnHeader style="Table_CH" height="30" rowSpan="1">
              <staticText>
                <reportElement x="0" y="0" width="180" height="30" uuid="526fb279-92a8-43e9-90dc-7b111418bee1"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Variacion Positiva]]></text>
              </staticText>
            </jr:columnHeader>
            <jr:detailCell style="Table_TD" height="30">
              <componentElement>
                <reportElement x="0" y="0" width="180" height="30" uuid="91c1cccc-5630-4d88-bb93-dd4bd8134e41"/>
                <jr:list printOrder="Vertical">
                  <datasetRun subDataset="Variacion" uuid="ccc4d8b8-59c8-4c15-9e8c-d81d3bed45b3">
                    <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("variacionPositiva")]]></dataSourceExpression>
                  </datasetRun>
                  <jr:listContents height="30" width="180">
                    <textField>
                      <reportElement x="0" y="0" width="180" height="30" uuid="c2f18909-9f0c-4c15-b652-e5f3d1247058"/>
                      <textElement verticalAlignment="Middle"/>
                      <textFieldExpression><![CDATA[$F{variacion} + " - " + $F{variacionPorcentaje}]]></textFieldExpression>
                    </textField>
                  </jr:listContents>
                </jr:list>
              </componentElement>
            </jr:detailCell>
          </jr:column>
        </jr:table>
      </componentElement>
    </band>
  </detail>
</jasperReport>
With the newer JSONQL language(but simpler, without the table):
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2017-04-12T18:25:18 -->
<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="b77d3e27-0685-44d2-979e-24e002daa629">
  <queryString language="jsonql">
    <![CDATA[report.Datos.baseDatosItem.*]]>
  </queryString>
  <field name="fecha" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="$.report.fecha"/>
  </field>
  <field name="periodo" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="$.report.periodo"/>
  </field>
  <field name="cod" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="$.report.cod"/>
  </field>
  <field name="baseDatos" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="baseDatos"/>
  </field>
  <field name="variacionSin_variacion" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionSin.variacion"/>
  </field>
  <field name="variacionSin_variacionPorcentaje" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionSin.variacionPorcentaje"/>
  </field>
  <field name="variacionPositiva_variacion" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionPositiva.variacion"/>
  </field>
  <field name="variacionPositiva_variacionPorcentaje" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionPositiva.variacionPorcentaje"/>
  </field>
  <field name="variacionNegativa_variacion" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionNegativa.variacion"/>
  </field>
  <field name="variacionNegativa_variacionPorcentaje" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="variacionNegativa.variacionPorcentaje"/>
  </field>
  <background>
    <band splitType="Stretch"/>
  </background>
  <title>
    <band height="39" splitType="Stretch"/>
  </title>
  <pageHeader>
    <band height="90" splitType="Stretch">
      <textField>
        <reportElement x="60" y="0" width="100" height="20" uuid="44f3eed3-ff23-4b64-95ec-b0834d342d78"/>
        <textFieldExpression><![CDATA[$F{fecha}]]></textFieldExpression>
      </textField>
      <staticText>
        <reportElement x="0" y="0" width="40" height="20" uuid="bbeb0d48-f4fd-47c9-918b-f505a88c089d"/>
        <text><![CDATA[Fecha:]]></text>
      </staticText>
      <textField>
        <reportElement x="60" y="30" width="100" height="20" uuid="dfd3b420-68fe-4f1f-8363-8a34d2dbff11"/>
        <textFieldExpression><![CDATA[$F{periodo}]]></textFieldExpression>
      </textField>
      <staticText>
        <reportElement x="0" y="30" width="40" height="20" uuid="b6b978b7-8935-4902-9b90-55eda7f3710e"/>
        <text><![CDATA[Periodo:]]></text>
      </staticText>
      <textField>
        <reportElement x="60" y="60" width="100" height="20" uuid="268e690e-5fbf-43af-8047-e474c49c1fc0"/>
        <textFieldExpression><![CDATA[$F{cod}]]></textFieldExpression>
      </textField>
      <staticText>
        <reportElement x="0" y="60" width="40" height="20" uuid="2dc362f3-0306-4d9d-a824-67254dfd8ecb"/>
        <text><![CDATA[Cod:]]></text>
      </staticText>
    </band>
  </pageHeader>
  <columnHeader>
    <band height="30" splitType="Stretch">
      <staticText>
        <reportElement x="20" y="0" width="100" height="30" uuid="a7af5a61-49c5-4284-8d05-c0d2c4e41a64"/>
        <text><![CDATA[Base Datos]]></text>
      </staticText>
      <staticText>
        <reportElement x="120" y="0" width="150" height="30" uuid="e7ab3885-6741-4c11-aacb-bc90e239b529"/>
        <text><![CDATA[Variacion Sin]]></text>
      </staticText>
      <staticText>
        <reportElement x="270" y="0" width="130" height="30" uuid="b47b08f1-93c3-427e-85fc-525748cdc2dc"/>
        <text><![CDATA[Variacion Positiva]]></text>
      </staticText>
      <staticText>
        <reportElement x="400" y="0" width="130" height="30" uuid="461ca03d-5c91-467b-ad13-870709332a0e"/>
        <text><![CDATA[Variacion Negativa]]></text>
      </staticText>
    </band>
  </columnHeader>
  <detail>
    <band height="30" splitType="Stretch">
      <textField>
        <reportElement x="20" y="0" width="100" height="30" uuid="abfdcb6e-526d-43de-8e3d-a053e62ca2fb"/>
        <textFieldExpression><![CDATA[$F{baseDatos}]]></textFieldExpression>
      </textField>
      <textField>
        <reportElement x="120" y="0" width="150" height="30" uuid="6ac462af-7c76-4475-9f2c-cb0de904fb4e"/>
        <textFieldExpression><![CDATA[$F{variacionSin_variacion} + " - " + $F{variacionSin_variacionPorcentaje}]]></textFieldExpression>
      </textField>
      <textField>
        <reportElement x="270" y="0" width="130" height="30" uuid="8ebd76dc-e7c2-4453-8648-bba296dceeb4"/>
        <textFieldExpression><![CDATA[$F{variacionPositiva_variacion} + " - " + $F{variacionPositiva_variacionPorcentaje}]]></textFieldExpression>
      </textField>
      <textField>
        <reportElement x="400" y="0" width="130" height="30" uuid="bc7682d8-10dd-46f5-80af-076f5ef3c075"/>
        <textFieldExpression><![CDATA[$F{variacionNegativa_variacion} + " - " + $F{variacionNegativa_variacionPorcentaje}]]></textFieldExpression>
      </textField>
    </band>
  </detail>
</jasperReport>
 
narcism's picture
narcism
6476
Joined: Nov 22 2010 - 12:39am
Last seen: 6 hours 59 min ago
Feedback