Jump to content
JasperReports Library 7.0 is now available ×

Jasper Report, crosstab manipulation


Go to solution Solved by yama818,

Recommended Posts

 

I'm using TIBCO Jaspersoft® Studio 6.8.0 and I'm creating a subreport containing a Crosstab. The problem is I'm not sure how to manipulate it to create the view i want. Or if there is another element / mix of elements to create something similar.
This is the final result I'd like to have (not the colors, just the structure):

1.jpg.e8638832e2e781a3996f6ef504e28bc0.jpg

As you can see we have a full row text used as title, then a row for "Classe", another full row for "Codice Anomalia" and then the crosstab. So far i created the crosstab part like this

2.jpg.a1890ac526fea79c58431efd6a785b7a.jpg

Here's the code (it's a subreport, so we have few parameters from the master report):

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.8.0.final using JasperReports Library version 6.8.0-2ed8dfabb690ff337a5797129f2cd92902b0c87b  --><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="Prova" pageWidth="595" pageHeight="842" orientation="Landscape" whenNoDataType="NoPages" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isSummaryNewPage="true" isSummaryWithPageHeaderAndFooter="true" uuid="1916d28f-c2ed-4735-a541-6dfbd82fe676">    <property name="com.jaspersoft.studio.unit." value="pixel"/>    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="reportmockAnomalies_Evolution_sub.xml"/>    <style name="DefaultStyle" isDefault="true" fontName="SinaFont" fontSize="8"/>    <style name="DefaultTableStyle" style="DefaultStyle">        <box>            <pen lineWidth="0.5" lineColor="#888888"/>        </box>    </style>    <style name="DefaultTableHeaderStyle" style="DefaultTableStyle" mode="Opaque" backcolor="#F0F0F0"/>    <style name="Table_T" style="DefaultTableStyle"/>    <parameter name="NomeAnomalia_P" class="java.lang.String" isForPrompting="false"/>    <parameter name="GruppiAnomalie_P" class="java.lang.Integer" isForPrompting="false"/>    <parameter name="Total_P" class="java.lang.String" isForPrompting="false"/>    <queryString>        <![CDATA[]]>    </queryString>    <field name="Value" class="java.lang.String"/>    <field name="Code" class="java.lang.String"/>    <field name="Severity" class="java.lang.String"/>    <field name="Class" class="java.lang.Integer"/>    <variable name="VI" class="java.lang.Float">        <variableExpression><![CDATA[Float.parseFloat($F{Value})]]></variableExpression>    </variable>    <variable name="NomeAnomalia" class="java.lang.String">        <variableExpression><![CDATA[$P{NomeAnomalia_P}]]></variableExpression>    </variable>    <variable name="GruppiAnomalie" class="java.lang.Integer">        <variableExpression><![CDATA[$P{GruppiAnomalie_P}]]></variableExpression>    </variable>    <variable name="Total" class="java.lang.String">        <variableExpression><![CDATA[$P{Total_P}]]></variableExpression>    </variable>    <group name="Group1">        <groupExpression><![CDATA[$V{GruppiAnomalie}]]></groupExpression>        <groupFooter>            <band height="362">                <crosstab>                    <reportElement x="10" y="100" width="520" height="130" uuid="ac2fc6d0-7f1c-4d18-885e-4eac7a3e1606">                        <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>                        <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/>                        <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/>                        <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/>                        <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/>                    </reportElement>                    <crosstabParameter name="NomeAnomalia_CT">                        <parameterValueExpression><![CDATA[$V{NomeAnomalia}]]></parameterValueExpression>                    </crosstabParameter>                    <rowGroup name="Severity" width="60" totalPosition="End">                        <bucket class="java.lang.String">                            <bucketExpression><![CDATA[$F{Severity}]]></bucketExpression>                        </bucket>                        <crosstabRowHeader>                            <cellContents mode="Opaque" style="Crosstab_CH">                                <textField>                                    <reportElement x="0" y="0" width="60" height="20" uuid="2a96a380-5476-4519-9668-d8b74e3c3dce"/>                                    <textElement textAlignment="Center" verticalAlignment="Middle"/>                                    <textFieldExpression><![CDATA[$V{Severity}.substring(4)]]></textFieldExpression>                                </textField>                            </cellContents>                        </crosstabRowHeader>                        <crosstabTotalRowHeader>                            <cellContents mode="Opaque" style="Crosstab_CT"/>                        </crosstabTotalRowHeader>                    </rowGroup>                    <columnGroup name="Code" height="20" totalPosition="End">                        <bucket class="java.lang.String">                            <bucketExpression><![CDATA[$F{Code}]]></bucketExpression>                        </bucket>                        <crosstabColumnHeader>                            <cellContents mode="Opaque" style="Crosstab_CH">                                <textField>                                    <reportElement x="0" y="0" width="60" height="20" uuid="6b72c99c-2807-4ecd-9229-60501eef76a8"/>                                    <textElement textAlignment="Center" verticalAlignment="Middle"/>                                    <textFieldExpression><![CDATA[$V{Code}]]></textFieldExpression>                                </textField>                            </cellContents>                        </crosstabColumnHeader>                        <crosstabTotalColumnHeader>                            <cellContents mode="Opaque" style="Crosstab_CT">                                <staticText>                                    <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="ff63be8e-5eb2-4c34-b7d8-5c468d2bde9c"/>                                    <textElement textAlignment="Center" verticalAlignment="Middle"/>                                    <text><![CDATA[%]]></text>                                </staticText>                            </cellContents>                        </crosstabTotalColumnHeader>                    </columnGroup>                    <measure name="Value_MEASURE1" class="java.lang.Float" calculation="Sum">                        <measureExpression><![CDATA[$V{VI}]]></measureExpression>                    </measure>                    <measure name="NA" class="java.lang.String">                        <measureExpression><![CDATA["n/a"]]></measureExpression>                    </measure>                    <measure name="totalCount" class="java.lang.Float" calculation="Sum">                        <measureExpression><![CDATA[$V{VI} == -1 ? 0 : $V{VI}]]></measureExpression>                    </measure>                    <crosstabCell width="60" height="20">                        <cellContents mode="Opaque" style="Crosstab_CD">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" uuid="2f6aa90e-73f8-4f5f-8fdf-47ea4df80674"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Value_MEASURE1} == -1 ? $V{NA} : $V{Value_MEASURE1}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabCell>                    <crosstabCell width="60" height="20" columnTotalGroup="Code">                        <cellContents mode="Opaque" style="Crosstab_CT">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="2b009b35-e77c-4cba-857e-84ba4ea6bfc6"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{totalCount}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabCell>                    <crosstabCell width="60" height="20" rowTotalGroup="Severity">                        <cellContents mode="Opaque" style="Crosstab_CT"/>                    </crosstabCell>                    <crosstabCell width="60" height="20" rowTotalGroup="Severity" columnTotalGroup="Code">                        <cellContents mode="Opaque" style="Crosstab_CT"/>                    </crosstabCell>                </crosstab>            </band>        </groupFooter>    </group></jasperReport>[/code]

What I'd like is to add the 3 upper rows like I explained above, is this possible? I'm open to alternatives too regarding other elements or mixing different elements, as long as the result is similar to the first photo.
Thank you in advance!

 

Edit following the answer of yama818

yama818, it's close, I'm trying to manage that....i forgot to mention that I'm taking data from a json passed through api...now I'm using a mock:

[        {        "NomeAnomalia": "MIT Impalcati,Travi,Traversi CA CAP",        "GruppiAnomalie": 201,        "Total": "6%",        "anomalyList": [            {                "GruppiAnomalie": 201,                "Code": "cacap1",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 3.2736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "cacap12",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 1.2736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "cacap16",                "Name": "Macchie di umidita passiva",                "Class": 2,                "Severity": "2 - Forte",                "Value": 0.7736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "DifGen1",                "Name": "Macchie di umidita passiva",                "Class": 5,                "Severity": "1 - Media",                "Value": 1.571812154            },            {                "GruppiAnomalie": 201,                "Code": "Test Code",                "Name": "Test Name",                "Class": 5,                "Severity": "1 - Media",                "Value": -1            },            {                "GruppiAnomalie": 201,                "Code": "Test Code 2",                "Name": "Test Name 2",                "Class": 5,                "Severity": "2 - Forte",                "Value": -1            }        ],        "pieChartData": [            {"severityName": "Lieve", "severityValue": 25, "severityLabel": "25%"},            {"severityName": "Media", "severityValue": 15, "severityLabel": "15%"},            {"severityName": "Forte", "severityValue": 38, "severityLabel": "38%"}        ]    },    {        "NomeAnomalia": "MIT Pile,Spalle,Fondazioni CLS",        "GruppiAnomalie": 202,        "Total": "6%",        "anomalyList": [            {                "GruppiAnomalie": 202,                "Code": "cacap1",                "Name": "Macchie di umidita passiva",                "Class": 2,                "Severity": "0 - Lieve",                "Value": 3.2736806021763885            },            {                "GruppiAnomalie": 202,                "Code": "cacap12",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 1.2736806021763885            },            {                "GruppiAnomalie": 202,                "Code": "cacap16",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "2 - Forte",                "Value": 0.7736806021763885            }        ],        "pieChartData": [            {"severityName": "Lieve", "severityValue": 25, "severityLabel": "5%"},            {"severityName": "Media", "severityValue": 15, "severityLabel": "87%"},            {"severityName": "Forte", "severityValue": 38, "severityLabel": "47%"}        ]    }]

