Article ID: 927165 - View products that this article applies to.
This article describes the rules for derivation of the FORMAT_STRING cell property in Microsoft SQL Server 2005 Analysis Services. This article mentions all cases in which the FORMAT_STRING cell property is inherited.
When you use certain Multidimensional Expressions (MDX) functions in SQL Server 2005 Analysis Services, the FORMAT_STRING cell property of the returned result is set to a different format than may be expected. This behavior differs from the behavior in SQL Server 2000 Analysis Services. This change was made to improve performance in the affected functions.
When the IIf function is used, the FORMAT_STRING cell property is set to Standard instead of to the cell property of the original measure.
When the Rank and Count functions are used, the behavior of the format string depends on the dimension in which the calculated member that is using the Rank or Count function is defined. If the calculated member is on any dimension other than the measures dimension, the format string is set to the format string of the default measure. If the calculated member is in the measures dimension, the format string is set to Standard. This occurs because the current measure becomes the calculated measure instead of becoming a regular-measure group measure. The calculated measure has no format string. Therefore, a default format string of "standard" is used.
For example, consider the following query.
This query will return the Rank1Emp value with a format string of currency if the default member for the measures dimension is Reseller Sales Amount.
Now, consider the following query.
This query will return the Rank1Emp value with a format string of Standard (numeric) regardless of the chosen default member for the measures dimension.
One way to make sure that the desired format string is returned is to explicitly specify it in the calculated member definition. For example:
The following are the rules for derivation of the FORMAT_STRING cell property in SQL Server 2005 Analysis Services.
Collapse this tableExpand this table
Contact us for more help
Connect with Answer Desk for expert help.