building group based on multiple records

[atarrqis]

Oracle and CR11

I have Type, Order, Line, Value (may be null), Shipped (may be null)
West, A100, 2, 100, 10
West, A100, 3, ,100
West, A100, 7, 50,

West, A200, 2, ,100
West, A200, 5, , 50

My top group = Type. I need a lower group: one where at least one line of the order has Value not null (IN), the other group is where every line in the order has Value = null (OUT)
So using the above example A100 is in the first group because of line 2:

WEST
IN
A100, 2, 100, 10
A100, 3, ,100
A100, 7, , 50

OUT
A200, 2, ,100
A200, 5, , 50

I can’t do this at the database level because of other parameters used. I can't figure out how to do this in the report.

 

[chelseatech]

I can't see any way to do this using the standard tools inside groups as what you are asking for is to get the data, group it by Type and Order, and count the number of NULL values and then regroup based on whether all the records for that order are NULL or not.

Grouping only happens once, and you want to sort by that grouping at the top level, so a subreport won't work.

Best solution I can think of is to use two SQL expressions to count the number of
records. Create a SQL expression of
(SELECT Count(Order) from table WHERE Order={mastertable.order} and
ISNULL (Value))

And for the other one - not sure if you need this
(SELECT Count(Order) from table WHERE Order={mastertable.order} and NOT
ISNULL (Value))

Note that if a SQL expression is enclosed in () you can write a sub select clause.

Then a Simple formula of
if {%SQL} >0 then "IN" else "OUT"

Only other problem I can see is that field names of ORDER, VALUE and TYPE might be reserved words in SQL. Hope your real fields are called something else.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[atarrqis]

Thanks for your response but I do not understand "Order={mastertable.order}". There is only one table so this looks lke "{mastertable.order}={mastertable.order} so I must be missing something.

 

[lbass]

Try setting up one SQL expression like this:

(
select count(`Value`)
from table A
where A.`Type` = table.`Type`
)

Replace "table" with your "table" name, and leave everything else as is (assuming "type" and "value" are your actual field names).

Then reference this in a formula to group on:

if {%cntVal} > 0 then
"In" else
"Out"

-LB