not able to Summurarize the grand total

[saradaga]

I have the data as follows

"code" "id" "amt"
SSIPP 99995 100
W2TAX 99994 500
PS 99994 500
ES 99994 500

i want to summurise on the field "amt" and get the total 600 with respect to id.
but iam getting 1600. how do i write the formula.
I have to place the grand total 600 in the page header as state grand total.

any help is appreciated.
Thanks.

 

[lbass]

Is that a typo where the ID ends in 5? Which row showing the value of 500 should be added to the value of 100? You can write a formula like this {@somecodes}:

if {table.code} in ["SSIPP","W2TAX"] then {table.amt}

Then create a second formula to place in the page header:

sum({@somecodes})

-LB

 

[saradaga]

thanks Ibass. That was not a Typo. My data is as follows

"district" "county" "office" "au_id" "Ver_cod" "amt"

DISTRICT4 BEDFORD DDI6 OFFICE 99995 SSIPP $100
DISTRICT1 ANDERSON CASPER OFFICE 99996 W2TAX $500
DISTRICT1 ANDERSON CASPER OFFICE 99996 EARNS $500
DISTRICT1 ANDERSON CASPER OFFICE 99996 BNKF $500

I have done grouping on district, county, office, au_id. au_id is unique. 99995 has 1 "ver_cod" i.e, SSIPP.And 99996 has 3 "ver_code" W2TAX, EARNS, BNKF. Because "Ver_cod" has different values , all other fields are repeating. The "amt" is with respect to "au_id". I need groupwise total and STATE GRAND TOTAL that need to be placed in the PAGE HEADER.I used RUNNING TOTAL to get groupwise totals. But for the STATE GRAND TOTAL I am getting "1600" instead of "600".
Please advise me to write a FORMULA to get the STATE GRAND TOTAL to keep it in PAGE HEADER.
OR is the data wrong as I wrote a VIEW on Database side.

Thanks.

 

[lbass]

You might want to change your view to include the maximum amount per your groups. Then you would be able to write a formula that summed these:

sum({view.maxamt})

-LB

 

[saradaga]

Thanks Ibass.
I did it in the following way and it worked.
I wrote command in the command objects as follows,
SELECT COUNT(FF006_VIEW.AU_ID),FF006_VIEW.AU_ID
FROM FF006_VIEW
GROUP BY FF006_VIEW.AU_ID

I linked this command to the view (FF006_VIEW.AU_ID --> Command.AU_ID) . I have put the INNER JOIN and ENFORCED BOTH options while joining.
Then I used the formula
WHILEREADINGRECORDS;
NUMBERVAR GG := {FF006_VIEW.INTERIM_ASST_AMT}/{Command.COUNT(FF006_VIEW.AU_ID)}

I got the state summary by using sum(ABOVE FORMULA). I got the groupwise sum by inserting the summary on the
ABOVE FORMULA.
Just wanted to share this with you.
Thanks a lot.
Many People are surviving in the job because of people like you helping.

 

[HowardHammerman]

Why not just use a running total?
Make sure that you either sort or group on the ID field and then in the running total either:
1) accumulate once per group (the ID group)
or
2) accumulate once per change of the field ID.

Reset never.

Keep it simple.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

Except a running total would be incorrect if placed in the page header.

-LB