a \'total\' colunm that grows upwards

[dosmo]

hi,

i want my report to do this:

date       item1    item2     total

03.01         1              1          6

02.01         1             1           4

01.01        1             1           2

 

so i need a total column that sums up 2 other columns upwards

thank you for your help

[despec]

I would imagine that something like this is best accomplished in the SQL query itself...Something like:

 

select

     date_field,

     sum(item1) as item_1_total,

     sum(item2) as item_2_total,

    sum(item1+ item2) over (partition by date_field desc) as running_total

from

    your_database

order by date_field desc

 

Hopefully this will give you the basic idea...

David

[dosmo]

hello david

thank you for your response

but i need a solution that takes the total from the previous day and adds the items from the current day

yours just gives me total of the whole report

 

[dosmo]

OK,

I summarised my items in the query and created a variable that had the "total" field  as initial expression and "$V{ITEM_COUNT}.subtract($F{ITEM_COUNT} )" as a variable expression. The variable has evaluation time set to "now" and calculation type "nothing".

so the problem is that  the first item is already subtracted from the total when the report is printed but i want the actual total to show first :(

 

Post Edited by dosmo at 08/06/2011 14:07

[despec]

select

date_field,

sum(item1) as item_1_total,

sum(item2) as item_2_total,

sum(sum(item1+ item2)) over (order by date_field asc) as running_total

from

your_database

group by date_field

order by date_field desc

===============================================================

Forgot the "group by" in the initial query, and the sum over wasn't formatted correctly...My apologies.  I tested this query using data at my office and the output shows the day total, then the running total like so:

Date                       A_Balance                      Running_Balance
====                     ========                      =============

6-1-11                    186066                             3183024

5-1-11                    192135                             2996958

4-1-11                    195705                             2804823

 

.....and so on....

 

 

David

 

 

Post Edited by despec at 06/08/2011 12:41