report select problem (using sample DB)

[Undecided]

Using the Xtreme sample database, I need to display all Customers that have ordered a 'Rapel' and 'Mozzie' on the same order. I am grouping by Orders.CustomerID and then by Orders.OrderID with the Product.Product Name in the Details section. What is the formula I need to use in the Select Expert? Using the following :-
{Product.Product Name} = 'Rapel' or
{Product.Product Name} = 'Mozzie'
Returns all the Orders where either\both were ordered and replacing the 'or' with 'and' returns nothing (no surprises there). Thanks for reading this far, a response will be even more appreciated.

 

[dgillz]

Create 2 formulas as follows:

if {item} = "Rapel" then 1 else 0
If {item} = "Mozzie" then 1 else 0

Summarize these by Order Number. The in the group selection formula put the following:

Sum((Formula1},{OrderNumber})>0 and
Sum((Formula2},{OrderNumber})>0

This will return only those orders where BOTH items are ordered on the same order.

Please post again if you have any questions.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[mbarron]

The way to do this is to insert a "Distinct Count" summary for your product name. The summary would be in the Orders.OrderID group. You would then use the select expert for your group selection to have the distinct count of 2.

The formula for Distinct count:
DistinctCount ({Product.Product Name}, {Orders.Order ID}) = 2 Mike

 

[BlurredVision]

Write up two formulas..

One

IF {Product.Product Name} = 'Rapel' Then 1.00

Second
IF {Product.Product Name} = 'Mozzie' Then 1.00
Place those in you details section.

Sum both of those formulas and place in the group footer.
Use the select expert and select those sums where they are >= 1.00

Might be an easier way, but this works...

Hope this helps. Brian

http://www.crystalinfo.net

 

[Ngolem]

Mbarron - I like your approach the best....but summary operations are not allowed in record selection

ok...my kick at the can

First of all you cannot do this selection in the Select Expert 100%. All you can do is narrow down the search to those 2 product items. This is because each product is on a separate record in the database.

(BTW: you can make your report more general by having those items entered by the user through parameters)

So in the record selection formula you would have, as you pointed out, the formula

{Product.Product Name} = "Rapel" or
{Product.Product Name} = "Mozzie"

now you must do your Grouping as you have done ...by CustomerID and OrderID

I would do the final selection by Distinct count now as suggested by Mbarron

I would have Group 1 header with the customer information, Group 2 header and the detail section suppressed, and the Group 2 footer conditionally suppressed in the Section Expert with the formula

Whileprintingrecords;
DistinctCount({Product.Product Name},{Orders.Order ID})= 2;

group 1 footer suppressed
Jim Broadbent

 

[mbarron]

Jim,
The select expert can be used for summary operations. If you click on the summary operation field and then click the Select Expert icon it will bring up the select expert where it will allow the evaluation of the summary. The tab will be Distinct Count of ... You can use this the same way as the record selection expert.


If you click the Show Formula button it will give you the option of viewing the Record Selection or Group Selection formula.

Mike

 

[Ngolem]

my apologies MBarron....your method works well...

I don't usually use Group selection formulas and I see when trying to do this that the selection expert automatically threw the selection criteria into the group select formula.

this would be evaluated on the second pass but I can see many uses for this...I should have read your post more carefully to see that it refered to GROUP not RECORD selection.

the only thing I would say though ( in my defence ) is that you should have mentioned that the record selection formula

{Product.Product Name} = "Rapel" or
{Product.Product Name} = "Mozzie"

must also be present to limit the data on the first pass. Jim Broadbent