No formula result for Null data 1

[Kevinski]

Hi

I have a crosstab report which splits purchases across business units for a company.

The business units are based on a numeric account code or are Null if the business unit has not yet been determined.

I have a formula which converts these numbers to more meaningful names for the crosstab column headings but I can't get a heading to appear for the Nulls. The data is there but no column heading.

I've tried both "if business unit = ' ' then.....
AND
"if business isNull then.......

but I still don't get a column heading.

Full formula is as follows....
if {dbo_vw_SoftwareLicense.BusinessUnit} = '3955'
then 'IT'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '3843'
then 'THL'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '3993'
then 'TI'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '6000'
then 'TMF'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '1409'
then 'TT'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = ' ' then 'BU not assigned' <- can't get this heading to appear

Data is from an SQL view

 

[synapsevampire]

The order is important with Crystal, try:

Full formula is as follows....

if isnull({dbo_vw_SoftwareLicense.BusinessUnit})
or
{dbo_vw_SoftwareLicense.BusinessUnit} = ' ' then 'BU not assigned'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '3955'
then 'IT'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '3843'
then 'THL'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '3993'
then 'TI'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '6000'
then 'TMF'
else if {dbo_vw_SoftwareLicense.BusinessUnit} = '1409'
then 'TT'

-k

 

[dgillz]

Yes, always make your null test the first line of logic in your formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Madawc]

Crystal has an odd approach to errors. If you try to divide by zero, it stops the entire report and insists that you correct it, regardless. But if you try doing anything to a null value without first testing it for null, it stops processing the formula and returns nothing, without a hint as to what's wrong.

Madawc Williams
East Anglia
Great Britain

 

[Naith]

Dividing by zero is a critical error in any application - so of course processing of the report must stop until the error is rectified.

A function on a null value is not an error. It just returns something you don't want - i.e. nothing. You can't compare the two, Madawc.

Kevinski, did SynapseVampire's solution work for you? Note that ' ', '', and null are not interchangable. They are all different values, and should be tested for individually, if you believe it possible that your data could contain solitary spaces ' ', or empty strings '', as well as null values.

Naith

 

[Kevinski]

Naith - SynapseVampire's solution worked well thanks. Yep I'm aware of the non-interchangeability (!) of those values.

While I'm here does anyone have any ideas on a previous post of mine (several weeks old - no replies) about how to get column headings to repeat on multi-page (down) crosstab reports?

Its actually this report which is affected - its 5 pages long but column headings only appear on the first page.

Any help appreciated