Jasper Report, crosstab manipulation

 

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

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

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="report\mock\Anomalies_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>

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!

mauri.alessandroit's picture
Joined: Jul 26 2021 - 7:30am
Last seen: 10 months 1 week ago

3 Answers:

I changed the input to a json file.


- Preview image

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

yama818's picture
2853
Joined: Aug 17 2018 - 3:48pm
Last seen: 6 min 36 sec ago

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


- Preview image

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


- Part of the sample data


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

yama818's picture
2853
Joined: Aug 17 2018 - 3:48pm
Last seen: 6 min 36 sec ago

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.

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!

mauri.alessandroit's picture
Joined: Jul 26 2021 - 7:30am
Last seen: 10 months 1 week ago
Feedback