I am trying to get the sum of a field (timps30.demf) for a range of plan codes and plan levels. This sum is to print by plan date (timps300.pdat)

When I try to get sum in the session script, the values on the report are not sums

This is what I have

functions:
function read.main.table()
{
select timps300.*, timps100.*, timps200.*
from timps300, timps100, timps200
where timps300._index1 inrange {:plnc.f, :plvl.f, :plni.f,
:plct.f, :pdat.f}
and {:plnc.t, :plvl.t, :plni.t, :plct.t, :pdat.t}
and timps300.plnc refers to timps100
and timps300._index1 refers to timps200
and timps200.seak inrange :seak.f and :seak.t
order by timps300.pdat
selectdo
select sum(timps300.demf)
from timps300
where timps300._index1 inrange {:plnc.f, :plvl.f, :plni.f,
:plct.f, :pdat.f}
and {:plnc.t, :plvl.t, :plni.t, :plct.t, :pdat.t}

selectdo
rprt_send()
endselect
endselect
}

Itannous

You need a group by statement with timps300.pdat in it and also a variable bound to the sum(...)
domain timps.qpdl g.demf | outside the sql

select sum(timps300.demf):g.demf
from timps300
where timps300_index1 = {:...}
group by timps300.pdat
selectdo
endselect

Is it possible to do the sum in the outer sql loop ? If not then the inner loop should probably use an alias for the table
such as .... from timps300 mps


Hope this helps

Terry

Terry,
You are right. You are better off using a varibale to hold the sum.
I think there is a space after the (sumtablenamefieldname) and
before the varibale.
Also, you may just give the date range in a AND clause without the need for having/group by.
Make sure the variable is initialized each time you get into the inner loop.you may want to send the data out to report
each time if you like that.

The session will not run after I created run time.

This is what I uderstood from your email(s).

functions:

function read.main.table()
{
domain timps.qpd1 g.demf

select timps300.*, timps100.*, timps200.*
from timps300, timps100, timps200
where timps300._index1 inrange {:plnc.f, :plvl.f, :plni.f,
:plct.f, :pdat.f}
and {:plnc.t, :plvl.t, :plni.t, :plct.t, :pdat.t}
and timps300.plnc refers to timps100
and timps300._index1 refers to timps200
and timps200.seak inrange :seak.f and :seak.t
order by timps300._index1
selectdo

select sum (timps300.demf) :g.demf
from timps300 mps
where timps300._index1 inrange {:plnc.f, :plvl.f, :plni.f,:plct.f, :pdat.f}
and {:plnc.t, :plvl.t, :plni.t, :plct.t, :pdat.t}
selectdo
rprt_send()
endselect
endselect
}

Ltannous,
I think the problem was not very clear at first.
I understand that you need to sum a p'lar field for each date.
If this is correct,there is no need for a variable and no need for the second select.
just use the first select and the rprt_send() in the selectdo
In the report layouts define a before field for the date field
(you will have to define it as sort field in the report input fields
first) and an after field:
In the after field section of that field,go to the field attributes
and select the "TOTAL" aggregate function and you are all set.
Compile the report.
Note:You can use a variable if you wish to sum up in the program script but in that case it is difficult if the records are not
sorted by date.SO STICK WITH THE REPORT TOTAL function here.