Crystal reports suppress using a where statement 1

[postmanplod]

Hello,

I have the below data in a report (dates in dd/mm/yy), grouped by job:

Job: 123
OP SEQ Desc: Type: Date
20 Stamping 01/02/11
30 CNC 01/03/11
40 Hand Dress 01/04/11
50 Machining Sub Contract 01/05/11
60 Inspection 01/06/11


What statement would I use to only show Op Seq's 20,30,40 and 50? I.e. Hide
anything after the sub contract. Basically I want to say if the date is greater
than an op that is a sub contract, then suppress - but how do I actually write
this?

Regards,

Michael

 

[lbass]

Create a formula {@subdt} like this:

if isnull({table.type}) or
{table.type} <> "Subcontract" then
date(0,0,0) else
{table.date}

Then go into report->selection formula->GROUP and enter:

maximum({@subdt},{table.job}) = date(0,0,0) or
{table.date} <= maximum({@subdt},{table.job})

This would show all rows where there is no subcontract, but only rows with equal or earlier dates if there is one, assuming you are grouping on the job field.

-LB

 

[postmanplod]

Thanks for this - how could I adjust it so it was dependent on the Opr Seq instead of the date? I've noticed that some the Sub Cons don't have a date or have the same date as an operation below it.

So how could I say suppress if the opr seq is greater than the one that is sub con type?

 

[lbass]

if isnull({table.type}) or
{table.type} <> "Subcontract" then
0 else
{table.opseq}

maximum({@subdt},{table.job}) = 0 or
{table.opseq} <= maximum({@subdt},{table.job})

This assumes that the Op Seq field is of number datatype.

-LB

 

[postmanplod]

I've hit a brick wall whereby the user wants to hid any jobs where after the filtering, there is only 1 sub op dispalyed and nothing else - how do I hide this?

Any ideas?

Regards,

Michael

 

[postmanplod]

So in the example at the top, how would I suppres if only Opr Seq 50 was visible?

 

[IanWaterman]

Not sure If I have missed something, but why can't you just filter data in select expert.

{table.type} = "Subcontract"

Only records with that value will show in report.

Ian

 

[postmanplod]

Ah yes, thanks but I still need sub contracts to show if there are operations before it, as per the example at the top. I have operations after it hidden, which I want (using the help from lbass), but now how do I just hide where only one line appears that is a sub con?

 

[lbass]

Insert a running total {#cntwingrp} that does a count of {table.opseq}, evaluates for each record and resets on change of group: {table.job}. Then go into the section expert->details->suppress->x+2 and enter:

{#cntwingrp} = 1 and
{Table.type} = "Subcontract"

-LB

 

[postmanplod]

Yes, this has worked!!

Thank you so much for the help.

 

[postmanplod]

Ah, one other problem.

I now have a situation where for some jobs I have only two sub ops remaining - how would I hide these but still taking all the above into consideration?

 

[lbass]

Try this instead. Create a formula like this and place it in the detail section:

whileprintingrecords;
booleanvar flag;
if {#cntwingrp} = 1 and
{Table.type} = "Subcontract" then
flag := true;

Add a reset formula to the job group header:
whileprintingrecords;
booleanvar flag;
if not inrepeatedgroupheader then
flag := false;

Then use a formula like this to suppress the detail section:
whileprintingrecords;
booleanvar flag;
flag = true; //note no colon

-LB

 

[postmanplod]

No change I'm afriad...

 

[lbass]

Please show a sample of the current display. Also please explain exactly how you implemented my suggestion.

-LB

 

[postmanplod]

Ok, I think I'm getting there. How would I code "is there a record before me?" If I can do that, I can do an IF statement on the result.

 

[lbass]

Please explain what you have done that you think this will correct. If you identify a record as first in a series, then there is no record before, and that is essentially what my most recent solution was doing, so I'm wondering what didn't work for you. It may be that my formula just need tweaking.

-LB

 

[postmanplod]

I can suppress the detail section fine, it's suppressing the group containing the Job Num that is the problem. For example, using all the examples above, I have been able to suppress records in the detail section, however, the Job Num group header is still displaying even when they are no records to show in the detail section.

 

[lbass]

You should be able to suppress that with:

{Table.type} = "Subcontract"

...since the group header will read the first record in the group.

-LB