Suppressing row based on criteria in a different row 1

[CJSilver]

I have CR 8.5.

I have a report that is used to show the status of manufacturing orders on our floor. We have two workcenters that a job goes through, 1ST END and ASSEMBLY, 1ST END is where the job starts. I want to see the jobs that 1ST END has finished but have not gone to the ASSEMBLY workcenter yet.

This is how the report looks now:

Group (Manufacturing order#)
Detail: Date Workcenter Status

This is how it looks when run:

DATE WORKCENTER STATUS
MO-28639
04/25/05 1ST END FINISHED
05/04/05 ASSEMBLY PAUSED

MO-28645
05/02/05 1ST END FINISHED

MO-28655
05/03/05 1ST END FINISHED
05/04/05 ASSEMBLY STARTED


The problem I am having is that I only want to see jobs that 1ST END has finished but have NOT been started or PAUSED by ASSEMBLY. So on this report, if ASSEMBLY would show up on the report, I don't want to see that job at all.

Based on the sample above, this is all I would want to see on the report:

MO-28645
05/02/05 1ST END FINISHED



C. Johnson

http://www.SilveradoCable.com

 

[synapsevampire]

Open the Report->Edit Selection Formula->Record and place:

(
{table.WORKCENTER} = "1ST END"
and
{table.STATUS}= "FINISHED"
)

-k

 

[synapsevampire]

Ahh, wait a sec, You mean that there are multiple rows:

Also go into the Report->Edit Selection Formula->Group and place:

count({table.WORKCENTER},{table.manufactoringorder}) = 1

-k

 

[CJSilver]

Synapsevampire,

Thank you for responding.

This is not working the way I need it to work. It's true I do only want to see jobs that are in the 1ST END workcenter and that are finished, but I only want to see those jobs that have NOT been started or PAUSED in the ASSEMBLY workcenter. Using your suggestion on my sample this is what I see:

DATE WORKCENTER STATUS
MO-28639
04/25/05 1ST END FINISHED

MO-28645
05/02/05 1ST END FINISHED

MO-28655
05/03/05 1ST END FINISHED

But all I should be seeing is:

MO-28645
05/02/05 1ST END FINISHED

The other two jobs both have been either STARTED or PAUSED in the ASSEMBLY workcenter. So I do not want to see those jobs at all.








C. Johnson

http://www.SilveradoCable.com

 

[lbass]

SV's suggestion will work if you remove the record selection formula he originially suggested, and just leave the group selection formula--as long a "1st End" is always the first record for any order.

-LB

 

[CJSilver]

lbass,

Thank you for your response,

I tried it again, with just the group selection formula and it is close but still not quite there. In my sample I didn't show all possibilites that could occur. We do have jobs that do not go to 1ST END at all, they only go to ASSEMBLY. I would not want to see these either. Here is an updated sample of what the report looks like WITHOUT the group selection criteria that synapsevampire suggested:

DATE WORKCENTER STATUS
MO-28639
04/25/05 1ST END FINISHED
05/04/05 ASSEMBLY PAUSED

MO-28645
05/02/05 1ST END FINISHED

MO-28655
05/03/05 1ST END FINISHED
05/04/05 ASSEMBLY STARTED

MO-28657
05/09/05 ASSEMBLY STARTED

C. Johnson

http://www.SilveradoCable.com

 

[lbass]

Then try the following. Create a formula {@not1stend} for the detail section:

if {table.workcenter} <> "1ST END" then 1

Then go to edit selection formula->GROUP and enter:

sum({@not1stend},{table.mfgorder#}) = 0

-LB

 

[CJSilver]

Thank you lbass,

I actually figured it out, though it was still based on information that you gave me in another post a couple months ago.

I did almost the same as what you suggested here.

Created a formula:
If {Actual.WC}='WC[R]1ST END' and {Actual.STAT_FLAG}='FINISHED'
then 0 else 1

then put in the group selection the same formula you listed above.

Thank you again for your help! You and synapsevampire have been an immense help in opening my mind to see what is possible in Crystal Reports. I know I still have a long ways to go, but with people like you two around, it makes the journey much easier.

C. Johnson

http://www.SilveradoCable.com

 

[davidwendelken]

An alternative would be to solve the problem in the underlying SQL, so that CR only sees the data it is supposed to see in the first place.

I don't know your table and column names, so I called the table "work" and made guesses as to the column names.

In v10, the SQL can be entered in the data expert under the "add command" node (as opposed to letting it build the SQL for you behind the scenes).

Here's a sample in Oracle syntax. Your mileage may vary with other databases, and you might have to convert portions to ANSI syntax.

Some databases don't appear to support the MINUS operator.

select
 work.job_number
,work.workcenter
,work.status
,work.statusdate
from 
 (select job_number 
    from work 
   where workcenter = '1ST END' 
     and status = 'FINISHED'
  minus
  select job_number 
    from work 
   where workcenter != '1ST END' 
 ) end_list
,work
where end_list.job_number = work.job_number
order by work.job_number, work.statusdate

The portion of this statement inside the parenthesis could have been set up as a view (which, after all, is nothing but a predefined query given a handy name to reference it by). The first part of that statement finds all jobs that have FINISHED at the 1ST END. We then subtract all jobs that have been at any other work center for any other reason. (You might have to tweak the where clause on this part to fit circumstances you didn't reveal in earlier posts.) The result is the list of jobs that have FINISHED at the 1ST END but haven't been anywhere else.

That list is joined to the work table to get the rest of the necessary data.

Hope this helps give you some insight on how to better use SQL. SQL is a set-based language, not a record-based language. One key to its efficient use is to learn to recognize the sets and UNION, UNION ALL, INTERSECT or MINUS them appropriately.

Depending upon the volume of data, the number in the keeper list vs the discard list, and your network connection to the database, this can be faster than bringing every record over the wire and then discarding most of them. It can also be slower, as it requires an internal database sort on the job_number to remove unwanted jobs. Sorting a bazillion records to remove a few hundred would be a bad idea. Sorting a bazillion records to remove all BUT a few hundred prior to sending across an overloaded network can be a really good idea. "It just depends."

David Wendelken

http://www.casetech.net

 

[CJSilver]

Hello David,

Thanks for your reply.

In Crystal I am using selection expert to filter out anything that was not in either the 1ST END or ASSEMBLY workcenters. We have many workcenters and I don't want to filter out jobs that hit any other workcenter besides hitting 1ST END, I only want to filter out jobs that have hit the ASSEMBLY workcenter.

We use SQL 2000, I do not think we have the minus operator. I am certainly not an expert in SQL, but I could not find MINUS in the help files.


C. Johnson

http://www.SilveradoCable.com

 

[davidwendelken]

I knew that Access doesn't have the Minus operator.

There's another way to write the query. Sort of turn it inside out and stick a NOT in front of the relevant parts.

Sorry, but brain is tired at the moment. I'll post a sample of the other way to write it later.

David Wendelken

http://www.casetech.net

 

[davidwendelken]

Here are 3 different ways to structure the SQL statement that should get identical results.

Brain is still tired, and I don't have a test database handy, so a good double-check would be prudent.

Performance may vary wildly, but I'm trying to give you SQL equivalents for a version of SQL whose limitations I don't know.

In this one, we're making a list of all FINISHED 1ST ENDs where we can't find any other entries for the job.

select
 work.job_number
,work.workcenter
,work.status
,work.statusdate
from 
 work
where work.workcenter = '1ST END' 
  and work.status = 'FINISHED'
  and NOT EXISTS
   (select work2.job_number 
      from work work2
   where work.job_number = work2.job_number
     and work2.workcenter != '1ST END' 
 ) 
order by work.job_number, work.statusdate

In this one, we're making a list of work assignments
where we find both a record for that job that's a 1ST END and FINISHED, as well as no other record.

select
 work.job_number
,work.workcenter
,work.status
,work.statusdate
from 
 work
where work.job_number IN
 (select work2.job_number 
    from work work2
   where work.job_number = work2.job_number
     and work2.workcenter = '1ST END' 
     and work2.status = 'FINISHED'
 )
and work.job_number NOT IN
 (select work3.job_number 
    from work work3
   where work.job_number = work3.job_number
     and work3.workcenter != '1ST END' 
 ) 
order by work.job_number, work.statusdate

David Wendelken

http://www.casetech.net