Report Studio - handling of blank cells/null values

[duanern]

I am using Report Studio to generate crosstab reports.  When creating a report, there may be intersections which result in a blank cell (due to null value).  For example, an intersection with sales for a province where no transaction existed. 

I know I can use data format to have the value appear as zero; however, calculations for those rows also appear as blank even though data exists somewhere in that column.  Currently, we are using multiple IF, THEN, ELSE statements to change these items to "0"; however, that becomes quite cumbersome.  Is there any other way?

???

Thanks,

Duane

[blom0344]

Some tools / databases allow arethmetic operations with null. Oracle does allow additions with null values, but SQL server 2000 does not. In this case I guess that the cognos engine determines the null value within calculations as 'unknown'. And in three value logic additions with an unknown value  always add up to unknown.
The most certain way to eliminate this problem is to bring in a recordset that holds a record (and thus a value) for every combination you want in the crosstab.

The technical way to do this is either by using an outer-join or by a union query.

[Veekaygee]

You could use a calculation like nvl(....,0) or any similar function to convert the null values to a calculatable value and the problem should disappear.

[blom0344]

The nulls appear in the crosstab cause there is no combination and thus no record to associate with.
Applying nvl  function would work if a record would exist.
In this case there aren't any for the nulls in the crosstab.