0

Need help with formula to filter based on certain conditions

Mar 15, 2017 at 11:24 PM

26

Former Member

So I have a fairly specific question. I have a table with 6 columns:

Date, Workcenter, PartNo, UnitPrice, PcsFinished, and Output (UnitPrice x PcsFinished)

If Pcsfinished is equal to 1, I sum the Output to get the "Total Output"

If PcsFinished is equal to 0, I sum the UnitPrice and call it "In Process"

Problem is, in the same day, pieces can go from "In Process" to a part of "Total Output" in the same day. A part may be in process in the morning and have line such as

3/15/17 - Cutting - 10320.1 - \$2,550 - 0 - \$0

and later in the day it gets completed so it creates another line item in our database that looks like:

3/15/17 - Cutting - 10320.1 - \$2,550 - 1 - \$2,550

Problem for me is that in my report, it shows total output as \$2,550 and total in process of \$2,550 as well, which is wrong. Once the part is complete (1), it should no longer show as part of "In Process". I tried this so far in my formula for "In Process" and it didn't work:

{TimeTicketDet.WorkCntr} = 'Cutting' and

{TimeTicketDet.PiecesFinished} = 0 and

Maximum({TimeTicketDet.PiecesFinished}) <> 1

Basically I want to sum if PcsFinished = 0, unless the max PcsFinished for that PartNo is equal to 1. I guess the part I'm missing is somehow associating the PartNo to my formula, no idea how to go about this. Any help is appreciated, thanks

Former Member

Using Crystal 2016 btw

Former Member Mar 16, 2017 at 05:34 PM
0

Anyone??????

Share
Don Williams
Jun 26 at 08:43 PM
0

changed tag to CR, you may want to report to Crystal Reports Tag

Share
Ian Waterman Jun 27 at 08:00 AM
0

Assuming there is a Time stamp on your data order entries so that completed item appears before In Process.

Group date by PartNo, suppress Group header and details, move Date, Workcenter, PartNo, UnitPrice into group footer

Create 3 Formula

@reset // place this in Partno group header

Whileprintingrecords;

Global Booleanvar Complete:=False

@Eval// place this in details

Whileprintingrecords;

Global Booleanvar Complete;

If Pcsfinished = 1 then Complete:= true

@Display// place in Group footer

Whileprintingrecords;

Global Booleanvar Complete;

Global numbervar InProcess;

If Complete = false then InProcess:= InProcess+PcsFinished;

InProcess;

This will provide a Running total of Pcsfinished

Ian

Share