CODEWITHSUNDEEP
crystal-reports
LapplandsCohan
LapplandsCohan

Reputation: 775

Exclude orders with specific item from report in Crystal Reports

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.

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.

Upvotes: 0

Views: 3746

Answers (2)

Jeremy Peck
Jeremy Peck

Reputation: 156

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}

Upvotes: 0

Lee Tickett
Lee Tickett

Reputation: 6037

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

Upvotes: 1

Related Questions