Select the product you need help with

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

Article ID: 927165 - View products that this article applies to.

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 "standard" 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="#" 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.

Collapse this tableExpand this table

Expression	Rule
Cell at granularity of measure group	Use the FORMAT_STRING property of the current measure.
CALCULATE (without outline calculations)	Use the FORMAT_STRING property of the current measure.
CALCULATE with semiadditive measure	Use the FORMAT_STRING property of the current measure.
Member A	Derive from the calculation that covers the member.
Tuple (a, b, … z )	Derive from the calculation that covers the tuple.
-a	Derive from "a."
FREEZE	Derive from the calculation at the pass of the FREEZE statement.
a.PARENT, a.FIRSTCHILD, a.LASTCHILD	Derive from the calculation that covers the result member.
CalculationPassValue(a,p,flag)	Derive from "a" at effective pass "p" (flag).
Aggregate, Sum, Min, Max, Avg, or Count(set)	If the current measure is regular, use the FORMAT_STRING property of the current measure. If the current measure is calculated, the FORMAT_STRING property derives from the current measure.
Aggregate, Sum, Min, Max, Avg(set, a)	If "a" is a measure, use the FORMAT_STRING property of "a." If "a" is calculated, the FORMAT_STRING property derives from "a."