Author Topic: How to replace nulls in crossTab by 0 ?  (Read 638 times)

Offline matrixfree

  • Full Member
  • ***
  • Join Date: Nov 2015
  • Posts: 28
  • Forum Citizenship: +0/-0
How to replace nulls in crossTab by 0 ?
« on: 10 Jul 2017 10:22:10 pm »

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 ?

Offline New_Guy

  • Statesman
  • ******
  • Join Date: Mar 2016
  • Posts: 308
  • Forum Citizenship: +15/-0
Re: How to replace nulls in crossTab by 0 ?
« Reply #1 on: 12 Jul 2017 01:02:59 pm »
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