Incorrect % value in Crosstab TOTAL Field.

[ananyachoubey18]

Hi,

I have created a report using Crosstab and I am simply taking the average of the values and then displaying it in the percentage form.

However, when I take the average manually or using excel, my total value doesn't match the crosstab report for few entries. 

In the image below, as you can see, the average for CORP branch is incorrect. It is supposed to be 75.25% but crosstab is calculating it to be 75.59%

Can someone please tell me how to fix this?

 crosstab.bmp

 

 

<crosstab>

<reportElement x="0" y="0" width="2160" height="426" uuid="fad7e9b5-6685-49d8-83d9-e7ddec9a72bd">

<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>

</reportElement>

<rowGroup name="Branch1" width="60" totalPosition="End">

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

<bucketExpression><![CDATA[$F{Branch}]]></bucketExpression>

</bucket>

<crosstabRowHeader>

<cellContents mode="Opaque" style="Crosstab_CH">

<textField>

<reportElement x="0" y="0" width="60" height="20" uuid="ddc76b5c-fa7b-4f8d-9365-dc5489e1ea4d"/>

<textElement>

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{Branch1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabRowHeader>

<crosstabTotalRowHeader>

<cellContents mode="Opaque" style="Crosstab_CT">

<staticText>

<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="3f681ef8-8c0b-4ea5-b3f5-680c7fa79b71"/>

<textElement verticalAlignment="Middle">

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<text><![CDATA[Total Branch1]]></text>

</staticText>

</cellContents>

</crosstabTotalRowHeader>

</rowGroup>

<columnGroup name="Insertion_Time1" height="20" totalPosition="End">

<bucket class="java.sql.Timestamp">

<bucketExpression><![CDATA[$F{Insertion_Time}]]></bucketExpression>

</bucket>

<crosstabColumnHeader>

<cellContents mode="Opaque" style="Crosstab_CH">

<textField pattern="M/d/yy">

<reportElement x="0" y="0" width="60" height="20" uuid="d38f3a54-ac5f-4a84-9927-139d2ad515f1"/>

<textElement verticalAlignment="Middle">

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{Insertion_Time1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabColumnHeader>

<crosstabTotalColumnHeader>

<cellContents mode="Opaque" style="Crosstab_CT">

<staticText>

<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="89c4c5c6-be8b-4134-be8b-ac69801a7824"/>

<textElement verticalAlignment="Middle">

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<text><![CDATA[Total Insertion_Time1]]></text>

</staticText>

</cellContents>

</crosstabTotalColumnHeader>

</columnGroup>

<measure name="folder_status_numeric_MEASURE1" class="java.lang.Double" calculation="Average">

<measureExpression><![CDATA[$F{folder_status_numeric}]]></measureExpression>

</measure>

<crosstabCell width="60" height="20">

<cellContents mode="Opaque" style="Crosstab_CD">

<textField pattern="#,##0.##%">

<reportElement x="0" y="0" width="60" height="20" uuid="73050814-3b1c-4949-a44d-d925bf168d4b"/>

<textElement textAlignment="Left" verticalAlignment="Middle">

<font fontName="Arial" size="11"/>

</textElement>

<textFieldExpression><![CDATA[$V{folder_status_numeric_MEASURE1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabCell>

<crosstabCell width="60" height="20" columnTotalGroup="Insertion_Time1">

<cellContents mode="Opaque" style="Crosstab_CT">

<textField pattern="#,##0.##%">

<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="bffe510e-9697-4c8f-8859-65b71f2a7590"/>

<textElement>

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{folder_status_numeric_MEASURE1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabCell>

<crosstabCell width="60" height="20" rowTotalGroup="Branch1">

<cellContents mode="Opaque" style="Crosstab_CT">

<textField pattern="#,##0.##%">

<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="71d1f9eb-2bc5-444e-88a2-212ef62f5094"/>

<textElement>

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{folder_status_numeric_MEASURE1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabCell>

<crosstabCell width="60" height="20" rowTotalGroup="Branch1" columnTotalGroup="Insertion_Time1">

<cellContents mode="Opaque" style="Crosstab_CT">

<textField pattern="#,##0.##%">

<reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="f70469f9-8f44-4d66-b634-202ba81d3ec5"/>

<textElement>

<font fontName="Arial" size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$V{folder_status_numeric_MEASURE1}]]></textFieldExpression>

</textField>

</cellContents>

</crosstabCell>

</crosstab>

 

[/code]

[elizam]

It depends a lot on the underlying data.  The average in a crosstab is not the average of the values you see, it is a weighted average across all the inputs.  So if you have 4 transactions on Tuesday with an average of 10% and 2 transactions on Wednesday with an average of 20%, it is not (10 +20)/2 = 15,  it is ((10x4) + (20x2))/(4+2)  = 13.333 (actually,  it just calculates the average over all the underlyin field values, but in terms of what you see, that is what it should equal).

If you have a constant # of transactions per day, then you would expect the average to be the same whichever way you took it.  However, if some values were null on one day, that would shift the calculation again so that one field is weighted more than another.  Ofr if there is one day where there were a diffferent number of transactions for some reason, then again you would see a difference.

[ananyachoubey18]

@Elizam Thank you for the explaination but is there any workaround for this type of situation?

[ananyachoubey18]

@Elizam Thanks for the explaination. Is there any work around for this problem?