how summerize null values

[jameys26174]

Hi all..
I have a general question regarding CR (10) where you want to show info that doesn't exist in some of your tables.

For example:
I have a rpt that has three tables (oracle) - parent & 2 childs.
I need to show all people who have not purchased product from one of the 2 childs. I know to create Left outer joins w/ my links and I do get the rpt to show info (null info from the child).. However, the problem occurs when I want to use select expert or formula that will generate NON sales activity w/in summeries.

Customer exist in parent - but doesn't exist in childs.

Need to summerize: show all customers who have not purchased something in the last x months.

When I try this, it shows customers who exist (but maybe didn't meet date requirements) but doesn't show customers who don't exist all together in child tables.

Txs,
J

 

[lbass]

First group on {parent.ID} and then create a formula {@inperiod}:

if {child1.date} in dateadd("m",-6,currentdate) to currentdate then 1 else 0

Then create a running total using the running total expert:
Select {parent.ID}, distinctcount, evaluate based on a formula:

sum({@inperiod},{parent.ID}) = 0

Reset never.

This would count those parents with no business with child1 during the last 6 months. Repeat for Child2. Or, if you want only to show those with no business in either account, then modify {@inperiod} to:

if {child1.date} in dateadd("m",-6,currentdate) to currentdate or
{child.date2} in dateadd("m",-6,currentdate) to currentdate then 1 else 0

-LB