anomalyList is the part of the json passed to the subreport (i passed other values too, like "NomeAnomalia", "GruppiAnomalie" and "Total" through parameters).

Every single column should have "Class", "Code", and "Severity" value. Measurements are showed with "Value" field, and I already managed to have a total of each "Value" at the end of the row (second image i posted in the first post).

As you noted, total % is already there, and with values of Severity ("0 - Lieve", "1 - Media", "2 - Forte") I already managed to give it a order.

If anyone has got a solution...

Thank you again!

 

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

It's not perfect, but I got this far.


- Preview image

w20210727_preview_sample.png.b19fc5a1d3048a2f4a13a5e1a54b68b8.png

Here are some additional points that need to be considered
- Adding a % column
- The order of the rows

 

Also, I tentatively assumed the following way of holding the data.
- Sample table

w20210727_table_sample.png.88abbf3ccd14514afa9a4a9473b2bbfa.png


- Part of the sample data

w20210727_data_sample.png.2eb5dd37b9c7e84147264fd499e0189e.png


- Sample source

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.5.0.final using JasperReports Library version 6.11.0-0c4056ccaa4d25a5a8c45672d2f764ea3498bebb  --><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="W20210727_crosstab" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="df1676c4-d142-482a-87c3-ec841361a022">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="MySQL_44_test"/>    <style name="Crosstab_CH" 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="Crosstab_CG" 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="Crosstab_CT" mode="Opaque" backcolor="#005FB3">        <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="Crosstab_CD" 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>    <queryString language="SQL">        <![CDATA[select * from t6]]>    </queryString>    <field name="seq" class="java.lang.Integer">        <property name="com.jaspersoft.studio.field.label" value="seq"/>        <property name="com.jaspersoft.studio.field.tree.path" value="t6"/>    </field>    <field name="Class" class="java.lang.String">        <property name="com.jaspersoft.studio.field.label" value="Class"/>        <property name="com.jaspersoft.studio.field.tree.path" value="t6"/>    </field>    <field name="CodiceAnomalia" class="java.lang.String">        <property name="com.jaspersoft.studio.field.label" value="CodiceAnomalia"/>        <property name="com.jaspersoft.studio.field.tree.path" value="t6"/>    </field>    <field name="Grave" class="java.lang.String">        <property name="com.jaspersoft.studio.field.label" value="Grave"/>        <property name="com.jaspersoft.studio.field.tree.path" value="t6"/>    </field>    <background>        <band splitType="Stretch"/>    </background>    <summary>        <band height="257" splitType="Stretch">            <crosstab>                <reportElement x="0" y="51" width="550" height="180" uuid="08b339f3-c517-477b-8cd4-58b6199e7f7e">                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>                    <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/>                    <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/>                    <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/>                    <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/>                </reportElement>                <crosstabDataset>                    <dataset resetType="Report"/>                </crosstabDataset>                <rowGroup name="Grave" width="100">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{Grave}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="100" height="20" uuid="38444ceb-2b49-45be-85cc-53eae5beeefd"/>                                <box leftPadding="2"/>                                <textElement verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Grave}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents mode="Opaque" style="Crosstab_CT">                            <staticText>                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d017997e-a0fb-419f-9483-ecdd92484c94"/>                                <text><![CDATA[Total Grave]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalRowHeader>                </rowGroup>                <columnGroup name="Class" height="20">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{Class}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" uuid="83e0f1d7-3e3c-4a5a-a044-c563a5d72415"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Class}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CT">                            <staticText>                                <reportElement x="0" y="0" width="60" height="40" forecolor="#FFFFFF" uuid="9501c233-5bfc-4cdf-8842-b9c15ec36ae6"/>                                <text><![CDATA[Total Class]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <columnGroup name="CodiceAnomalia" height="20">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{CodiceAnomalia}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" uuid="291850ad-65f3-407a-9c94-3922d70ca358"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{CodiceAnomalia}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CG">                            <staticText>                                <reportElement x="0" y="0" width="60" height="20" uuid="518b1fea-7104-48a7-b501-4649842e1b1a"/>                                <text><![CDATA[Total CodiceAnomalia]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <measure name="seq_MEASURE" class="java.lang.Integer" calculation="Count">                    <measureExpression><![CDATA[$F{seq}]]></measureExpression>                </measure>                <crosstabCell width="60" height="20">                    <cellContents mode="Opaque" style="Crosstab_CD">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" uuid="732ac6d1-1422-48e0-8fa8-31b1f6364392"/>                            <textElement textAlignment="Center" verticalAlignment="Middle"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" columnTotalGroup="Class">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="eb7fe51e-c2d9-444b-9e80-573a88053a06"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" columnTotalGroup="CodiceAnomalia">                    <cellContents mode="Opaque" style="Crosstab_CG">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" uuid="a716aa6c-39ae-47c9-8e68-49c5c6223eac"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Grave">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7127fc87-7037-44fc-894e-b4d758585da5"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Grave" columnTotalGroup="Class">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d2db2e47-8734-4f30-bb36-776a961abef5"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Grave" columnTotalGroup="CodiceAnomalia">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7c873d7b-79dc-4c74-9636-c721156e7b72"/>                            <textFieldExpression><![CDATA[$V{seq_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>            </crosstab>            <staticText>                <reportElement x="0" y="50" width="100" height="21" uuid="8c281303-39bd-456a-bf6b-511bcc71fb3d"/>                <box leftPadding="2"/>                <textElement verticalAlignment="Middle"/>                <text><![CDATA[Class]]></text>            </staticText>            <staticText>                <reportElement x="0" y="70" width="100" height="21" uuid="3a67121c-326b-43ba-bb90-d8c8f04cc9be"/>                <box leftPadding="2"/>                <textElement verticalAlignment="Middle">                    <font isBold="true"/>                </textElement>                <text><![CDATA[Codice Anomalia]]></text>            </staticText>            <staticText>                <reportElement x="0" y="10" width="180" height="30" uuid="1f0196ba-f401-4809-8409-d9ab752f11da"/>                <textElement verticalAlignment="Middle">                    <font size="16" isBold="true"/>                </textElement>                <text><![CDATA[Gruppo Anomalie .....]]></text>            </staticText>        </band>    </summary></jasperReport>

 

