KB15233: How to control warehouse null values to rank properly in MicroStrategy Developer 9.x

When using a ranking function in Strategy Developer 9.x, null values are placed at the top of the dataset and are not given separate rank values.
 
Users may want to create a report which displays the metrics 'Unit Cost' and 'Unit Price', and a third metric, 'Rank' which ranks the items based on 'Unit Price' from high to low. Some of the items in the warehouse have null values for the two metrics. To do this, follow the steps below:
 

• Modify the Strategy Tutorial Warehouse to include null values (NOTE: It is recommended to backup the warehouse before making changes to it.):
   
  
1. Navigate to the Tutorial Reporting Warehouse, typically C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_DATA_7200.mdb
2. Open the database to edit it.
3. Open the 'LU_ITEM' table.
4. Navigate to row 31, 'The Prince' and delete the two values for 'Unit Price' and 'Unit Cost'.
5. Repeat step d for row 32, 'The Fountainhead'.
6. Close the warehouse.
• Create a new metric called 'Rank' with the definition 'RunningCount()'.
• Right-click on the 'RunningCount' function and select 'Parameters'.
• Choose the 'Sort By' tab, and add the metrics 'Unit Price' descending, then 'Unit Cost' ascending, as shown below:
  

• Create a report in Strategy Developer 9.x with the attribute 'Item', and the metrics 'Unit Cost', 'Unit Price', and 'Rank'.
• Execute the report in Strategy Developer 9.x and the results will display as follows:
  

• Notice that the two items with null values are listed at the top of the report and have the same rank as the last item, in this case, 2. It is desirable for the null values to be listed at the bottom of the report, and to have different ranks.
• To get the null values to show different ranks, edit the 'Rank' metric. Add the function 'NullToZero()' Around the fact, 'Unit Price' as shown below:
  

• This will force the Strategy Analytical Engine to recognize the null values as zeros, and apply a separate rank to them as shown below:
  

• Lastly, the Null Values should be at the bottom of the report, to read the rank logically from 1, 2, 3, 4. To accomplish this, users must modify a VLDB setting.
• In the report, choose the 'Data' menu, and then 'VLDB Properties.
• Expand the first group for 'Analytical Engine', and choose the first option for 'Display Null on Top'.
• >Uncheck the box for 'Use default…' and select the top radio button for 'Display Null values in bottom while sorting' as shown below:
  

• Re-execute the report, and the desired result will display, as shown below: