nulls to 0 in pivot

[zach_myt]

I am trying to get nulls in a pivot to show as 0's, which seems like it should be very basic built in functionality. I have tried If's, case's, and jquery scripts and nothing seems to work. I can do this at the SQL level buy I am trying to count in the widget and not in the query. 

I have read threads for 9 years ago where this was still a problem. How is this still an issue and not a quick function or easy setting???

What I've tried:

if(isnull(DUPCOUNT([device])),0, DUPCOUNT([device])) <-- this counts everything fine but blanks instead of 0

case when isnull(DUPCOUNT([device])) then 0 else DUPCOUNT([device]) end <-- this counts everything fine but blanks instead of 0

In the edit script section:

widget.transformPivot(
{
     type: ['member']
},
function (metadata, cell) {

if(cell.value === '') {
      cell.content = '0';
    }
  }
);

AND 

widget.on('ready', function(){

$('td[class*=p-value] div').map(function(i, cell) {

      if(cell.innerHTML == "&nbsp;" ) cell.innerHTML='0';

      })

    })

})

Any ideas?

[Sijo1995 · Accepted solution]

Hi @zach_myt ,

This script for Linux region

widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        if(cell.content.trim() == '')
			cell.content = '0'
    }
)

Thanks

Sijo

[zach_myt]

Simple and it works correctly, thank you!