Changing the format of numbers in a report

You want users to be able to change the display of numbers in a report, from units to hundreds or thousands by selecting these from a list.

Here, we use unique names in a custom hierarchy to hold divisors. We divide the numbers in the report by these divisors and so alter their display.

  1. Create a formula in a report.
    For example: 
    =ROC("BestPracticesOLAP","SALES","[PROFIT].[Gross Margin]",
"[TIME].[All Years]","[VERSION].[Actual]","[CURRTYPE].[LC]","[LEVEL].[IFRS]",
"[UNIT].[G0000]","[INTERCO].[TotalPartner]",
"[PRODUCT].[All Tires]")
  2. Create a custom hierarchy with three elements. Use Units, Hundreds and Thousands as the captions and 1, 100 and 1000 as the unique names.
  3. Create a combo box in the report and drag the custom hierarchy onto it.
    The combo box displays Units, Hundreds and Thousands.
  4. Open the Format Listview dialog of the combo box and note the name of the combo box).
  5. In the report, click in the cell with the formula which returns the numbers.
    To divide the product of the formula by the divisors in the custom hierarchy we add the divide operator (/) and the reference to the combo box to the end of the formula.
    For example: =ROC("BestPracticesOLAP","SALES","[PROFIT].[Gross Margin]","[TIME].[All Years]","[VERSION].[Actual]","[CURRTYPE].[LC]","[LEVEL].[IFRS]","[UNIT].[G0000]","[INTERCO].[TotalPartner]","[PRODUCT].[All Tires]")/ReportObjects.combo.Text
Selecting 'Units', 'Hundreds' or 'Thousands' from the combo box affects the display of the numbers in the report