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))
kelsanitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
kelsanitAuthor 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
mlmccCommented:
What is the GP formula?

Try this one

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

mlmcc
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

kelsanitAuthor 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
mlmccCommented:
You want to suppress the record when the gp is 0 or -100 or is between 10 and 100, is that correct?

mlmcc
0
kelsanitAuthor Commented:
yes sir
0
mlmccCommented:
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
kelsanitAuthor Commented:
Still did not work. I cannot figure this out.
0
James0628Commented:
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
)

Open in new window


 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.