Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Converting Zeros to Nulls (in Analysis Services Pivot Tables)


In certain situations, it is desirable to not have 'blank' records displayed within a Pivot Table.  This scenario is most often encountered when there are measures that only apply to certain criteria, such as Discounts or Costs, but where the records themselves contain a value other than NULL.

The screenshot below is an example of this scenario, in which the desired output does not contain the Customer's that don't have any value associated with them.  


What is happening here is Analysis Services considers '0' a value, and as such is displaying it.  If you were to drill into these cells, you would have records returned, but the total of the value in question would sum to '0'.

To correct for this, you must first clean up the data that exists within the Data Warehouse.  This can be done by creating a Script Action that retroactively converts 0's to NULL.

  1. Right-click on Script Action and select Add Custom Step

  2. Build a Custom SQL Query, similar to the example below, for each of your measures that you want to be treated in this manner.  Each measure contained within a single table can be added to the same script.  Be sure to set your table as a parameter of the script, so that managed execution recognizes the relationship of the script and table.

    Example Query: 

    UPDATE [Posted Sales Transactions_R]
    SET [Sales Amount] = NULL
    WHERE [Sales Amount] < 0.01 AND [Sales Amount] > -0.01

  3. Next, you need to set this Script Action as a Pre-Script on your Fact Table's Data Cleansing step.  To start, right-click on the appropriate fact table and select Advanced -> Set Pre- and Post Scripts.

  4. In the dialog box that opens, select the newly created Script Action from the drop-down list under the Pre Step column in the Data Cleansing row:

  5. As a last step, you need to adjust the Null Processing setting for each measure that you want treated in this manner.  To begin this, right-click on the desired measure and select Edit Standard Measure

  6. In the dialog box that opens, change the Null Processing setting to Preserve.

Was this article helpful?
0 out of 0 found this helpful