asked on

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

peter57r

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.

Sue Taylor

ASKER

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}

Sue Taylor

ASKER

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

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sue Taylor

ASKER

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!!!!!

James0628

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

Sue Taylor

ASKER

Thanks so much, James. I'll keep this as a reference for future formulas.

James0628

You're welcome. Glad I could help.

James