I hope this is helpful.

 

Link to comment
Share on other sites

yama818, it's close, I'm trying to manage that....i forgot to mention that I'm taking data from a json passed through api...now I'm using a mock:

[        {        "NomeAnomalia": "MIT Impalcati,Travi,Traversi CA CAP",        "GruppiAnomalie": 201,        "Total": "6%",        "anomalyList": [            {                "GruppiAnomalie": 201,                "Code": "cacap1",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 3.2736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "cacap12",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 1.2736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "cacap16",                "Name": "Macchie di umidita passiva",                "Class": 2,                "Severity": "2 - Forte",                "Value": 0.7736806021763885            },            {                "GruppiAnomalie": 201,                "Code": "DifGen1",                "Name": "Macchie di umidita passiva",                "Class": 5,                "Severity": "1 - Media",                "Value": 1.571812154            },            {                "GruppiAnomalie": 201,                "Code": "Test Code",                "Name": "Test Name",                "Class": 5,                "Severity": "1 - Media",                "Value": -1            },            {                "GruppiAnomalie": 201,                "Code": "Test Code 2",                "Name": "Test Name 2",                "Class": 5,                "Severity": "2 - Forte",                "Value": -1            }        ],        "pieChartData": [            {"severityName": "Lieve", "severityValue": 25, "severityLabel": "25%"},            {"severityName": "Media", "severityValue": 15, "severityLabel": "15%"},            {"severityName": "Forte", "severityValue": 38, "severityLabel": "38%"}        ]    },    {        "NomeAnomalia": "MIT Pile,Spalle,Fondazioni CLS",        "GruppiAnomalie": 202,        "Total": "6%",        "anomalyList": [            {                "GruppiAnomalie": 202,                "Code": "cacap1",                "Name": "Macchie di umidita passiva",                "Class": 2,                "Severity": "0 - Lieve",                "Value": 3.2736806021763885            },            {                "GruppiAnomalie": 202,                "Code": "cacap12",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "0 - Lieve",                "Value": 1.2736806021763885            },            {                "GruppiAnomalie": 202,                "Code": "cacap16",                "Name": "Macchie di umidita passiva",                "Class": 1,                "Severity": "2 - Forte",                "Value": 0.7736806021763885            }        ],        "pieChartData": [            {"severityName": "Lieve", "severityValue": 25, "severityLabel": "5%"},            {"severityName": "Media", "severityValue": 15, "severityLabel": "87%"},            {"severityName": "Forte", "severityValue": 38, "severityLabel": "47%"}        ]    }][/code]

