How to hide row group in a Crosstab?

Question

I'm using Jaspersoft Studio 6. I have a data set like this:

sales channel, product, amount
channel a, prod a, 1
channel a, prod b, 1
channel b, prod a, 1
channel b, prod b, 1

I want to generate a crosstab (or whatever component that can do it) like this (2 is the total amout for each product as you can guess)

prod a, prod b
2, 2

so basically a crosstab without row groups, I tried creating a crosstab with sales channel as row group and product as column group, and then delete row group but the report won't compile. The reason I don't create a dedicated dataset for it is I also need to display this crosstab (below) in the same report so thinking to use the same dataset for both tables, is this possible?

        prod a, prod b
channel a   1, 1
channel b   1, 1

Alex K · Accepted Answer · 2016-06-07T21:10:50.543

Question 1: I want to generate a crosstab without row groups. Is it possible?

Yes, it is possible. You can create crosstab with any field in row group and than change the Bucket expression. In case using some constant in expression you will have one group for all rows.

After that you can set width/height attributes with 0 value for all cell you don't need to show. This trick allow you to hide all unwanted cells.

Question 2: Can I use only one dataset for different crosstabs?

Yes, you can use the single datasource for both crosstabs from your sample.

Working example

Datasource

I used the csv datasource.

sales channel, product, amount
channel a, prod a, 1
channel a, prod b, 1
channel b, prod a, 1
channel b, prod b, 1

The name of data adapter for this datasource in the example below is channels.csv.xml

The reports template

I used Studio for designing a report template. The styles in template are left to make the report more attractive.

<?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="crosstab_wout_row_group" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="5edd462e-9135-43d5-a3d2-552c8663900a">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF" hTextAlign="Center" vTextAlign="Middle">
        <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" hTextAlign="Center" vTextAlign="Middle">
        <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" hTextAlign="Center" vTextAlign="Middle">
        <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" hTextAlign="Center" vTextAlign="Middle">
        <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="channelsDS" uuid="ad122c55-8366-4f20-b070-a8d40614c7c5">
        <property name="net.sf.jasperreports.data.adapter" value="channels.csv.xml"/>
        <queryString>
            <![CDATA[]]>
        </queryString>
        <field name="sales channel" class="java.lang.String"/>
        <field name="product" class="java.lang.String"/>
        <field name="amount" class="java.lang.Integer"/>
    </subDataset>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <title>
        <band height="708">
            <crosstab ignoreWidth="true">
                <reportElement x="0" y="10" width="186" height="50" uuid="bad8b778-d3b7-426f-b5fe-ff15218f1df6">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="channelsDS" uuid="fe18e49c-7b5f-4644-8821-6a5ece2305cf">
                            <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRCsvDataSource("c:\\path\\chanels.csv")]]></dataSourceExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="all" width="0" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA["1"]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH"/>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT"/>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="product" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{product}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement style="Crosstab_CH" x="0" y="0" width="60" height="20" uuid="ad62e573-acbd-4e01-a339-d4611a6593d4"/>
                                <textFieldExpression><![CDATA[$V{product}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT"/>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="sum" class="java.lang.Integer" calculation="Sum">
                    <measureExpression><![CDATA[$F{amount}]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement style="Crosstab_CD" x="0" y="0" width="60" height="20" uuid="64c93e0c-c740-41f7-9ad2-a98f32a34f12"/>
                            <textFieldExpression><![CDATA[$V{sum}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="0" height="20" columnTotalGroup="product">
                    <cellContents mode="Opaque" style="Crosstab_CT"/>
                </crosstabCell>
                <crosstabCell width="60" height="0" rowTotalGroup="all">
                    <cellContents mode="Opaque" style="Crosstab_CT"/>
                </crosstabCell>
                <crosstabCell width="0" height="0" rowTotalGroup="all" columnTotalGroup="product">
                    <cellContents mode="Opaque" style="Crosstab_CT"/>
                </crosstabCell>
            </crosstab>
            <crosstab ignoreWidth="true">
                <reportElement x="200" y="10" width="186" height="70" uuid="371e42fa-6c78-45bc-b7c7-5efc8f97c789">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="channelsDS" uuid="a9518e47-0662-4b8a-96bf-876220cbfe3c">
                            <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRCsvDataSource("c:\\path\\chanels.csv")]]></dataSourceExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="channel" width="60" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{sales channel}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement key="" style="Crosstab_CH" x="0" y="0" width="60" height="20" uuid="52e76a1c-73cd-4380-a0c1-1a5a43796992"/>
                                <textFieldExpression><![CDATA[$V{channel}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement style="Crosstab_CT" x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d65fc01b-5f52-478e-97c5-005c50bed918"/>
                                <text><![CDATA[Total]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="product" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{product}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement style="Crosstab_CH" x="0" y="0" width="60" height="20" uuid="006f5601-cda6-42d1-a5c8-ce4e6c6b2ce7"/>
                                <textFieldExpression><![CDATA[$V{product}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement style="Crosstab_CT" x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="cb16eb49-0945-46bc-9e6f-9a83296b8293"/>
                                <text><![CDATA[Total]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="amount_MEASURE" class="java.lang.Integer" calculation="Sum">
                    <measureExpression><![CDATA[$F{amount}]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement style="Crosstab_CD" x="0" y="0" width="60" height="20" uuid="10e01814-0960-4522-99d6-dc2e37eb9a8e"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="product">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement style="Crosstab_CT" x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="34e42b3b-5b34-4ed3-888a-4925d56f37aa"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="channel">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement style="Crosstab_CT" x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="7464296e-2f2b-467c-8700-616d0cb6bc0d"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="channel" columnTotalGroup="product">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement style="Crosstab_CT" x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="f248de34-9e48-4b9b-bc9f-dbe95acba86d"/>
                            <textFieldExpression><![CDATA[$V{amount_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </title>
</jasperReport>

There are two crosstabs in this template: the first without rows groups and unwanted cells and the second have both groups and all cells (by default).

I used this bucket expression for "joining" all channels in one group.

<bucket class="java.lang.String">
    <bucketExpression><![CDATA["1"]]></bucketExpression>
</bucket>

You can change this expression with help of Studio, for example.

I changed the width and height of cells in Studio too.

Output result

I used Studio's preview mode to take a picture of the output result.

Thanks! Will you be able to answer my other question? http://stackoverflow.com/questions/37700673/jasper-report-crosstab-sort — thotwielder, Jun 08 '16 at 11:01