Exclude orders with specific item from report in Crystal Reports

0
votes

I'm building a report that calculates processing time for orders, but we want to exclude all orders that contains a specific item from the report. Any ideas how I can accomplish this?

The (concerned part of the) database is quite straight forward.

  • A table with orders containing order id, customer id, order placement time, order shipping time etc.
  • A table with items.
  • A table linking the order and item tables. (orderitems)

Linking orders and orderitems and then excluding the specific item still counts and processes the orders that contains the item, so I have to figure out a way to exclude on order_id basis.

Using SQL I can get a list of orders to be excluded with a simple query:

SELECT DISTINCT order_id FROM orderitems WHERE item_id = 'Excluded item ID'

but how can I use this record selection in Crystal Reports? I've tried entering this as a SQL-command and have it unlinked with the rest of the report tables and then in record selection use

not ({orders.order_id} in [{ordersToBeExcluded.order_id}])

but that does not give the correct result.

2
crystal-reports

2 Answers

1
votes

A few options spring to mind- in order of preference:

  1. Do the work in SQL. Use a view to present the data to Crystal with those orders already excluded

  2. Create a sql expression using a subquery like isnull((select top 1 1 from orderitems where orderid = X and orderitemid = Y),0). Then use this in your selection criteria and sqlexpression = 0

  3. Use the group selection expert to suppress groups where count({specialitem},{group}) > 0

  4. Use running totals which evaluate only where count({specialitem},{group}) > 0

0
votes

You could also do a full outer join with exclusion (!=) or try doing a report filter (Report -->Selection Formula -->Record) with {orders.orders_id}<>{ordersToBeExcluded.orders_id}