cant use record selection - to show all orders with no red products

[japeconsulting]

I have a report where I need to show all orders which do not contain a "red" product ordered. I cannot use the record selection to limit the report where product<>red because that will just show me the lines that are not red,

eg. Order 1 contains 4 lines - red, blue, green and yellow
Order 2 contains 3 lines - blue, green and yellow

I want a report to only show order number 2 on my report because that does not contain red. However if I use the select expert to put that selection in it will still show 3 lines from Order number 1 as well, the blue, green and yellow ones.

Hope this makes sense. Please help as it is driving me mad!
Jackie

 

[Madawc]

Create a formula field:

If {your.field} = "Red" 
then 1
else 0

Do a summary count of @CountRed for each order: I assume you group products by order. It should be zero when there is no 'red' product.

You can then use Report > Selection Formulas > Group to suppress all orders where @CountRed is greater than zero.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

This is based on Crystal 11. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


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

 

[IanWaterman]

Group report by order number

Create a formula
@red
If product = 'red' then 1 else 0

In Group selection

sum(@red, orderno) <> 0

If you want to do any summaries on data you will need to use a Running Total, as data is still in reoprt but suppressed from view.

Ian

 

[japeconsulting]

Hi Ian and Madwac
Thanks so much for your replies. Sometimes you have to go back to basics I was making it much too complex!