How to list record set 2

[hchau]

I have two tables one has the order number and the other has the item being ordered and shipment status like below



order1 ------ Item 1, Shipped
------ Item 2, On-order
order 2 ----- Item 1, Shipped
----- Item 2, Shipped

How can I show only order with all items being shipped? In this case, I want to list order 2.

Please advise.

Thanks

 

[IdoMillet]

Create a formula that returns 0 if shipped and 1 if not.

Add a group selection formula that selects groups whose maximum of that formula is 0.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hchau]

I know how to create a formular for shipment
SHIPPED
If Shipped Then 1
else 0

Can you show me how to create the group selection?

 

[IdoMillet]

Use the menu option of:
Report, Selection Formulas, Group...

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hchau]

I have followed what is suggested. Here is what I get in the report

Order 1 --- Item 1 Shipped
Order 2 --- Item 1 Shipped
Order 2 --- Item 2 Shipped.

But I do not want Order 1 because it has an outstanding order. I only want to prit out order with all items shipped.

 

[RTag]

You can use a command with a subquery. This is how the command will look like if your database is SQLServer:

SELECT d.*
FROM Orders o
INNER JOIN Details d ON rderID=d.OrderID
LEFT JOIN (SELECT DISTINCT OrderID FROM Details WHERE Status<>'Shipped') t ON d.OrderID=t.OrderID
WHERE t.OrderID IS NULL

You have to update the table and column names.

The SQL will get the list of Orders with at least one non Shipped item and show all orders which are not in this list.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

Insert a group on order and then create a formula:

//{@NotShipped}:
if isnull({table.status}) or
{table.status} <> "Shipped" then
1 else 0

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

sum({@NotShipped},{table.order})=0

This would display only orders that have all items shipped.

-LB