?
Solved

Crystal Reports Formula Help

Posted on 2013-06-05
10
Medium Priority
?
748 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Accepted Solution

by:
James0628 earned 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

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 create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

801 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