[SOLVED] Repeated values in Crosstab

1

 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
fgaudin's picture
Joined: Jun 14 2010 - 3:03am
Last seen: 5 years 6 months ago

10 Answers:

0

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

baggypants's picture
Joined: Apr 7 2009 - 12:43am
Last seen: 10 years 7 months ago
0

 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
Joined: Jun 14 2010 - 3:03am
Last seen: 5 years 6 months ago
1

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

baggypants's picture
Joined: Apr 7 2009 - 12:43am
Last seen: 10 years 7 months ago
0

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
Joined: Apr 11 2011 - 10:41pm
Last seen: 8 years 7 months ago
0

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
Joined: Jun 14 2010 - 3:03am
Last seen: 5 years 6 months ago
0

Hi,

 

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

How do you create this invisible column?

 

Regards

besmirgogu's picture
Joined: Apr 11 2011 - 10:41pm
Last seen: 8 years 7 months ago
0

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
Joined: Jun 14 2010 - 3:03am
Last seen: 5 years 6 months ago
1

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
543
Joined: Mar 19 2007 - 4:15am
Last seen: 5 days 23 hours ago
3

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
Joined: Jun 23 2014 - 10:40pm
Last seen: 4 years 12 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 - 5 years 3 months ago
0

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

gotoark's picture
12
Joined: Mar 2 2018 - 3:15am
Last seen: 1 year 4 months ago
Feedback
randomness