• Status: Solved
• Priority: Medium
• Security: Public
• Views: 54

# Crystal - Formula nesting not working

I have a crystal report where I need it to NOT show where GP in 0.00 or -100 and also not between 10-100  (formula below).  The report is still returning records where the GP=0.00 and not sure what I have wrong in the formula.

(not ({@gp} in [0.00, -100.00])) and (not ({@gp} in 10.00 to 100.00))
0
kelsanit
• 4
• 4
1 Solution

Commented:
Since you are comparing to decimal values try formatting the report values to have 6 decimal places to see if one of the numbers is really something small like 0.00002 which displays as 0.00 but doesn't equal 0.00

mlmcc
0

Author Commented:
what is strange is that when I just change to not = 0.00 it will work. It is only when I have the two conditions it is not working
0

Commented:
What is the GP formula?

Try this one

({@gp} < 10 OR {@gp} > 100) AND {@gp} <> 0 and {@gp} <> -100

mlmcc
0

Author Commented:
That did not work.  Below is a copy of the GP formula and I have also attached the report with saved data.  I extended the decimal place x8 and all 0.   You will see on the report where several items are at 0% and display

@GP = if {@sales}=0 then -100 else (({@sales}-{@actcost})/{@sales})*100
billing-errors-short-version.rpt
0

Commented:
You want to suppress the record when the gp is 0 or -100 or is between 10 and 100, is that correct?

mlmcc
0

Author Commented:
yes sir
0

Commented:
Try this in the detail suppression.  Remove it from the selection filter

({@gp} > 10 AND {@gp} < 100) OR {@gp} = 0 OR {@gp} = -100

mlmcc
0

Author Commented:
Still did not work. I cannot figure this out.
0

Commented:
Your calculation is resulting in some very small fractional numbers.  Create a formula like the following and put it in the sample report that you posted, and set the field format to display at least 5 decimal places (don't forget to set both Decimals and Rounding), and you should see some non-0 decimal places popping up:

if {@gp} <> 0 and {@gp} > -1 and {@gp} < 1 then
{@gp} * 1000000000  // That's 1 billion :-)
else
1

The 1 at the end replaces all of the "normal" values with 1, to make it easier to concentrate on the non-zero 0's.  :-)

FWIW, you could narrow down the results using something like > -.00001 and < .00001 (to exclude normal decimals like 0.5), but -1 and 1 worked fine for that test data.

So, for whatever reason, you've got some extremely tiny fractions.  I guess the first question would be: Are those results correct, or is there some kind of error in your calculations?  Assuming that those are valid, the simplest fix would seem to be to round the results.  I don't know if you need to apply rounding in any of the individual calculations (eg. sales or actcost).  To fix the immediate problem, you can use the Round function in gp.  For example, to round the result to 5 decimal places:

if {@sales}=0 then -100 else Round ((({@sales}-{@actcost})/{@sales})*100, 5)

That rounds the result of the calculation to 5 decimal places, and those tiny gp values become 0, and all of the detail lines in your sample report disappear.

If @sales includes extra decimals (so it doesn't = 0 when it should), you could round it too:

if Round ({@sales}, 5)=0 then -100 else Round ((({@sales}-{@actcost})/{@sales})*100, 5)

FWIW, I found this by first creating 3 formulas, one for each of your condition sets:

({@gp} < 10 OR {@gp} > 100)

{@gp} <> 0

{@gp} <> -100

When I put those on the report, {@gp} <> 0 was always True, even when it appeared to be 0.  Of course you had said that 0 was the problem, but I figured that I might as well check the other conditions while I was at it.  But 0 did seem to be the obvious problem, so I created the formula that I posted earlier and started multiplying by different factors of 10, until some non-0 decimals started appearing.

James

PS:

Just for fun, if you want to see all of those nasty decimal places :-), create the formula below and put it on the report:

``````Local NumberVar i;
Local NumberVar shifted_num;
Local StringVar teeny_tiny;

shifted_num := {@gp};
teeny_tiny := "0.";

if {@gp} <> 0 and {@gp} > -.00001 and {@gp} < .00001 then
(
for i := 1 to 35 do
(
teeny_tiny := teeny_tiny + Left (Split (CStr (shifted_num, "0.0"), ".") [ 2 ], 1);
shifted_num := shifted_num * 10
);
teeny_tiny
)
``````

You can change the 35 in the For loop to a larger number if you like, to display more decimals.  The precision in the version of CR that I have seems to be around 30 decimal places.  Just after that, I start getting repeated decimals -- At least in the test data in your sample report.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.