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"
gebiglerAsked:
Who is Participating?
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.

plusone3055Commented:
if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
 OR {sp_cstTruckSched1;1.aoTruck}="1"
   then "GROUP 1"
else
  "GROUP 2"
0
mlmccCommented:
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
0
plusone3055Commented:
mlmccc

I was under the impression that both did not have to be true otherwise i would have put
but I could be wrong
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

gebiglerAuthor Commented:
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.
0
mlmccCommented:
Do you get values but the group name is blank?

mlmcc
0
mlmccCommented:
Can you post a screen shot of the formula?

mlmcc
0
James0628Commented:
Could aoTruck be null?  Nulls can cause problems in CR formulas.  It doesn't seem like they would cause what you seem to be describing, but I think some problems they cause may be less obvious than others.  IAC, it wouldn't hurt to add a check, just in case.

if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
  and (not IsNull ({sp_cstTruckSched1;1.aoTruck})
  and {sp_cstTruckSched1;1.aoTruck}="1")
   then "GROUP 1"
else
  "GROUP 2"


 FWIW, the added () aren't really necessary, but I like having them there, to make the logic clear.

 James
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
gebiglerAuthor Commented:
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 ...
0
mlmccCommented:
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
0
James0628Commented:
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
0
gebiglerAuthor Commented:
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.
0
James0628Commented:
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.
0
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.