Show only groups which match certain detail data

[petejigsaw]

I am working on a sales report where I need to show all sales made which contain products by a certain supplier. As our database stores a record for each line on an order, this is straightforward, and I have grouped the order lines by order number to show a summary of lines by the supplier for each relevant order.

However, I want to also show other lines on those orders, so I can analyse cross-selling.

So, for example, an order may have:

3 lines of Supplier A products
2 lines of Supplier B products

What I need to be able to achieve is to show on the report ONLY orders which contain lines by Supplier A, but at the same time also show within the order no. group all other order lines from that order. (i.e don't show orders where there are no lines by Supplier A)

Using simple selection criteria, I have only been able to show orders with product lines by Supplier A.......

Any assistance gratefully received.

Thanks

 

[Madawc]

You should be able to set up a formula field that will count the number of "Supplier A" order lines. Something like

If @SupplierA then 1
else 0

"@SupplierA" would be another formula field that identifies them. Or you could test directly if that is simple.

Once you have the field, you can do a summary count for the group (order). Then you can choose Report > Selection Formulas > Group to suppress those groups with a count of zero. Or selectively show or suppress extra sections for the group.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You would use a group selection formula like this:

sum({@Madawcsformula},{table.order}) <> 0

Just wanted to clarify that you must use a sum, not a count.
This assumes you are grouping on {table.order}.

-LB