Conditional Sum (variable)

A report in iReport (4.0.1) with various fields includes: $F{value} (Integer) and $F{created_at}.

I'd like to calculate variables that would give:

1. the sum of $F开发者_运维问答{value} when $F{created_at} is before a given date
2. the sum of $F{value} when $F{created_at} is after a given date

Any idea how this could be done?

You will have to use two different variables to do this. For your variables, use something like this in the 'Variable Expression'. The Date class also has an after() function. If the expression evaluates to true $F{value} will be added, otherwise 0 will be added.

$F{created_at}.before($P{givenDate}) ? $F{value} : 0

To use a variable to sum, you need to change the calculation type to "Sum". The default reset type, report will sum values over the entire report. The other reset types work the same way just over different sections of the report (column, page or group).

Here is the XML for the "before" case:

<variable name="sumValueCreatedBefore" class="java.lang.Integer" calculation="Sum"> <variableExpression><![CDATA[F{created_at}.before($P{givenDate}) ? $F{value} : 0]]></variableExpression> </variable>

there is another solution for that : write sub query in the select statment

like

Select (select sum(Fieldname) from tablename where dategiven date) as aftersum

from tablename where conditions