Version 2021

Formatting null values and blank cells

An empty cell of data on a grid report represents a null value. A null value is an unknown value, because it can be the result of an empty area of your data source, or the result of the calculations and cross-tabbing that are sometimes performed on a grid report.

For example, a null value in your data source can occur if a customer omits his birth date or another piece of personal information. If your data source does not contain data in a particular field and you pivot the rows and columns on the resulting report, the resultant cross-tabbing may produce a null value. In the image below, the Internet Revenue column does not have data for display and returns empty cells when the report is run.

You can replace null values with a specific value, such as a zero or the word NULL or NO VALUE as shown in the image below, so that cells do not appear as empty on a report. The replacement can be for the final report display only, or for the calculation of the report data. Replacements do not change any values in your data source.

Null value replacements are specified in MicroStrategy Developer. MicroStrategy Web displays null values in the format designated for the report in Developer.

To replace a null value with a specific value

  1. In Developer, open a grid report.

  2. From the Data menu, select Report Data Options.

  3. Expand the Display category and select the Null Values subcategory.

  4. To replace a null value for the final report display only, enter the replacement value (such as a zero) in the Null Display Settings area for any of the scenarios listed:

    • An empty value is retrieved from your data source.
    • An empty value is calculated in the cross-tabulation process.
    • An empty value appears when the report is sorted.
  5. To replace a null value during the calculation of report data, enter the replacement value in the Aggregation Null Values box.
  6. Click OK.