Solved

Crystal Reports Formula Help

Posted on 2013-06-05
10
742 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
  • 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 34

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 34

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 34

Expert Comment

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

 James
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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