How to fill table with subdataset json ?

asd.backup · April 11, 2017

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!

narcism · April 12, 2017

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

asd.backup · April 12, 2017

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!

narcism · April 12, 2017

This is one way to do it:

With current JSON language:

<?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="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>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<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"?>


<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>

Sign In

How to fill table with subdataset json ?

Recommended Posts

asd.backup

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

narcism

Link to comment

Share on other sites

asd.backup

Link to comment

Share on other sites

narcism

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore