Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suppress group if it contains both specified detail records

Status
Not open for further replies.
Apr 11, 2008
68
GB
Hi

I have been struggling for several weeks on a report that I need to get finished, yet just can't get to work as it should. Essentially, I am trying to work out how to suppress a group based on the presence of BOTH specified values in the details section. Here is some example data, showing the groups of data as they are so far - grouped on Order No.:

Order = 45674567[Supplier = 180005 Product = WARDTREF123
Order = 45674567 Supplier = 180005 Product = CHEST12345

Order = 1234567 Supplier = 180005 Product = CHEST567809
Order = 1234567 Supplier = 0 Product = PREXMASPREF

Order = 3564896 Supplier = 0 Product = PREXMASPREF


Basically, I want to suppress the Order No. group in all cases except where there is a product by supplier 180005 AND supplier 0, but only where supplier 0's product code is PREXMASPREF. In the above item, that means that of the 3 example groups, only the middle one would still show.

I have tried all sorts of formulas to give 1 or 0 for each individual condition and then use them to create summaries, but I always end up with some anomolies.

Any helps gratefully appreciated!

Many thanks


Petejigsaw
 
Create two formulas:

//{@supplier108005}:
If supplier = 180005 then 1

//{@supplier0prex}:
If supplier=0 and
Product code=PREXMASPREF then 1

Then use a group selection formula. Go to report->selection formula->GROUP and enter:

Sum({@supplier108005},table.orderno})>=1 and
sum({@supplier0prex},{table.orderno})>=1

If you continue to have anomalies, show examples of those anomalies.

-LB
 
Thanks lbass - that works a treat! I was on the right lines, but was trying to do it via group supression, rather than as group selection.
 
I think you could have used group suppression as follows:

Sum({@supplier108005},table.orderno})=0 OR //note the change from AND to OR
sum({@supplier0prex},{table.orderno})=0

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top