Customer Forum

Reporting on NULL or Blank values

Workbooks Support Posted: 2011-09-05 15:49

If you want to identify records with blank values, you should use an OR statement because blank values can be represented in the Workbooks database in two forms:  

  • NULL, which represents the absence of a value;


  • An empty/blank field, which is a field-formatted value. 

To identify a field that is either blank or null, you should format your formula using an OR statement as shown below:

  • column_abc IS NULL OR column_abc =''

NOTE:  If you use the predefined "is blank" or "is not blank" in reporting criteria or within Workbooks filters, Workbooks checks both options for you, thus 'under the covers':

  • Choosing is blank is doing this:

    • column_abc IS NULL OR column_abc=''
  • And choosing is not blank is doing this:

    • column_abc IS NOT NULL AND column_abc !=''

So, for example, if you want a report that shows you organisations that have a revenue in excess of £2,000,000 AND the number of employees field is empty, you would enter the formula as shown below:

IF( organisation_annual_revenue >2000000
AND (organisation_num_employees IS NULL
OR organisation_num_employees =''),
'Complies', 'Does not comply')

or you can use a built in Workbooks function that checks both blank and null for you, IS_BLANK:

IF( organisation_annual_revenue >2000000 
AND IS_BLANK(organisation_num_employees),
'Complies', 'Does not comply')

NOTE:  When looking for a blank value you should not enter a space between the two apostrophes, so it's '' and not ' '.

Workbooks Support Posted: Mon, 01.12.2014 - 11:40


Imagine you wanted to check the integrity of your data in Workbooks. You could easily set a criteria to identify blank fields, but you may also want to have visibility of those which are not blank too. Using the theory above you can easily compare blank and non-blank fields to gain a greater understanding of your data.

In this example, I'd like to know how many of my People records have Mobile numbers compared to those that don't. Firstly I've created a calculated column which will give me BLANK if, you guessed it, the field is blank and NOT BLANK otherwise. Click to enlarge.

Which makes my report look like this.

Now I will add a Summary View, with a Summarised Column grouped by the calculated column 'Mobile Blank Test' I just created. 

This Summarised Column counts number of BLANK and NOT BLANK entries to give me the following result - a clear picture of the scale of my mobile data integrity.