I'm currently looking to build a report that will not list people who are on our pension scheme.
So my report criteria is to list fields, with a filter "[Benefit]<>Pension Scheme".
Problem is, we list Pension Scheme as a Benefit along with other things such as Car, Healthcare, Etc.
So people who are on the Pension Scheme show on the report becasue they have other benefits. Is it possible to cancel them out?
Thanks, Chris.
Are there multiple rows for the various benefits? In other words you might see:
Lynn Car (Expensive Luxury Brand)
Lynn Bonus (Huge and highly deserved)
Lynn Pension Scheme (to live in style at a beach locale)
etc.
So your filter only causes the third row to drop out but the other two still allow Lynn to show up in the report. If I've gotten the idea correct, here is a possible idea (did this quickly so may not have exact right steps, but hopefully enough to give the concept):
Calculate an item to use for the filter expression:
1) Create a pension data item that returns 0 or 1 so you can count up pension by person. If benefit = pension then 1 else 0.
2) Create a pension total query calculation count(pension for person)
3) Filter expression pension total = 0