Count or Sum one field

[impalacrazy]

I've been stairing at the computer all day and the solution may be right under my nose but i cant see it so hopefully someone else can. here is my issue:

I have one table that has multiple lines for one employee becasue each type of benefit an employee has is considered its own line. (example of table data)

emp# plan type emp contribution
1602 401K 6.8 %
1602 401M 13.2 %
1602 DBC1 $ 136.52
1602 HSEE $ 33.95
1733 401K 7 %
1733 DBC1 $ 453.77
1733 LTD3 $ 16.28
1922 401K $ 280.00
1922 401M $ 620.00

now what i am trying to do is take what is in the 401K emp contribution field and what is in the 401M emp contribution field and produce a total number. Here is what i have in my formula thus far:

if {BENEFIT.PLAN-CODE} startswith '401'
and {BENEFIT.PCT-AMT-FLAG} = 'P' then
{BENEFIT.EMP-CONTRIB}

now i am currently trying to take this formula and use it in a new one that will display the total of the two records in one field. is this even possible or am i wasting time...

thanks

Ivan

 

[lbass]

You should be able to simply place your formula in the detail section and insert a sum on it at the group or report level. If you want to place the sum in some other section, e.g., details, you would have to write a formula like this:

sum({@yourformula},{table.employee})

...if you group on the employee field, or:

sum({@yourformula})

...if you want to show the sum for the report as a whole.

-LB

 

[impalacrazy]

I tried both. If i sum the formula in the detailed section is says that i am trying to sum a field that is not always populated (or something to that extent). if i sum the formula itself i get the return of a value 19,869.61 for all rows. even when i try to do a rounding on the field it does not give me the correct amount. for example if the two plans are 6.8 an 13.2 it should have a sum of 20

 

[lbass]

What happens if you go into file->report options and check "convert nulls to default values"?

Note that if you want the sum per some group, you need to add the group condition to the sum formula:

sum(amount,groupfield)

-LB