null and not null values in a cross tab

[jschill2628]

I am using Crystal XI.

I have the following formula, that I want to put in a Crosstab, however it is not populating everything, when I put the formula into the Crosstab, it only shows the first two values Cause- Not Populated and Cause- Populated.

(if isnull ({CHICLAIMSA1.SA136}) then "Cause- Not Populated" else
if not (isnull ({CHICLAIMSA1.SA136})) then "Cause- Populated" ) or

(if isnull ({CHICLAIM.SPECIAL9}) then "Severity- Not Populated" else
if not (isnull ({CHICLAIM.SPECIAL9})) then "Severity- Populated" ) or

(if isnull ({CHICLAIMSA1.SA73})then "Injury- Not Populated" else
if not (isnull ({CHICLAIMSA1.SA73})) then "Injury- Populated" ) or

(if isnull ({CHICLAIMSA1.SA138}) then "Line of Business- Not Populated" else
if not (isnull ({CHICLAIMSA1.SA138})) then "Line of Business- Populated" ) or

(if isnull ({CHICLAIMSA1.SA139}) then "Event Type- Not Populated" else
if not (isnull ({CHICLAIMSA1.SA139})) then "Event Type- Populated" ) or

(if isnull ({CHICLAIMSA1.SA140}) then "Event Subtype- Not Populated" else
if not (isnull ({CHICLAIMSA1.SA140})) then "Event Subtype- Populated" )


THANK YOU!!

 

[lbass]

How were you planning on using this formula in a crosstab? An if/then statement will stop processing as soon as a record meets a criterion in the formula, so the later clauses are not being processed. Since these are different fields, they probably should be handled in separate formulas. Because they are in different fields they are not really suited for a crosstab.

-LB

 

[Madawc]

What are you trying to do? If you want to count the various instances, then set up a number of formula fields, e.g.

if isnull ({CHICLAIMSA1.SA136}) then 1
else 0

The reason your formula fails is that the first two tests exhaust the possibilities: either that field is null or it isn't.

You could then do a summary count of @CauseUnpopulated, or do a crosstab using it. Repeat for the other fields.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP