Exclude orders when any line item in the order has a certain attribute 1

[elsenorjose]

Hello all,

I'm creating a report for sales orders and the requester has asked that certain orders be excluded if any line item has a specific stock location (anything with '2-' at the beginning). As a real-world example, I have an order number 20022036 that has 2 lines, one with a stock location 2-FG1 and one with location FG1. If I just use "Not({Part_Stock.STK_06} Like '2-*')" in my select criteria, I still get the line with FG1. The requester would like that entire order to be excluded from the report. I tried grouping on order id and then using "Not({Part_Stock.STK_06} Like '2-*')" in the group select expert but that didn't work either; I still get the order with the FG1 line.

Feels like I'm missing something simple.

I'm on CRXI Developer on a SQL Server 2005 backend.

Thank you.

 

[lbass]

Create a formula like this:

//{@has2-}
if {Part_Stock.STK_06} Like '2-*' then
1

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

sum({@has2-},{table.orderID}) = 0

If you need to do any calculations across orders, use running totals, since the more usual summaries will include non-group-selected values.

-LB

 

[elsenorjose]

Thank you LB.