Description of the LANGUAGE cell property in SQL Server 2005 Analysis Services

View products that this article applies to.

SUMMARY

Microsoft SQL Server 2005 Analysis Services introduces the LANGUAGE cell property in addition to the VALUE cell property and the FORMAT_STRING cell property. In SQL Server 2005 Analysis Services, the FORMATTED_VALUE cell property is built on the following cell properties:

VALUE
FORMAT_STRING
LANGUAGE

The LANGUAGE cell property is very useful when you want to display the currency symbol for a measure and the format string of the measure is defined as Currency.

Back to the top

MORE INFORMATION

How SQL Server 2005 Analysis Services determines the LANGUAGE cell property for an object

You can explicitly specify the LANGUAGE cell property on objects such as dimensions or cubes. If you do not specify the LANGUAGE cell property on an object, the LANGUAGE cell property is inherited from the parent of the object.

If you do not specify the LANGUAGE cell property on a cube, the LANGUAGE cell property of the cube is inherited from the database that contains the cube. If you do not specify the LANGUAGE cell property on the database, the LANGUAGE cell property is inherited from the server object.

For example, you have a cube that is located on a English (United States) version of SQL Server 2005 Analysis Services. By default, the value of the LANGUAGE cell property of the cube is 1033. When you run a Multidimensional Expressions (MDX) query but you do not specify the LANGUAGE cell property, the MDX query uses the LANGUAGE cell property of the cube against which you run the MDX query.

However, when you run the "Sample 1" MDX query against the Adventure works cube of the AdventureWorks Analysis Services Project Enterprise Edition sample project, the query output is displayed as the Chinese Yuan symbol. This is because the LANGUAGE cell property is explicitly specified to 2052. 2052 is the locale identifier (LCID) for the Chinese - China locale.

Sample 1

with member measures.A 
as [Measures].[Reseller Gross Profit], language=2052
member measures.B as measures.A
select {measures.A, measures.B } on 0
from [Adventure Works]
cell properties value, format_string, language, formatted_value

Note The AdventureWorks Analysis Services Project Enterprise Edition sample project is included in the Analysis Services database project. To download the Analysis Services database project, visit the following Microsoft Web site:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004 (http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004)

Back to the top

How to use MDX scripts to control the LANGUAGE cell property

You can use MDX scripts in cubes to control the LANGUAGE cell property. For example, you can use an MDX script that resembles the "Sample 2" MDX script.

Sample 2

Scope([Reporting Currency].[Destination Currency Code].[Destination Currency Code].Members);
    language(this) = [Reporting Currency].[Currency LCID].membervalue;  
End Scope;

Additionally, you can use the "Sample 3" currency conversion MDX script to convert a measure value from US Dollar to another currency.

Sample 3

 Scope ({ Measures.[Sales Price] });     
     Scope( Leaves([Time]), [Reporting Currency].[USD], Leaves([Destination Currency]));     
        Scope({ Measures.[Sales Price]});     
         This = [Reporting Currency].[USD] * Measures.[Exchange Rate];
         Non_Empty_Behavior(This) = Measures.[Sales Price];
        End Scope;    
     End Scope;      
     Scope( Leaves([Time]) , Except([Reporting Currency].[Destination Currency Code].[Destination Currency Code].Members, 
                {[Reporting Currency].[Destination Currency Code].[Destination Currency Code].[USD]}));     
         Scope( { Measures.[Sales Price]});    
         This = [Reporting Currency].[Destination Currency Code].[USD] /(Measures.[Exchange Rate], LinkMember([Reporting Currency].[Destination Currency Code].CurrentMember, [Destination Currency].[Destination Currency Code]));   
     Non_Empty_Behavior(This) = Measures.[Sales Price];
    End Scope;      
  End Scope; 
End Scope;

The "Sample 3" MDX script creates a Reporting Currency dimension. You can use this MDX script to control the LANGUAGE cell property of cells and the currency conversion of cells based on query context. For example, you use the "Sample 4" MDX query:

Sample 4

SELECT  
    [Geography].[Continent].&[EUROPE] ON 0,
    [Measures].[Sales Price] ON 1
FROM 
    [MyCube] 
cell properties value, format_string, language, formatted_value

The "Sample 4" MDX query generates a cell that represents the aggregated sales price for Europe. Because you do not specify the LANGUAGE cell property in this MDX query, the LANGUAGE cell property is inherited from the default member of the Reporting Currency dimension. If the value of the default LANGUAGE cell property of the Reporting Currency dimension is 1033, the numeric output, the LANGUAGE cell property, and the currency symbol represent U.S. dollars. This behavior occurs because 1033 is the LCID for the English - United States locale.

Additionally, you can update the "Sample 4" MDX query to display the output in British pounds (GBP). To do this, apply a slice on the relevant member of the Reporting Currency dimension. For example, you use the following "Sample 5" MDX query.

Sample 5

SELECT
    [Geography].[Continent].&[EUROPE] ON 0,
    [Measures].[Sales Price] ON 1
FROM [MyCube] 
WHERE 
    ( [Reporting Currency].[Destination Currency Code].[GBP] )
cell properties value, format_string, language, formatted_value

If you double-click the cell that is returned, the value of the LANGUAGE cell property appears as 2057. 2057 is the LCID for the English - United Kingdom locale. To display the output in a specific currency, you must specify the Reporting Currency dimension in the SELECT clause or in the WHERE clause to force the MDX script of the cube to take effect.

Back to the top

How to display the output of a calculated member in a specific currency

If you want to display the output of a calculated member in a specific currency, you must explicitly specify the LANGUAGE cell property. The LANGAUGE cell property for calculated members is not inherited from the query context. If you rewrite the "Sample 5" MDX query to resemble the following example, you obtain different result.

Sample 6

WITH MEMBER [Geography].[Continent].Calc AS 
    '(
        [Geography].[Continent].&[EUROPE] , 
        [Reporting Currency].[Destination Currency Code].[GBP]
        ) ' 
SELECT 
    [Geography].[Continent].Calc ON 0,
    [Measures].[Sales Price] ON 1
 FROM     [MyCube] 
cell properties value, format_string, language, formatted_value

The cell that the "Sample 6" MDX query returns has the same numeric value as the cell that the "Sample 5" MDX query returns. However, the cell is displayed together with a U.S. dollar sign ($). If you double-click the cell, you notice that the value of the LANGUAGE cell property is 1033, even though you expected 2057.

This behavior occurs because you did not specify the LANGUAGE cell property in the definition of the calculated member. Therefore, the LANGUAGE cell property is inherited from the default LANGUAGE cell property of the Reporting Currency dimension. Additionally, the default LANGUAGE cell property of the Reporting Currency dimension is U.S. dollars.

If the "Sample 3" MDX script does not exist, SQL Server 2005 Analysis Services determine the LANGUAGE cell property of the returned cell based on the order of the following objects:

The default language for the cube
The default language for the database
The language for SQL Server 2005 Analysis Services
The system locale

Back to the top

What to consider when you try to specify a language for calculated members

SQL Server 2005 Analysis Services can determine the actual numeric currency conversion for calculated members from the query context. However, SQL Server 2005 Analysis Services cannot determine the value of the LANGUAGE cell property or of the currency symbol for calculated members from the query context.

You must specify the LANGUAGE cell property if you want to obtain a nondefault currency symbol. If you do not specify the LANGUAGE cell property, the numeric value of the cell value and the currency symbol of the cell value may not match.

Back to the top