Good Noon All,
My requirement is to get sum of a array field.. Here bptmm111.hrea is the array field.. If my input cprj is s010001c and it is the index field of main table ltrpd300 and it has another index cact..
In table ltrpd300 In table bptmm111
For cprj:S010001C cprj:s0100001c
Cact is:01 cact :01
02 02
03 03
I want sum of all the records which matches with the main table ltrpd300.
i.e if for 01 hrea is 12 then for 02 hrea is 12 then for 03 hrea is 2 then on the whole normal should be 26

Here is my script:

function extern summary()
{ | normal = 0
| othrs = 0
select ltrpd300.*
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.loca inrange {:loca.f} and {:loca.t}
selectdo
normal = 0
othrs = 0
select sum(ltrpd300.rhrs):rccp , sum(ltrpd300.bhrs):book
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.wtyp = ltrpd.wctype.labour
selectdo
ltrpd300.rhrs = rccp
ltrpd300.bhrs = book
varhrs = book - rccp
varper = varhrs/rccp*100
select bptmm111.hrea,bptmm111.cact,bptmm111.cprj,bptmm111.chlt
from bptmm111
where bptmm111.cprj = :ltrpd300.cprj and
bptmm111.cact = :ltrpd300.cact
selectdo
if bptmm111.chlt = "REG" then
normal1 = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)
message(str$(normal1))

normal = normal1 + (bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8))
message(str$(normal))
else
if trim$(bptmm111.chlt) = "OT" then
othrs = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)

othrs = othrs + (bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8))
endif
endif
endselect
endselect
endselect
brp.ready(brp_id1)
}


But its giving the last record value i.e 12+12 =24
Am not getting the whole sum value...
please help...

My first thought was you are reading from the same table twice, so I would recommend using an alias for the second query - even though you are summing a couple of fields.

Then the more I looked at what you are doing the more confused I became. It looks like you are getting a range of records, then trying to sum individual records. But you reset normal hours(for example) each time you execute the outer query. The way I look at it if you want the report to have project, location(assuming that is what they represent. Then you would move the brp.ready inside the query right before the last end select.

So I speculated on what might be wanted. Just a wild ass swag.

function extern summary()
{ | normal = 0
| othrs = 0
select ltrpd300.*
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.loca inrange {:loca.f} and {:loca.t}
selectdo
| Init sums
rccp = 0
book = 0
normal = 0
othrs = 0
| This sum would return only 1 record for the complete project range as you
| had it. | Is that what is wanted - or do you just want the project from the
| above query. I assumed you wanted only one project since that
| is what is in the next query's select statement and the first query finds
| a complete range.
select sum(a.rhrs):rccp , sum(a.bhrs):book
from ltrpd300 a
where a._index1 = :ltrpd300.cprj
and a.wtyp = ltrpd.wctype.labour
selectdo
endselect
| No clue what these would do.
| ltrpd300.rhrs = rccp
| ltrpd300.bhrs = book

varhrs = book - rccp
varper = varhrs/rccp*100

|Now get normal and ot hours. Not sure why you add stuff twice, but my
|sepeculation is
select bptmm111.hrea,bptmm111.cact,bptmm111.cprj,bptmm111.chlt
from bptmm111
where bptmm111.cprj = :ltrpd300.cprj and
bptmm111.cact = :ltrpd300.cact
selectdo
if bptmm111.chlt = "REG" then
| Normal1 contains current record's hours if chlt = REG
normal1 = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)
message(str$(normal1))

| Normal contains the total for all records found.
normal = normal + normal1
else
if trim$(bptmm111.chlt) = "OT" then
| othrs1 contains current record's hours if chlt = OT
othrs1 = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)

| Othrs contains total for all records found.
othrs = othrs + othrs1
endif
endif
endselect
| Print project, location othrs, normal, varhrs, variation percent.
brp.ready(brp_id1)
endselect
| brp.ready(brp_id1)
}

In this iteration I assumed you only wanted to print one record for the complete project and location range. In this case you could move the one sum outside the first query - you could remove the alias, but since I was cutting and pasting I left it in. Now my swag looks like below - notice the variable init is outside the queries now for the total normal and ot hours.



function extern summary()
{
rccp = 0
book = 0
normal = 0
othrs = 0


|Assuming you want only one record to go to the report
select sum(a.rhrs):rccp , sum(a.bhrs):book
from ltrpd300 a
where a._index1 inrange {:cprj.f} and {:cprj.t}
and a.wtyp = ltrpd.wctype.labour
selectdo
endselect
varhrs = book - rccp
varper = varhrs/rccp*100


select ltrpd300.*
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.loca inrange {:loca.f} and {:loca.t}
selectdo

|Now get normal and ot hours. Not sure why you add stuff twice, but my
|sepeculation is
select bptmm111.hrea,bptmm111.cact,bptmm111.cprj,bptmm111.chlt
from bptmm111
where bptmm111.cprj = :ltrpd300.cprj and
bptmm111.cact = :ltrpd300.cact
selectdo
if bptmm111.chlt = "REG" then
| Normal1 contains current record's hours if chlt = REG
normal1 = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)
message(str$(normal1))

| Normal contains the total for all records found.
normal = normal + normal1
else
if trim$(bptmm111.chlt) = "OT" then
| othrs1 contains current record's hours if chlt = OT
othrs1 = bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8)

| Othrs contains total for all records found.
othrs = othrs + othrs1
endif
endif
endselect
endselect
| Print 1 record for the project and location range.
brp.ready(brp_id1)
}


Using the combination of these two you should find something to point you in the right direction.

Thank you mark for understanding my requirement properly...
ltrpd300.rhrs - i used in ssrs to get the sum of ltrpd300.rhrs i.e sum(ltrpd300.rhrs):rccp
ltrpd300.bhrs - to get sum of bhrs i.e sum(ltrpd300.bhrs):book

At last i wrote my query as:

function extern summary()
{
select ltrpd300.*
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.loca inrange {:loca.f} and {:loca.t}
selectdo
select sum(ltrpd300.rhrs):rccp , sum(ltrpd300.bhrs):book
from ltrpd300
where ltrpd300._index1 inrange {:cprj.f} and {:cprj.t} and
ltrpd300.wtyp = ltrpd.wctype.labour
selectdo
ltrpd300.rhrs = rccp
ltrpd300.bhrs = book
varhrs = book - rccp
varper = varhrs/rccp*100
select bptmm111.hrea,bptmm111.cact,bptmm111.cprj,bptmm111.chlt
from bptmm111
where bptmm111.cprj = :ltrpd300.cprj and
bptmm111.cact = :ltrpd300.cact
selectdo
if bptmm111.chlt = "REG" then
normal = normal + (bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8))

else
if trim$(bptmm111.chlt) = "OT" then
othrs = othrs + (bptmm111.hrea(1)+bptmm111.hrea(2)+
bptmm111.hrea(3)+bptmm111.hrea(4)+
bptmm111.hrea(5)+bptmm111.hrea(6)+
bptmm111.hrea(7)+bptmm111.hrea(8))
endif
endif
endselect
endselect
endselect
brp.ready(brp_id1)
}