Sum with exclusion condition

[rmduser]

I am using CR7 MR1.

I want to create a formula to sum a field but exclude values where another field meets a certain condition.

It would look like:
sum {pay_detl.total} where {pay_detl.location}<>MSMIS

I know that there is no "where" function in CR (unlike SQL) so I'd appreciate your advice. I'm a rookie at CR despite the age of our version so I would appreciate instructions with that in mind.

 

[jnujella]

you can use a running total ...where you can give the where conditon in formula..

 

[kskid]

Or you could add it to your record selection if you don't want to see it at all.

and {pay_detl.location}<> "MSMIS"

 

[lbass]

Create a formula like this:

if {pay_detl.location} <> "MSMIS" then
{pay_detl.total}

Right click on this formula (placed in the detail section) and insert a sum on it.

-LB

 

[rmduser]

Thanks very much for your advice!