Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to make an MDX query return a value in the numeric format in SQL Server 2005 Analysis Services

How to make an MDX query return a value in the numeric format in 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.

↑ Back to the top

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]

↑ Back to the top

Keywords: kbhowto, kbexpertiseadvanced, kbsql2005as, kbinfo, kb, misc_migrate_32718

↑ Back to the top

Article Info
Article ID : 933837
Revision : 3
Created on : 4/13/2018
Published on : 4/13/2018
Exists online : False
Views : 224