KB14652: Currency formatting is lost for values equal to zero when a grid is placed on a Report Services document in MicroStrategy Developer 10.x

SYMPTOM:
The currency formatting is lost for values equal to zero when a grid is placed on a Report Services document in Strategy Developer 10.x. Instead of displaying as $0, the value appears as 0. The metric displays correctly on the underlying report.
 
The following image shows the grid as seen when executed as a report:
 

 
The following image shows the grid as seen when placed on a Report Services document using shift+drag:
 

 
Notice that the value for the Paris Call Center is displayed as 0 instead of $0.
 
CAUSE:
The zero value is actually a warehouse null. This null can arise under two different circumstances:
 

• The report SQL contains an outer join and the warehouse fact table does not contain a row for the specified attribute element.
• The warehouse fact table contains a row for the specified attribute element but has a null in the column corresponding to the metric.

The null is displayed as a zero because of null value display settings set at the report or project level.
 
ACTION:
There are two solutions for this issue. The first solution is to apply a threshold on the report. This solution will work for both null situations. The only drawback is that this solution needs to be applied to every report where the issue is seen. Follow the steps below to apply a threshold to the report:
 

1. In the report editor, click on the Data menu and select Thresholds.
2. Select the metric exhibiting the issue from the drop-down menu.
3. Add a new threshold to the threshold list.
4. Click the 'Click here to start a new qualification' option.
5. For the 'Field', select the metric exhibiting the issue.
6. For the operator, select 'Is null'.
7. In the Format Definition section, change the operation from 'Format' to 'Replace Text'.
8. In the new text field that appears type in '$0'.
9. Click OK.
10. Save and close the report.
11. Open the document in edit mode.
12. Remove the grid from the document section.
13. Add the grid back onto the document by shift+dragging the dataset.
14. Save the document.

The second solution is to use the NullToZero function on the fact being used by the problematic metric. This solution only works with the second null situation. This solution will not work with a null due to an outer join. Also, this solution will impact all reports using this fact. Follow the steps below to use the NullToZero function on the fact being used by the problematic metric:
 

1. Open the fact being used by the metric exhibiting the issue in edit mode.
2. Click the 'Modify' button.
3. Clear out the 'Fact Expression' text box.
4. Click on the 'f(x)' button.
5. Select the 'Functions' folder -> 'Null/Zero Functions' folder -> 'NullToZero' function.
6. Click the 'Next>' button.
7. Select the column the fact should be based on.
8. Click the 'Finish' button.
9. Save and close the fact.
10. Update schema.
11. Open the document in edit mode.
12. Remove the grid from the document section.
13. Add the grid back onto the document by shift-dragging the dataset.
14. Save the document.