Preserving Language across inline Calculated Members in SSAS

Question

Problem: I need to retrieve the language of a given cell from the cube. The cell is defined by code-generated MDX, which can have an arbitrary level of indirection as far as calculated members and sets go (defined in the WITH clause). SSAS appears to ignore the Language of the specified members when you declare a calculated member inline in the query.

Example:

The cube's default locale is 1033 (en-US)
The cube contains a Calculated Measure called [Net Pounds] which is defined as [Net Amt], language=2057 (en-GB)
The query requests this measure alongside an inline calculated measure which is simply an alias to the [Net Pounds]
When used directly, the measure is formatted in the en-GB locale, but when aliased, the measure falls back to using the cube default of en-US.

Here's what the query looks like:

WITH MEMBER [Measures].[Pounds Indirect] AS [Measures].[Net Pounds]
SELECT { [Measures].[Pounds Indirect], [Measures].[Net Pounds] } ON AXIS (0)
FROM [Cube] CELL PROPERTIES language, value, formatted_value

The query returns the expected two cells, but only uses the [Net Pounds] locale when used directly.

Is there an option or switch somewhere in SSAS that will allow locale information to be visible in calculated members? I realise that it is possible to declare the inline calculated member in a particular locale, but this would involve extracting the locale from the tuple first, which (since the cube's member is isolated in the application's query schema) is unknown.

khaled khaled · Accepted Answer · 2011-05-11T13:54:47

WITH

MEMBER [Measures].[Category] AS [Dim Category].[Category Parent ID].CURRENTMEMBER.Properties("LCID1036")
MEMBER [Measures].[Survey Date] AS [Dim Survey Date].[Hierarchy].CURRENTMEMBER.Properties("LCID1036")

SELECT 
NON EMPTY{[Measures].[AVG MFA Score], [Measures].[CategoryIsLeaf], [Measures].[Category], [Measures].[Survey Date]} ON COLUMNS, 

NON EMPTY { filter((DESCENDANTS(STRTOSET("[Dim Survey Date].[Hierarchy].members"),[Dim Survey Date].[Hierarchy].[Month],SELF), STRTOSET("[Dim Category].[Category Parent ID].members")),[Measures].[AVG MFA Score]<>null)}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
from [MFA DWH]

Preserving Language across inline Calculated Members in SSAS

3 Answers