sum(average)

[2005 IR Help]

By: Adrien Lemoine - maizie

sum(average)

2004-03-29 13:52

Hello,

 

I'm facing the following problem:

I have a report with two groups. For the nested group, I calculate an average on a numeric field and for the higher level group I need to perform a sum on the averages previously calculated.

For that, I created a variable for the average values, let's say $V{avg_group1} with $F{MY_NUMERIC_FIELD} as the expression and this works fine. For the sum, I created a variable $V{sum_group2} which uses $V{avg_group1} as the expression.

And this doesn't work. The tool simply calculates the sum of all records in the result set for the group2, as if the avg_group variable was replaced directly with MY_NUMERIC_FIELD. Or, I need something like sum(avg(MY_NUMERIC_FIELD)).

 

What should I do?

 

Thanks,

Adrien

 

 

 

 

By: Adrien Lemoine - maizie

RE: sum(average)

2004-03-30 23:38

It seems like a difficult difficult question.

 

I would like to know however if it is possible to use imbricated aggregate functions with jasper reports in order to know quickly if I continue to use it or not.

 

Thank you.

 

 

 

 

By: David Lim - aberrant80

RE: sum(average)

2004-03-31 00:29

Hi, I'm not really sure, but you could try this instead for your sum_group2.

 

<variable name="sum_group2" class="java.lang.Double" resetType="Group" resetGroup="group1">

<initialValueExpression>

new Double(($V{sum_group2} == null) ? 0.0 : ($V{avg_group1}.doubleValue() + $V{sum_group2}.doubleValue()))

</initialValueExpression>

</variable>

 

I don't know if it'll work and I don't know if it's a bad hack, it's just what I can come up with. Of course, this means that you'd have to declare your avg_group1 variable above this and initialised to 0.0.

 

 

 

 

 

By: Adrien Lemoine - maizie

RE: sum(average)

2004-03-31 05:00

Hello,

 

Thank you for your answer.

 

I tried your suggestion but it doesn't work.

As I said in my first post, it seems that when we use a nested aggregate variable into another aggregate variable, the nested one is directly replaced with the current field value to which it refers instead of being replaced by the result of the calculation.

So, if I write $V{avg_group1} into the $V{sum_group2} variable (initialValueExpression included), than the last value of the $F{MY_NUMERIC_FIELD} is used instead of the result of the result of the avg function.

 

One solution would be to use scriptlets, but I must recognize that for such a simple operation it sounds like a big hack. But the variety of fired scriptlet events is very poor and we should have at least one event fired after all variables of a group were evaluated and are ready to be used...

 

Adrien

 

 

 

 

By: Carlos Costa e Silva - carloscs

RE: sum(average)

2004-03-31 20:07

What did you put in the resetGroup and resetType attributes of the variables?

 

And in the evaluationGroup and evaluationTime of the textfields?