Counting nulls 1

[skeezwiz]

How can I get the total number of nulls in my data provider?

 

[Sridharan]

There's something called IsNull() function. Maybe you can just play around that creating an Object. But really don't have a formula with me in hand. Gotta play myself with that.

Sri

 

[blom0344]

Do you have a field in your DP that stores unique values:

Suppose unique field = PK
Field to check is = TEST

Then create variable XXX:

=Count(<PK>) In <TEST>

Create a table with TEST , XXX

set a filter on test only selecting the null value of TEST,
voila , you have one row with the actual amount of null values for that field.

T. Blom
Information analyst
tbl@shimano-eu.com

 

[skeezwiz]

This is what I done initially (apply filter selecting #EMPTY and counting rows) which gives correct result.

However, I want that value on the summary report as I cannot copy it over i.e. summary report has no null filter.

My DP:

Col1 Col2 ...
GUID Name ...
GUID NULL ...
GUID NULL ...
GUID Name ...

How you count the nulls here?

 

[blom0344]

I am not sure whether I understand what you want, so I'll suggest a little diversion.

Create a second dataprovider that just does a count on the number of null values and returns just that as a measure. Since it is just a measure without a dimension you can use it anywhere in the report.

What do you mean by summary report? Mail me an example if you like..

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Subhashab]

the following formula will do the needful
if col2 is the object containing nulls

the create a measure variable in BO
= sum(if (isNull(<col2>)) then 1 else 0)
this will give you count of nulls

 

[skeezwiz]

blom0344, your method worked!


Subhashab, when I used your method I get an #IERR in the results.

 

[Subhashab]

this is because on the objects present in the formula is not present in the body of the table. include the objects appearing in the formula. in case you do not want to display it , you can hide it .