Compare each detail Row to overal Average Value

[barry_4]

Hi,

I have a report in whiuch I want to be able categorise results based on how theyt relate to the average score for that (set of) values.  

These are to be displayed in the detail section with a Above/Below flag for the field in question.

As an example: I have a 'cost' field in the fiueldset from MySQL.  

I defined 'avg_cost'  and avg_importance as

<variable name="Avg_Cost" class="java.math.BigDecimal" incrementType="Report" calculation="Average">  <variableExpression><![CDATA[$F{Cost}]]></variableExpression> </variable> [/code]

<variable name="Avg_Importance" class="java.math.BigDecimal" calculation="Average">  <variableExpression><![CDATA[$V{Importance}]]></variableExpression> </variable>[/code]

I the 'detail band'  - I have :

<textField>    <reportElement x="486" y="0" width="69" height="48" uuid="b117ddb5-ac24-4dff-a17e-d4957d2fd7cc"/>    <textFieldExpression><![CDATA[$V{Avg_Cost}]]></textFieldExpression>   </textField>[/code]

This gives me a rolling average. Rather than the average of all.

Ultimately I wish to implement the following logic.

As well as Cost, I have a variable that is used to calculate Importance. (Imp) 

For all Record where Imp >= Imp.avg ( if cost < cost.avg => 2 else 3 )

For all Record where Imp < Imp.avg (if cost < cost.avg => 3) 

(ignore the rest)

I've been using JasperReports for about a week, btw.  SQL since 1984.

 

THanks in advance, stay well

Barry

 

[hozawa]

The best way is to add a column in your table to distinguish which group the record belongs.

You may be able to nest select statements to find the average and to group using the "case" statement but this will slow down report execution.