Solved

Crystal Reports Formula Help

Posted on 2013-06-05
10
739 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Author Comment

by:ITworks
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much, James.   I'll keep this as a reference for future formulas.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
How to increase the row limit in Jasper Server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now