Eliminate Nulls in Grouping

[marksimm]

I have a field that I need to group on that frequently has nulls. The data source is a SQL stored procedure. I created the following formula.

IF {sp_ProductionSchedule;1.job_no} = "1" THEN
"LINE 1"
ELSE IF {sp_ProductionSchedule;1.job_no} = "2" THEN
"LINE 2"
ELSE IF {sp_ProductionSchedule;1.job_no} = "3" THEN
"LINE 3"
ELSE IF {sp_ProductionSchedule;1.job_no} = "BY HAND" THEN
"BY HAND"
ELSE IF IsNull({sp_ProductionSchedule;1.job_no}) THEN
"UNASSIGNED"
ELSE
"UNASSIGNED"

Neither the IsNull or Else will stop my report from creating "blank" group names. The "UNASSIGNED" group never appears. After browsing the forum, I unselected the convert NULL field value to default in options/reporting. That had no affect. What now? Thanks in advance.

Mark Simmerman
Napa, CA

 

[synapsevampire]

You might try restructuring to:

IF IsNull({sp_ProductionSchedule;1.job_no}) THEN
"UNASSIGNED"
IF {sp_ProductionSchedule;1.job_no} = "" or
{sp_ProductionSchedule;1.job_no} = "0"
THEN
"UNASSIGNED"
ELSE IF {sp_ProductionSchedule;1.job_no} = "1" THEN
"LINE 1"
ELSE IF {sp_ProductionSchedule;1.job_no} = "2" THEN
"LINE 2"
ELSE IF {sp_ProductionSchedule;1.job_no} = "3" THEN
"LINE 3"
ELSE IF {sp_ProductionSchedule;1.job_no} = "BY HAND" THEN
"BY HAND"
ELSE "UNASSIGNED"

-k

 

[lbass]

"IsNull" always has to be the first component of a formula, so try:

IF IsNull({sp_ProductionSchedule;1.job_no}) THEN
"UNASSIGNED"
ELSE IF {sp_ProductionSchedule;1.job_no} = "1" THEN
"LINE 1"
ELSE IF {sp_ProductionSchedule;1.job_no} = "2" THEN
"LINE 2"
ELSE IF {sp_ProductionSchedule;1.job_no} = "3" THEN
"LINE 3"
ELSE IF {sp_ProductionSchedule;1.job_no} = "BY HAND" THEN
"BY HAND"
ELSE
"UNASSIGNED"

-LB

 

[synapsevampire]

Ooops, I missed an else in the beginning there:

IF IsNull({sp_ProductionSchedule;1.job_no}) THEN
"UNASSIGNED"
ELSE IF {sp_ProductionSchedule;1.job_no} = "" or
{sp_ProductionSchedule;1.job_no} = "0"
THEN...

-k

 

[mbarron]

I've never work with null fields or stored procedures directly, so there may be "rules" I don't know about.

There appears to be 5 possible outcomes: Line 1, Line 2, Line 3, By Hand or Unassigned. Would it be possible to change your formula to the following, eliminating the "null check"?:

IF {sp_ProductionSchedule;1.job_no} = "1" THEN
"LINE 1"
ELSE IF {sp_ProductionSchedule;1.job_no} = "2" THEN
"LINE 2"
ELSE IF {sp_ProductionSchedule;1.job_no} = "3" THEN
"LINE 3"
ELSE IF {sp_ProductionSchedule;1.job_no} = "BY HAND" THEN
"BY HAND"
ELSE
"UNASSIGNED"


Mike