Automatic number formatting

You can specify different number formats in the database and apply them automatically on CELLGET formulas.

For example, if a report displays values for different elements of the Measures dimension, you can automatically format each value according to the type of measure. For example, apply a % format for percentage measures and a currency format for currency measures etc. Those formats can be automatically applied to all values in which those measures are referenced, such as values returned by CELLGET.

If you specify the tilde (~) character as the number format of a cell that contains a CELLGET formula, then values in that cell are automatically formatted according to the format that is specified in the database. But, if the values are modified by a calculation then the number formatting is lost. For example, if you provide options to scale the values returned by a CELLGET formula by 10, 100, 1000 etc, the necessary calculation prevents the application of the database's number format.

In that case, use the FORMATSTRING function to read the number format from the database and apply it to the result of the calculation. FORMATSTRING reads the number formatting of a data source and applies it to values that are modified by calculations. FORMATSTRING has two parameters: the value to be formatted and the source of the format.

Note: Automatic number formatting is supported by Microsoft Analysis Services and OLAP data sources.

In OLAP, databases, to implement automatic number formats, the different number formats must be stored in an attribute named Format_String. You can configure which dimension controls the number format.