Microsoft KB Archive/927165

= Description of the rules for derivation of the FORMAT_STRING cell property in SQL Server 2005 Analysis Services =

Article ID: 927165

Article Last Modified on 10/29/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



INTRODUCTION
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.



MORE INFORMATION
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 &quot;standard&quot; is used.

For example, consider the following query. WITH MEMBER [Employee].[Employee].[Rank1Emp] AS 'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)' SELECT [Employee].[Employee].[Rank1Emp] ON 0 FROM [Adventure Works] 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. WITH MEMBER [Measures].[Rank1Emp] AS 'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)' SELECT [Measures].[Rank1Emp] ON 0 FROM [Adventure Works] 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: WITH MEMBER [Employee].[Employee].[Rank1Emp] AS 'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)', format_string=&quot;#&quot; SELECT [Employee].[Employee].[Rank1Emp] ON 0 FROM [Adventure Works] The following are the rules for derivation of the FORMAT_STRING cell property in SQL Server 2005 Analysis Services.

Additional query words: MDX

Keywords: kbhowto kbinfo kbsql2005as KB927165

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.