Solved

Crystal Reports Formula Help

Posted on 2013-06-05
10
744 Views
Last Modified: 2013-06-10
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
0
Comment
Question by:ITworks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39224878
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
 
LVL 4

Author Comment

by:ITworks
ID: 39225127
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
 
LVL 4

Author Comment

by:ITworks
ID: 39225297
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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 39233229
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
 
LVL 4

Author Comment

by:ITworks
ID: 39233286
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
 
LVL 35

Expert Comment

by:James0628
ID: 39233342
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
 
LVL 4

Author Closing Comment

by:ITworks
ID: 39234419
Thanks so much, James.   I'll keep this as a reference for future formulas.
0
 
LVL 35

Expert Comment

by:James0628
ID: 39235597
You're welcome.  Glad I could help.

 James
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question