INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I've gotten solutions within a day - it saved a lot of time and actually got me one ATTABOY from my boss..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Reporting Solutions > Business Objects: Crystal Reports 4 Other topics Forum

How to list record set (2)

Read More Threads Like This One

hchau (Programmer)

15 Aug 12 9:45

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 (Instructor)

15 Aug 12 10:14

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.
www.MilletSoftware.com

hchau (Programmer)

15 Aug 12 11:30

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 (Instructor)

15 Aug 12 11:36

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

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.
www.MilletSoftware.com

hchau (Programmer)

15 Aug 12 14:48

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 (Programmer)

15 Aug 12 15:17

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 o.OrderID=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.

www.R-Tag.com Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

lbass (TechnicalUser)

18 Aug 12 9:31

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Business Objects: Crystal Reports 4 Other topics

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

How to list record set (2)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!