Microsoft KB Archive/933837: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
Line 49: | Line 49: | ||
<pre class="codesample">WITH | <pre class="codesample">WITH | ||
MEMBER [Employee].[Employee].[Rank1Emp] AS | MEMBER [Employee].[Employee].[Rank1Emp] AS | ||
'RANK([Employee].[Employee].& | 'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)' | ||
SELECT | SELECT | ||
[Employee].[Employee].[Rank1Emp] ON 0 | [Employee].[Employee].[Rank1Emp] ON 0 | ||
Line 67: | Line 67: | ||
<pre class="codesample">WITH | <pre class="codesample">WITH | ||
MEMBER [Employee].[Employee].[Rank1Emp] AS | MEMBER [Employee].[Employee].[Rank1Emp] AS | ||
'RANK([Employee].[Employee].& | 'RANK([Employee].[Employee].&[47], [Employee].[Employee].members)' | ||
,format_string="#" | ,format_string="#" | ||
Latest revision as of 14:18, 21 July 2020
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="#" SELECT [Employee].[Employee].[Rank1Emp] ON 0 FROM [Adventure Works]
Keywords: kbhowto kbexpertiseadvanced kbsql2005as kbinfo KB933837