CODEWITHSUNDEEP
mdx
AGuyCalledGerald
AGuyCalledGerald

Reputation: 8150

StrToMember does not accept calculated measure (mdx)

I try to create a member by using some calculated measure (I have called it 'MyDay' here). I return date members on an axis and the members have a property which I want to use. I can return the property itself, but cannot create the member and work with it.

WITH
MEMBER [Measures].[MyDay] as DatePart("d", MyDateDimension.CurrentMember.Properties("MyProperty"))

MEMBER MyDateDimension.[MyMember] AS  
    STRTOMEMBER("MyDateDimension.&[2015]&[4]&[" + CStr([Measures].[MyDay]) + "]")

MEMBER [Measures].[SomeOther] as (MyDateDimension.[MyMember], [Measures].[Other]

SELECT
{ 
    MyDateDimension.Members)

} ON COLUMNS,

{
    [Measures].[MyDay] //works,
    [Measures].[SomeOther] //error
}

If I replace 'MyDateDimension.CurrentMember' by some hard coded date member, it works fine. And this also works:

MEMBER [Measures].[SomeOther] as (STRTOMEMBER("MyDateDimension.&[2015]&[4]&[" + CStr([Measures].[MyDay]) + "]"), [Measures].[Other])

Upvotes: 0

Views: 1568

Answers (2)

whytheq
whytheq

Reputation: 35587

If you put the strToMember directly in the SomeMeasure then things start to function:

WITH 
  MEMBER [Measures].[MyDay] AS 
    Datepart
    ("d"
     ,[Date].[Calendar].CurrentMember.Member_Caption
    ) 
  MEMBER [Date].[Calendar].[MyMember] AS 
    StrToMember
    (
      '[Date].[Calendar].&[200603' + Cstr([Measures].[MyDay]) + ']'
     ,constrained
    ) 
  MEMBER [Measures].[SomeOther] AS 
    (
      //[Date].[Calendar].[MyMember]
      StrToMember
      (
        '[Date].[Calendar].&[200603' + Cstr([Measures].[MyDay]) + ']'
       ,constrained
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[MyDay]
   ,[Measures].[SomeOther]
  } ON COLUMNS
 ,Tail
  (
    Descendants
    (
      [Date].[Calendar].[Month].&[2005]&[8]
     ,[Date].[Calendar].[Date]
    )
   ,10
  ) ON ROWS
FROM [Adventure Works];

A slightly tidier version:

WITH 
  MEMBER [Measures].[MyDay] AS 
    Datepart
    ("d"
     ,[Date].[Calendar].CurrentMember.Member_Caption
    ) 
  MEMBER [Measures].[MyDayString] AS 
    '[Date].[Calendar].&[200603' + Cstr([Measures].[MyDay]) + ']' 
  MEMBER [Measures].[SomeOther] AS 
    (
      StrToMember
      (
        [Measures].[MyDayString]
       ,constrained
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[MyDay]
   ,[Measures].[SomeOther]
  } ON COLUMNS
 ,Tail
  (
    Descendants
    (
      [Date].[Calendar].[Month].&[2005]&[8]
     ,[Date].[Calendar].[Date]
    )
   ,10
  ) ON ROWS
FROM [Adventure Works];

Above returns the following:

enter image description here

So something like this in your cube:

WITH 
  MEMBER [Measures].[MyDay] AS 
    Datepart
    ("d"
     ,MyDateDimension.CurrentMember.Properties("MyProperty")
    ) 
  MEMBER [Measures].[MyDayString] AS 
    StrToMember('MyDateDimension.&[2015]&[4]&[' + Cstr([Measures].[MyDay]) + ']') 
  MEMBER [Measures].[SomeOther] AS 
    (
      StrToMember
      (
        [Measures].[MyDayString]
       ,constrained
      )
     ,[Measures].[Other]
    ) 
SELECT 
  {
    [Measures].[MyDay]
   ,[Measures].[SomeOther]
  } ON COLUMNS
 ,{MyDateDimension.MEMBERS} ON ROWS
FROM YourCube;

Upvotes: 1

SouravA
SouravA

Reputation: 5253

Try using the SUM function in the definition of [Measures].[SomeOther]. It will accept calculated members.

MEMBER [Measures].[SomeOther] as 
SUM(
   MyDateDimension.[MyMember], 
   [Measures].[Other]
   )

Upvotes: 0

Related Questions