Microsoft KB Archive/933837

= How to make an MDX query return a value in the numeric format in SQL Server 2005 Analysis Services =

Article ID: 933837

Article Last Modified on 4/24/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



INTRODUCTION
In Microsoft SQL Server 2005 Analysis Services, a Multidimensional Expressions (MDX) query that uses the RANK function or the COUNT function may not always return a value in the numeric format. This article describes how to make the MDX query return a value in the numeric format.



MORE INFORMATION
In certain situations, an MDX query that uses the RANK function or the COUNT function returns a value that is not in the numeric format. For example, you run the following MDX query against the sample Adventure Works DW database. WITH MEMBER [Employee].[Employee].[Rank1Emp] AS       'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)' SELECT [Employee].[Employee].[Rank1Emp] ON 0 FROM [Adventure Works] This example MDX query returns a value in the currency format as follows. Rank1Emp $88.00 If the calculated member does not have the FORMAT_STRING property, the RANK function or the COUNT function uses the format of the default measure or of the measure property in the query context. The example MDX query uses the format of the Reseller Sales Amount default measure. Therefore, the example MDX query returns a value in the currency format.

To make the MDX query return a value in the numeric format, use one of the following methods to modify the MDX query.

Method 1
Specify a different slice in the WHERE clause, or put a measure in an axis. If you do this, the FORMAT_STRING property uses the current measure in the MDX query.

Method 2
Specify the FORMAT_STRING property when you define the calculated member. For example, modify the example MDX query as follows. 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]

Keywords: kbhowto kbexpertiseadvanced kbsql2005as kbinfo KB933837

-

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

© Microsoft Corporation. All rights reserved.