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 !!
2 Answers:
Posted on April 17, 2017 at 12:06pm
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>
