How to replace nulls in crossTab by 0 ?

[matrixfree]

Hello!

Can someone please let me know how they got rid of the null in a crossTab cells to actually BE (not only display) 0$ so that these empty cells be used in other data item calculations ?

This is what i've already tried:

In the FORMAT options of the Data Item, i indicated "0" as the caracter to display when missing values BUT this is only for formatting matters (DISPLAY) it doesn't really replaces nulls by 0$ for example.

The problem is that i have another DatItem that needs to calculate arythmetic formulas from 4 different cells on the same line of our crossTab BUT it's still treating the missing cells like nulls EVEN though it's displaying 0 when missing caracters. :(

I also DID try the coalesce([Data Item], 0) with no luck as for it's simply not replacing the nulls or missing values on my crossTab's cells by 0.  :(

I found out on the net what we need to FIRST create some sort of temporary query that will force 0$ Inside the metric (Data Item) that is sometimes displaying nulls/missing values AND THEN to UNION (preserve duplicates) this query to our normal query (the Query that contains the Data Item that sometimes displays nulls/missing values) BUT this is taking too much time to process and after running the report it actually ends ups with an error !

Probably because itoo much rows are being processed this ways and THAT'S why i was looking for alternative ways to counter our original challenge (to force nulls into 0s)

Any other suggestions anyone ?

[New_Guy]

Hi,
Is it relational or Dimensional and did you try a case [measure] is null or if [measure] is null statements and how big is the result set you are expecting? Can it be done on the source side if it is a relational or any other cube technology?
Good luck
New guy