I have report requirement from the user where they want to sum the amount based on certain criteria.

The criteria is based on the OBJECT_ACCOUNT and JE (Journal Entry)

If the object account of JE is in range 1000 -3999 ONLY then sum of the Amount should be 0.

If the object acount of JE is in range of 1000-3999 and the object account of the same JE is in the range of 4000-9999, then SUM the amount only for the range of 4000-9999 and don't list the JE amount for range 1000-3999.

For example:

JE Object Account Amount
11223344 1000 $100
11223345 2000 $200
11223346 4001 $ 20
11223346 5001 $300
11223346 1234 & 10

Looking at the data above.

We want to see the 11223344 & 11223345 as 0 since that JDE does not exist in the range of 4000-9999.

We want to SUM the following since it is in the range of 4000-9999 and it is also listed in the range of 1000-3999. (note, again we only want to sum the amount which is in the range of 4000-9999.

11223346 4001 $ 20
11223346 5001 $300

The final report will be as follows:

JE Object Account Amount
11223344 1000 $0
11223345 2000 $0
11223346 4001 $320

Is it possible?

Thanks,

This message has been edited. Last edited by: Kerry,

You need to create a DEFINE with IF,THEN,ELSE logic of what you want.