Show all detail lines for orders containing a particular item

[DolphinDave2000]

Crystal Reports: I want to display all the detail lines on orders that contain a particular item. For example: order ABC has 10 detail lines and one line has product id = 1110. Order DEF has 2 detail lines, neither of which has product id = 1110. Order GHI has 3 detail lines with one line where the product id = 1110. The final result would show all lines on both order ABC & GHI.

When I try to use the Select Expert, I get both orders, but it only shows the lines that contain product id= 1110 and not all the lines for those orders.

I have done this in Access with a query based on another query, but am not sure how to do it in Crystal. Any solutions?

 

[markajem]

Since I don't know much more about the design of your report one quick solution would be maybe to insert a subreport. In the main report select only orders that contain that item number. Then link to your subreport by order number and it will only pull orders that have that item. In the subreport you can have it show all line items on that order that contains that item number. This is a rough way to do it but it should work. Another way would be to create a view and use the view as your data table linked by your product table. The selection again would be on your product table. User paramenter would choose item.

hth

 

[DolphinDave2000]

That sounds like it would work, thanks, but I was hoping there is another solution using formulas or something because sometimes I need to use additional criteria. For example: from the orders table (not the order details table) I need to restrict the orders by order date. So the end result will show all the detail lines on orders that contain a particular item when the order date is greater than a certain date.

In other words: I have a restriction on the "header" table, order date in the orders table, and a restriction on the "details" table, product ID in the order details table, but I want to then display all order detail lines for the orders meeting both restrictions.

 

[lbass]

Insert a group on {table.order} and then create a formula {@has1110}:

if {table.productid} = 1110 then 1

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

Sum({@has1110},{table.order}) > 0

-LB

 

[DolphinDave2000]

That worked! Thank you!!!