FORMATSTRING

If you specify the tilde (~) character as the number format of a cell, 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.

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.

Syntax

=FORMATSTRING("value","format_string_source")

The source of the format can be a cell reference, or a function that returns a value that includes the format.

Example

In this example, CELLGET returns a value that is then divided by a value in a variable. This calculation prevents the application of the number format specified in the database:

=CELLGET( "BestPracticesOLAP", "Analysis", "[Period].[All Years]",
"[Product].[All Tires]")/reportvariables.rv_scaling.text

Example

In this example, the same CELLGET formula is used as the value to be formatted. The CELLGET is reused, without the /ReportVariables.rv_scaling.Text calculation, as the source of the format:

=FORMATSTRING(CELLGET("BestPracticesOLAP", "Analysis", "[Period].[All Years]","[Product].[All Tires]")/ReportVariables.rv_scaling.Text,
CELLGET( "BestPracticesOLAP", "Analysis", "[Period].[All Years]","[Product].[All Tires]"))