anomalyList is the part of the json passed to the subreport (i passed other values too, like "NomeAnomalia", "GruppiAnomalie" and "Total" through parameters).

Every single column should have "Class", "Code", and "Severity" value. Measurements are showed with "Value" field, and I already managed to have a total of each "Value" at the end of the row (second image i posted in the first post).

As you noted, total % is already there, and with values of Severity ("0 - Lieve", "1 - Media", "2 - Forte") I already managed to give it a order.

Your solution is close but not right...I will try to modify your example and see what I got...if you have time and will, you can start too starting from the json file

Thank you again!

Link to comment
Share on other sites

  • Solution

I changed the input to a json file.


- Preview image

w20210729_preview_sample.png.b0cbb8e4f717c494550c7c0c56b0ef74.png

- Sample source

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.5.0.final using JasperReports Library version 6.11.0-0c4056ccaa4d25a5a8c45672d2f764ea3498bebb  --><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="W20210727_crosstab" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="df1676c4-d142-482a-87c3-ec841361a022">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="test_json3"/>    <property name="com.jaspersoft.studio.unit." value="pixel"/>    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>    <style name="Crosstab_CH" 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="Crosstab_CG" 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="Crosstab_CT" mode="Opaque" backcolor="#005FB3">        <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="Crosstab_CD" 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>    <queryString language="JSON">        <![CDATA[anomalyList]]>    </queryString>    <field name="GruppiAnomalie" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression" value="GruppiAnomalie"/>        <fieldDescription><![CDATA[GruppiAnomalie]]></fieldDescription>    </field>    <field name="Code" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="Code"/>        <fieldDescription><![CDATA[Code]]></fieldDescription>    </field>    <field name="Name" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="Name"/>        <fieldDescription><![CDATA[Name]]></fieldDescription>    </field>    <field name="Class" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression" value="Class"/>        <property name="com.jaspersoft.studio.field.label" value="Class"/>        <property name="com.jaspersoft.studio.field.tree.path" value="t6"/>        <fieldDescription><![CDATA[Class]]></fieldDescription>    </field>    <field name="Severity" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="Severity"/>        <fieldDescription><![CDATA[severity]]></fieldDescription>    </field>    <field name="Value" class="java.math.BigDecimal">        <property name="net.sf.jasperreports.json.field.expression" value="Value"/>        <fieldDescription><![CDATA[Value]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <summary>        <band height="283" splitType="Stretch">            <staticText>                <reportElement x="0" y="50" width="100" height="21" uuid="8c281303-39bd-456a-bf6b-511bcc71fb3d"/>                <box leftPadding="2"/>                <textElement verticalAlignment="Middle"/>                <text><![CDATA[Class]]></text>            </staticText>            <staticText>                <reportElement x="0" y="70" width="100" height="21" uuid="3a67121c-326b-43ba-bb90-d8c8f04cc9be"/>                <box leftPadding="2"/>                <textElement verticalAlignment="Middle">                    <font isBold="true"/>                </textElement>                <text><![CDATA[Codice Anomalia]]></text>            </staticText>            <staticText>                <reportElement x="0" y="10" width="180" height="30" uuid="1f0196ba-f401-4809-8409-d9ab752f11da"/>                <textElement verticalAlignment="Middle">                    <font size="16" isBold="true"/>                </textElement>                <text><![CDATA[Gruppo Anomalie .....]]></text>            </staticText>            <crosstab>                <reportElement x="2" y="50" width="798" height="130" uuid="d529e7c9-8838-4ff3-a819-783a70e85f5d">                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>                    <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/>                    <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/>                    <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/>                    <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/>                </reportElement>                <crosstabDataset>                    <dataset resetType="Report"/>                </crosstabDataset>                <rowGroup name="Severity" width="100">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{Severity}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="100" height="20" uuid="e56deaa8-0432-4ea8-ae0c-44476ef2e450"/>                                <box leftPadding="4"/>                                <textElement verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Severity}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents mode="Opaque" style="Crosstab_CT">                            <staticText>                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="a9422fc4-9fd4-4af2-bf13-183cf7f50247"/>                                <text><![CDATA[Total Severity]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalRowHeader>                </rowGroup>                <columnGroup name="Class" height="20" totalPosition="End">                    <bucket class="java.lang.Integer">                        <bucketExpression><![CDATA[$F{Class}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" uuid="4998cf69-a4eb-46ea-9571-1252ed35dc1b"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Class}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CT">                            <staticText>                                <reportElement x="0" y="0" width="60" height="40" forecolor="#FFFFFF" uuid="e9cd7912-54c5-4822-90aa-d42d46c8ddb4"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <text><![CDATA[%]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <columnGroup name="Code" height="20">                    <bucket class="java.lang.String">                        <bucketExpression><![CDATA[$F{Code}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CH">                            <textField>                                <reportElement x="0" y="0" width="60" height="20" uuid="2d368a8a-100b-4ece-b242-7f7100c8ea41"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <textFieldExpression><![CDATA[$V{Code}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents mode="Opaque" style="Crosstab_CG">                            <staticText>                                <reportElement x="0" y="0" width="60" height="20" uuid="3b9b5b72-0f96-45fd-acfd-df0c708cc295"/>                                <text><![CDATA[Total Code]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <measure name="Value_MEASURE" class="java.math.BigDecimal" calculation="Sum">                    <measureExpression><![CDATA[$F{Value}]]></measureExpression>                </measure>                <crosstabCell width="60" height="20">                    <cellContents mode="Opaque" style="Crosstab_CD">                        <textField pattern="0.00">                            <reportElement x="0" y="0" width="60" height="20" uuid="945a8120-7e38-474b-9e2a-b90b9e62e0e4"/>                            <textElement textAlignment="Center" verticalAlignment="Middle"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" columnTotalGroup="Class">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField pattern="0.00">                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="b0400007-1abd-44c9-9106-f124b32062b4"/>                            <textElement textAlignment="Center" verticalAlignment="Middle"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" columnTotalGroup="Code">                    <cellContents mode="Opaque" style="Crosstab_CG">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" uuid="10cb4cfe-19a4-4a7f-a474-0379b739a9b7"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Severity">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="a46eba72-7a92-4e20-9c0a-9b9437bc93f2"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Severity" columnTotalGroup="Class">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="5345354d-89e0-4d00-b6bd-70f1d5c64343"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="60" height="20" rowTotalGroup="Severity" columnTotalGroup="Code">                    <cellContents mode="Opaque" style="Crosstab_CT">                        <textField>                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="5d54a4af-9196-4f78-9732-65eb07429fd9"/>                            <textFieldExpression><![CDATA[$V{Value_MEASURE}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>            </crosstab>        </band>    </summary></jasperReport>

I hope this is helpful.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...