Avatar of gebigler
gebiglerFlag for United States of America asked on

Crystal Reports 9 - Formula if

Example 1 works and I see "GROUP 1" and "GROUP 2" in the group headings.  

Example 2 puts them into two groups, but "GROUP 1" header is blank.  "GROUP 2" shows up when that groups starts.  The only difference between the two is example 2 has an "and" condition added.  

Example 1:
if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
   then "GROUP 1"
else
  "GROUP 2"

Example 2:
if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
  and {sp_cstTruckSched1;1.aoTruck}="1"
   then "GROUP 1"
else
  "GROUP 2"
Crystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
plusone3055

if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
 OR {sp_cstTruckSched1;1.aoTruck}="1"
   then "GROUP 1"
else
  "GROUP 2"
Mike McCracken

IN your formula both mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"  AND {sp_cstTruckSched1;1.aoTruck}="1"  have to be true.

DO you have data that meets both criteria?

If not then perhaps changing the AND to OR as suggested is what you want.

mlmcc
plusone3055

mlmccc

I was under the impression that both did not have to be true otherwise i would have put
but I could be wrong
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
gebigler

Both have to be true.  And yes, I had records that met that criteria.  The report grouped them correctly, but for some reason the "GROUP 1" heading name shows up as blank.  The field exists, but it's blank.  It's very strange.
Mike McCracken

Do you get values but the group name is blank?

mlmcc
Mike McCracken

Can you post a screen shot of the formula?

mlmcc
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
James0628

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gebigler

Hey James,

Yes, I found that null was the issue.  I don't understand why that would make the heading blank but would still group properly, but at least it works.  

Thanks all ...
Mike McCracken

It makes the heading blank because the formula doesn't return a value.  THe only thing you can do with a NULL value is test if it is NULL.

If you try any other comparison in a Crystal formula, Crystal terminates execution and the result is not predictable.  In your case it must be returning nothing.

mlmcc
James0628

mlmcc,

 The part that doesn't make sense to me is that if he has records where aoTruck = "1", then it seems like he should have gotten "GROUP 1" for those records.  IOW, there would be 3 groups.

 mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc" and aoTruck is null
 Group name is null

 mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc" and aoTruck = "1"
 GROUP 1

 Everything else
 GROUP 2


 James
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
gebigler

We only need two groups.  Group one is 'abc' and truck='1'.  Everything else falls into group 2.  

It groups correctly, but no value shows up in the group header #1.  when I add and (truck='1' and not isnull(truck)), it works.

at least it's working, even though it doesn't make much sense.  :)

thanks.
James0628

I know that you only wanted two groups.  It just seemed to me like nulls in truck would add a third group, where the formula produces a "null" result because truck is null.  I would have thought that "Group 1" and "Group 2" would still show up when truck was not null.  But, like I said, it seems like sometimes the problems caused by nulls are not as obvious, at least to me.

 FYI, the IsNull test really should be first, _before_ you do anything else with the field.  Or another option would be to go to File > "Report Options" and check the "Convert database null values to default" option (assuming that CR 9 has that option).  Then any "normal" formulas in the report will just see a space or whatever (depending on the type of field), instead of a null.

 FWIW, here's a quote from the CR 10 Help:

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.