Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

Zeroes vs. Blanks

Adaptive currently does not differentiate between zeroes and true blank cells (empty cells, in other words) when it comes to the data Export page or matrix reporting. It might be the case that you need to differentiate between the two on a data dump to perform an accurate audit. It is also useful to remember that matrix reports that are run directly to Excel (or template reports) can be used in place of an export of data. Recent improvements to optimize matrix report behavior allows it to be a good alternative. Below is a set of steps to take to work around this limitation. 

  1. Set up a matrix report with accounts, levels, version and time periods you need for your data dump.
  2. Above the Horizontal axis, insert a Display As: custom element from Elements>>Display As>>Custom.

clipboard_e29e231565ecd18f775f675a8ee4eea5a.png

  1. Right click on this element and select formula assistant:
    • The key to this method is to trick the report in showing you blanks as a very large number, a number that would never show up in your regular budget.
    • I will be using 100000000000000000, but 99999999999999999 or something like this might be better for visibility.
  2. In the formula assistant box, type in the following formula:
    • iff(isblank(ACCT.this),100000000000000000, ACCT.this)
    • What this formula says is "if the current account that we are in is blank at a given intersection, then display this blank as 100000000000000000, otherwise show me the true value of this account."
    • This means that if an account contains a true zero, be it a calculated zero or an imported/manually entered zero will display as a zero in the report's output.

clipboard_e16ee68dbd50ab64b5fc2f07721dd4447.png

  1. Once this has been set up, run the report to excel.
  2. As a final step, use the "Find and Replace" option in Excel to find & replace all of the instances of 100000000000000000 with a blank or something else if you would like.
  3. This effectively achieves a differentiation between zeroes and actual, true blanks.