Straight table

[aheavy95]

Hi all, 

I am trying to create a new report based on an existing report, that will show me all records in which the product Name doesn't exist (null).   To be clear, If there are 4 rows of missing  product Name data in the original report, this report will show only these 4 

In this case, what do I need to add to the product name column and to the rest of the columns?

Thanks, 

Alon

[Or]

The easiest way to achieve this is to remove the other measures and add one new measure - if(isnull([Product Name]),'Missing Product Name').  You would then just need to disable "Include zero values".

[aheavy95]

The thing is I need to show these columns as well 

[Or]

Then you could either modify those columns with a similar if(isnull()) condition, or you could create a new dimension instead:

If(Isnull([Product Name]),'Missing Product')

You would then place this dimension in your chart and uncheck "Include null values" for the dimension.

[aheavy95]

didn't work. it shows me no rows, even though there are such cases

[BrunPierre]

Use below as calculated dimension and uncheck suppress zeroes-values / uncheck 'Include zero values' .

Aggr(IF(Len(trim([Product Name]))=0, 'Missing Product Name'),DocNum)

or

IF(Len([Product Name])<=0,'Missing Product Name')

[Or]

I can confirm that this works, assuming your values are null. I use it regularly.

And with nulls hidden:

If your values are not actually null, you'll have to check for len(Field)=0, or whatever other option you prefer, rather than isnull().

[aheavy95]

I've tried both formulas: 

=if(isnull([Company Name]),'missing company')

//=if(len([Company Name])=0,'missing company')

And it still doesn't work:

I know this script should work. Any idea what the problem might be?

[Or]

Is the company name actually null? Or is it missing entirely (that is, it's a result of a key to another table and no row exists)? It seems like it might be the latter...

If that's the case, you could force the issue with aggr(), something like:

=if(isnull(aggr(Only(CustomerName),UniqueCustomerID)),'Missing')