How to put the count columns of the crosstable in the pageheader?

0

Hi everyone! 

I have a problem with the count columns of my crosstable. I just to put the count columns in the page header section od my reports. The problem is that the crosstable born of a dataset. How do you do? or is there anything for this?

 

Thanks you !! 

asd.backup's picture
Joined: Mar 21 2017 - 7:53am
Last seen: 2 years 4 months ago

2 Answers:

0

check this working example :

Instead of counting the columns inside the crosstab, I'm counting the distinct field values on the main report using a variable.

Please note that crosstab will not allow you to count the columns with varying execution timings.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2017-04-17T12:03:33 -->
<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="Blank_A4_3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="05e26242-e3d0-4d1d-be44-111aa4ea6c1e">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <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>
    <style name="Crosstab 1_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 1_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 1_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 1_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>
        <![CDATA[select 'one-1' as ID, 1 as no from dual union
select 'one-2' as ID, 2 as no from dual union
select 'one-3' as ID, 3 as no from dual union
select 'Two-1' as ID, 1 as no from dual union
select 'Two-2' as ID, 1 as no from dual union
select 'Two-1' as ID, 2 as no from dual]]>
    </queryString>
    <field name="ID" class="java.lang.String"/>
    <field name="NO" class="java.math.BigDecimal"/>
    <variable name="Variable_1" class="java.lang.Integer" calculation="DistinctCount">
        <variableExpression><![CDATA[$F{ID}]]></variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <pageHeader>
        <band height="30">
            <staticText>
                <reportElement x="0" y="0" width="80" height="30" uuid="b410e5ab-8d4f-42a9-b8ef-1a79b4b77c05"/>
                <text><![CDATA[Column Count]]></text>
            </staticText>
            <textField evaluationTime="Report">
                <reportElement x="80" y="0" width="100" height="30" uuid="c15ef0e5-b3b4-439c-8253-86d760c8e711"/>
                <textFieldExpression><![CDATA[$V{Variable_1}]]></textFieldExpression>
            </textField>
        </band>
    </pageHeader>
    <summary>
        <band height="60" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="555" height="60" uuid="3723c618-efae-49dc-988c-5fd37fd0179e">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <crosstabHeaderCell>
                    <cellContents/>
                </crosstabHeaderCell>
                <rowGroup name="NO1" width="60" totalPosition="End">
                    <bucket class="java.math.BigDecimal">
                        <bucketExpression><![CDATA[$F{NO}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="12cbf8a1-8b2e-4ef2-956f-aec51b8d7f32"/>
                                <textFieldExpression><![CDATA[$V{NO1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="fe414e4a-e0b0-495b-b8bb-653a6ab442e2"/>
                                <text><![CDATA[Total NO1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="ID1" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{ID}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CH">
                            <textField>
                                <reportElement x="0" y="0" width="60" height="20" uuid="2e9b8629-9dfd-4653-9cef-972dfb11bcc3"/>
                                <textFieldExpression><![CDATA[$V{ID1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="58dfcb9d-e9d3-4210-ac62-8e00bf9627db"/>
                                <text><![CDATA[Total ID1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="NO_MEASURE1" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{NO}]]></measureExpression>
                </measure>
                <crosstabCell width="60" height="20">
                    <cellContents mode="Opaque" style="Crosstab 1_CD">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="9db324df-e668-4828-9dc1-c202e629f5de"/>
                            <textFieldExpression><![CDATA[$V{NO_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" columnTotalGroup="ID1">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="40a19183-1461-4f01-a9b6-bee9a713c559"/>
                            <textFieldExpression><![CDATA[$V{NO_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="NO1">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="fc668eff-dc56-4cf4-ab6e-1a0f0f3bc33e"/>
                            <textFieldExpression><![CDATA[$V{NO_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="60" height="20" rowTotalGroup="NO1" columnTotalGroup="ID1">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="59d9ef19-3922-40e4-bd54-a7f70658d5ee"/>
                            <textFieldExpression><![CDATA[$V{NO_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </summary>
</jasperReport>

reportdev's picture
1224
Joined: Oct 12 2015 - 12:05pm
Last seen: 2 weeks 1 day ago
0

Thank you for your answer!

But in my case, I didn't declare the total in the crosstable because I don't want show this variable in the table. I just want to show the total in the page header.

please any idea for this ?

Thank you !!

Best regards.

 

 

asd.backup's picture
Joined: Mar 21 2017 - 7:53am
Last seen: 2 years 4 months ago
Feedback
randomness