Crystal Reports Formula Help

I'm having problems filtering out certain data within my report.  I'm sure there's a few different ways to handle this, but each one that I try, I get stumped because of my lack of knowledge.  I'm trying to filter OUT items that have a sequence number of 1 AND @AllZeroes = 0.

isNull({p21_view_process_x_transaction.completed_date}) and
{p21_view_process_x_transaction_detail.stage_cd} in ["4HEADCT", "BROACH", "C'DRILL", "CENTERDRILL", "CHAMFER5", "CHAMFORGE", "DE-FLASH", "DRILL3", "GRIND3", "HEATSWELL", "HT", "KNURL", "MACH", "REMSTAMP", "RT-ROUND", "SAW5", "STAMP5"] and
{p21_view_process_x_transaction_ext.location_id} = 601 and
{@QtyRem} > -1

My original report had the select expert as it is listed above with the exception of {@QtyRen} was greater than 0.   But this would leave off jobs that were not started yet.  I don't have a field within any of the tables for the "Started Qty" so I created a formula called AllZeros that basically tells me that the job has not been started.  

Then I changed my record selection to show @QtyRem > -1 to display jobs that have not been started.

Now I need to display only jobs that have not been started where the sequence no is 1 and the AllZeros = 0

If I run the report with @QtyRem at >0 then I get 59 records.
If I run the report with @QtyRem at >-1 then I get 362 records.  Of the 362 records roughly 20 of them have a sequence no. of 1 AND have not been started.  I want to be able to display those roughly 20 records and the 59.

I hope this make sense!
Machine-Shop-Production-Schedule.rpt
LVL 4
ITworksAsked:
Who is Participating?
 
James0628Commented:
In your first post you said that @QtyRem > 0 gave you 59 records, and you want to include those records, and then add approx. 20 records with @QtyRem > -1 and sequence # 1 and "not started".

 Since @QtyRem > 0 is apparently giving you a different set of records, I assume that @QtyRem > -1 really means @QtyRem = 0 ?  IOW, you don't really want to include _everything_ > -1 in that group, because > 0 is included in the first group.  It may not matter, depending on your data and formula, but I'd rather be as precise as possible.

 Do the sequence # 1 and "not started" tests apply to the @QtyRem > 0 records too, or just @QtyRem = 0 ?  I am assuming just @QtyRem = 0.

 You have an @IsStarted formula that appears to be doing the sequence # 1 and "not started" tests.

 Using the record selection formula from the report that you posted, and the conclusions/assumptions above, this may be what you want:

isNull({p21_view_process_x_transaction.completed_date}) and
{p21_view_process_x_transaction_detail.stage_cd} in ["4HEADCT", "BROACH", "C'DRILL", "CENTERDRILL", "CHAMFER5", "CHAMFORGE", "DE-FLASH", "DRILL3", "GRIND3", "HEATSWELL", "HT", "KNURL", "MACH", "REMSTAMP", "RT-ROUND", "SAW5", "STAMP5"] and
{p21_view_process_x_transaction_ext.location_id} = 601 and
(
{@QtyRem} > 0 or
({@QtyRem} = 0 and {@IsStarted})
)


 James
0
 
peter57rCommented:
By..
  "I'm trying to filter OUT "
do you mean you want to Exclude such records or Include such records

Your report has no formula @AllZeros

Are the conditions "sequence number of 1 AND @AllZeroes = 0." to be applied as well as your other conditions or instead of your other conditions.
0
 
ITworksAuthor Commented:
Yes, I want sequence number 1 and @AllZeros = 0 to be applied as well as my other conditions.

When I'm back at work, I'll copy my formula for AllZeroes but it something like:

{allocated qty} + {finished qty} + {lost qty}
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ITworksAuthor Commented:
AllZeros formula is:

{p21_view_process_x_transaction_detail.qty_in} + {p21_view_process_x_transaction_detail.qty_out} + {p21_view_process_x_transaction_detail.qty_lost}
Machine-Shop-Production-Schedule.rpt
0
 
ITworksAuthor Commented:
I'm pretty sure this worked, James.  I tried it from home via LogMeIn to my work computer....can't see too great this way and really need to print out the report and review it with our ERP system.

I see now how you put this information in the bottom of the formula (
{@QtyRem} > 0 or
({@QtyRem} = 0 and {@IsStarted})
)

I was trying to put all of these extra parenthesis and including the other part of the formula with it.....I thought that I had to start off with an open parenthesis in the very beginning of the formula.

I'll check this out 100% tomorrow morning when I get into work.

Thanks!!!!!
0
 
James0628Commented:
You could repeat the first conditions with each of the new tests, but if the first conditions always apply, you can leave them separate, which is what I did.  IOW, you could use

([other tests] and {@QtyRem} > 0) or
([other tests] and {@QtyRem} = 0 and {@IsStarted})

   or

[other tests] and
(
{@QtyRem} > 0 or
({@QtyRem} = 0 and {@IsStarted})
)

 The results should be the same either way.  The second form is just simpler.

 James
0
 
ITworksAuthor Commented:
Thanks so much, James.   I'll keep this as a reference for future formulas.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
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.

All Courses

From novice to tech pro — start learning today.