CODEWITHSUNDEEP
jasper-reportsireport
LapinLove404
LapinLove404

Reputation: 1949

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?

Upvotes: 3

Views: 13349

Answers (2)

Pramod
Pramod

Reputation: 61

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

Upvotes: 0

Aaron
Aaron

Reputation: 574

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>

Upvotes: 7

Related Questions