Microsoft KB Archive/933837

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


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

SELECT
    [Employee].[Employee].[Rank1Emp] ON 0
FROM [Adventure Works] 

Keywords: kbhowto kbexpertiseadvanced kbsql2005as kbinfo KB933837