Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 80

Useful properties of measures
Apart from the AggregateFunction property of a measure, which we'll come to
next, there are two other important properties we'll want to set on a measure once
we've created it: FormatString and DisplayFolder .

FormatString
The FormatString property of a measure specifies how the raw value of the
measure gets formatted when it's displayed in query results. Almost all client tools
will display the formatted value of a measure, and this allows us to ensure consistent
formatting of a measure across all applications that display data from our cube.

A notable exception is Excel 2003 and earlier versions, which can only
display raw measure values and not formatted values. Excel 2007 and
later will display properly formatted measure values in most cases, but
not all. For instance, it ignores the fourth section of the FormatString
property which controls formatting for nulls. Reporting Services can
display formatted values in reports, but doesn't do it by default; the
following blog entry describes how you can make it do so: http://
tinyurl.com/gregformatstring .

There are a number of built-in formats that you can choose from, and you can
also build your own by using syntax very similar to the one used by Visual
BASIC for Applications ( VBA ) for number formatting. The Books Online topic
FORMAT_STRING Contents gives a complete description of the syntax used.

Here are some points to bear in mind when setting the FormatString property:

• If you're working with percentage values, using the % symbol will display

your values multiplied by one hundred and add a percentage sign to the end.
Note that only the display value gets multiplied by hundred—the real value
of the measure will not be, so although your user might see a value of 98%,
the actual value of the cell would be 0.98.

• If you have a measure that returns null values in some circumstances

and you want your users to see something other than null, don't try to
use an MDX calculation to replace the nulls—this will cause severe query
performance problems. You can use the fourth section of the FormatString
property to do this instead—for example, the format #,#.00;#,#.00;0;\N\A
will display the string NA for null values, while keeping the actual cell value
as null without affecting performance.