[SOLVED] Repeated values in Crosstab

Posted on June 14, 2010 at 7:38am

 Hello,

 

I have two row groups in my crosstab element and I would like values to be repeated so that the report output can be used effectively in Excel.

 

Here is the output I get:

 

    2008 2009 2010
GA a 22 3 9
  e 65 4 6
IR l 4 34 7
VG a 14 8 6

And this is the output I would like to have:

    2008 2009 2010
GA a 22 3 9
GA e 65 4 6
IR l 4 34 7
VG a 14 8 6
 

I have tried selecting and deselecting "print repeated values" for the first row group but it doesn't seem to make any difference to the output.

Does anyone know if I can make the values repeat as in the second example above??

 

 

Code: 
<?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 <a target="_blank" href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd""><a href="http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"" target="_blank">http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"</a></a> name="crosstab" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<style name="Crosstab Data Text" hAlign="Center"/>
	<queryString>
		<![CDATA[select jpx_ciafr.afr_idf,
jpx_ciafr.afr_cod_typ as nature,
jpx_ciafr.cpc_cod as competence,
jpx_ciopr.dec_cod_typ, jpx_ciopr.scn_idf, jpx_ciopr.opr_idf as cnt,
jpx_ciafr.cpc_cod from jpx_ciopr, jpx_ciafr
where jpx_ciopr.afr_idf = jpx_ciafr.afr_idf and scn_idf = 'TUTLN' and
dec_cod_typ is not null and cpc_cod is not null]]>
	</queryString>
	<field name="AFR_IDF" class="java.lang.String"/>
	<field name="NATURE" class="java.lang.String"/>
	<field name="COMPETENCE" class="java.lang.String"/>
	<field name="DEC_COD_TYP" class="java.lang.String"/>
	<field name="SCN_IDF" class="java.lang.String"/>
	<field name="CNT" class="java.math.BigDecimal"/>
	<field name="CPC_COD" class="java.lang.String"/>
	<background>
		<band/>
	</background>
	<title>
		<band/>
	</title>
	<pageHeader>
		<band/>
	</pageHeader>
	<columnHeader>
		<band/>
	</columnHeader>
	<detail>
		<band height="1"/>
	</detail>
	<columnFooter>
		<band height="45"/>
	</columnFooter>
	<pageFooter>
		<band height="54"/>
	</pageFooter>
	<summary>
		<band height="42">
			<crosstab>
				<reportElement x="0" y="0" width="555" height="42"/>
				<rowGroup name="NATURE" width="70">
					<bucket>
						<bucketExpression class="java.lang.String"><![CDATA[$F{NATURE}]]></bucketExpression>
					</bucket>
					<crosstabRowHeader>
						<cellContents backcolor="#F0F8FF" mode="Opaque">
							<textField>
								<reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="25"/>
								<textElement/>
								<textFieldExpression class="java.lang.String"><![CDATA[$V{NATURE}]]></textFieldExpression>
							</textField>
						</cellContents>
					</crosstabRowHeader>
					<crosstabTotalRowHeader>
						<cellContents/>
					</crosstabTotalRowHeader>
				</rowGroup>
				<rowGroup name="COMPETENCE" width="70">
					<bucket>
						<bucketExpression class="java.lang.String"><![CDATA[$F{COMPETENCE}]]></bucketExpression>
					</bucket>
					<crosstabRowHeader>
						<cellContents backcolor="#F0F8FF" mode="Opaque">
							<textField>
								<reportElement style="Crosstab Data Text" x="0" y="0" width="70" height="25"/>
								<textElement/>
								<textFieldExpression class="java.lang.String"><![CDATA[$V{COMPETENCE}]]></textFieldExpression>
							</textField>
						</cellContents>
					</crosstabRowHeader>
					<crosstabTotalRowHeader>
						<cellContents/>
					</crosstabTotalRowHeader>
				</rowGroup>
				<columnGroup name="DEC_COD_TYP" height="30">
					<bucket>
						<bucketExpression class="java.lang.String"><![CDATA[$F{DEC_COD_TYP}]]></bucketExpression>
					</bucket>
					<crosstabColumnHeader>
						<cellContents backcolor="#F0F8FF" mode="Opaque">
							<textField>
								<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="30"/>
								<textElement/>
								<textFieldExpression class="java.lang.String"><![CDATA[$V{DEC_COD_TYP}]]></textFieldExpression>
							</textField>
						</cellContents>
					</crosstabColumnHeader>
					<crosstabTotalColumnHeader>
						<cellContents/>
					</crosstabTotalColumnHeader>
				</columnGroup>
				<measure name="CNTMeasure" class="java.lang.Integer" calculation="Count">
					<measureExpression><![CDATA[$F{CNT}]]></measureExpression>
				</measure>
				<crosstabCell width="50" height="25">
					<cellContents>
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell height="25" rowTotalGroup="NATURE">
					<cellContents backcolor="#005FB3" mode="Opaque">
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25" forecolor="#FFFFFF"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell width="50" columnTotalGroup="DEC_COD_TYP">
					<cellContents backcolor="#BFE1FF" mode="Opaque">
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell rowTotalGroup="NATURE" columnTotalGroup="DEC_COD_TYP">
					<cellContents backcolor="#005FB3" mode="Opaque">
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25" forecolor="#FFFFFF"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell height="25" rowTotalGroup="COMPETENCE">
					<cellContents backcolor="#BFE1FF" mode="Opaque">
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell rowTotalGroup="COMPETENCE" columnTotalGroup="DEC_COD_TYP">
					<cellContents backcolor="#BFE1FF" mode="Opaque">
						<textField>
							<reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>
							<textElement/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{CNTMeasure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
			</crosstab>
		</band>
	</summary>
</jasperReport>


Post Edited by fgaudin at 09/14/2011 08:14
iReport Designer
fgaudin's picture
fgaudin
169
Joined: Jun 14 2010 - 3:03am
Last seen: 2 years 2 months ago

10 Answers:

Posted on June 15, 2010 at 2:34am

Create another group which is Column1||Column2 and set that above column1 and then hide it?

baggypants's picture
baggypants
2107
Joined: Apr 7 2009 - 12:43am
Last seen: 13 years 10 months ago
Posted on June 15, 2010 at 7:10am

 That does work and might be a workaround in some cases. However the reason I want to export to excel is to be able reorder and filter on the different columns.  If it ends up all concatenated in one cell then that defeats the purpose of exporting to excel... :-(

fgaudin's picture
fgaudin
169
Joined: Jun 14 2010 - 3:03am
Last seen: 2 years 2 months ago
Posted on June 21, 2010 at 8:04am

Have three columns with the concatenated cell being the first one and then hide it somehow.

baggypants's picture
baggypants
2107
Joined: Apr 7 2009 - 12:43am
Last seen: 13 years 10 months ago
Posted on April 25, 2011 at 3:48am

Hi,

 

Did you actually manage to find a way to print repeated values in a crosstab?

I have the exact same problem.

 

besmirgogu's picture
besmirgogu
71
Joined: Apr 11 2011 - 10:41pm
Last seen: 11 years 10 months ago
Posted on April 26, 2011 at 11:32pm

Hi,

Yes, I followed baggypants's advice and it works fine.  Just create an invisible column that contains a concatenation of your columns fields.  In that way the values will always be unique and the repeated values in the other columns will be displayed.

I hope that's helpful.  Let us know what you find...

fgaudin's picture
fgaudin
169
Joined: Jun 14 2010 - 3:03am
Last seen: 2 years 2 months ago
Posted on April 28, 2011 at 2:35am

Hi,

 

thank you very much for your reply. I have another question?

How do you create this invisible column?

 

Regards

besmirgogu's picture
besmirgogu
71
Joined: Apr 11 2011 - 10:41pm
Last seen: 11 years 10 months ago
Posted on April 28, 2011 at 3:34am

From memory I think there is a 'visible' attribute if not as a last resort you could set the width to 0 and drag the rest of the columns to take the place.

fgaudin's picture
fgaudin
169
Joined: Jun 14 2010 - 3:03am
Last seen: 2 years 2 months ago
Posted on September 13, 2011 at 1:50pm

Great post everyone; I can't believe this was so easy and I had been struggling with it for so long.  Thank you!

ktalarico's picture
ktalarico
1285
Joined: Mar 19 2007 - 4:15am
Last seen: 7 months 3 weeks ago
Posted on June 23, 2014 at 10:57pm

change the bucket expression of report row group. make the bucket expression by concatenation of few fields.

Eg:

<bucket class="java.lang.String">

<bucketExpression><![CDATA[$F{field1}+""+$F{field2}]]></bucketExpression>

</bucket>
dimuthuroshan3's picture
dimuthuroshan3
50
Joined: Jun 23 2014 - 10:40pm
Last seen: 8 years 2 months ago

Hello,
I have similar problem but here is situation i.e., I have a cross tab with 3 row groups and multiple column groups. When data filled in cross tab,first row group is blank when the second & row groups having the data.What we need is that crosstab should allow duplicate values instead of blanks. I tried to having a invisible column but didn't worked for me.

RowGroup1 RowGroup2 RowGroup3 ColumnGroup1 ColumnGroup2
1 11 111 22 23
12 121 24 24

So,Here in second row,We need to display 1 in first column.

Can any one please help me to succeed from this situation?
Please let me know if you need any further details to get this done?
Thanks,
Gollapudi.

gollapudi.narasimharao - 8 years 6 months ago
add comment
Posted on March 2, 2018 at 3:39am

In bucket Expression, I added the $F{repeatedField}+"     "+$F{nonrepeatedField} as mentioned by @dimuthuroshan3 .It works like a charm Thanks Man :)

gotoark's picture
gotoark
122
Joined: Mar 2 2018 - 3:15am
Last seen: 4 years 6 months ago
Feedback