locked
Create calculate member return string RRS feed

  • Question

  • I would like to create a calculate measure that return all members (string) in dimension

    Is it possible?

    CREATE MEMBER CURRENTCUBE.Measures.[DefaultAreas] AS

        

     TUPLETOSTR([Areas].[Default].currentmember) ,

    VISIBLE = 1;  

    Above query returns

    Default

    DefaultAreas

    Area1

    [Area].[Default].&[Area1]

    Area2

    [Area].[Default].&[Area2]

    Area3

    [Area].[Default].&[Area3]

    I would like to get result like below. how can I do this?

    DefaultAreas

    [Area1]

    [Area2]

    [Area3]                    thanks,


    • Edited by SSAS_5000 Friday, May 6, 2016 2:20 PM wrong
    Friday, May 6, 2016 2:20 PM

Answers

  • Hi SSAS_5000,

    In this scenario, I think you need to get the key column, which is the string after "&" symbol, not the member caption. So you need to use the .properties("key") on current member. Please refer to MDX below:

    with member [x] as
    [Date].[Calendar].currentmember.member_caption
    member [y] as
    [Date].[Calendar].currentmember.properties("key")
    select {[x],[y]} on 0,
    {[Date].[Calendar].[Calendar Year].&[2010],
    [Date].[Calendar].[Calendar Year].&[2011]} on 1
    from
    [Adventure Works]

    Regards,


    Simon Hou
    TechNet Community Support


    Monday, May 9, 2016 7:30 AM

All replies

  • Hi,

      Try the MEMBER_CAPTION property. Sample Adventure work code below

    WITH MEMBER Measures.DimCaption
    AS
    [Geography].[Country Region Name].CURRENTMEMBER.MEMBER_CAPTION
    
    
    SELECT [Geography].[Country Region Name].[Country Region Name] ON 1
    ,Measures.DimCaption ON 0
    
      FROM [Model]


    Best Regards Sorna

    Friday, May 6, 2016 3:48 PM
  • Hi SSAS_5000,

    In this scenario, I think you need to get the key column, which is the string after "&" symbol, not the member caption. So you need to use the .properties("key") on current member. Please refer to MDX below:

    with member [x] as
    [Date].[Calendar].currentmember.member_caption
    member [y] as
    [Date].[Calendar].currentmember.properties("key")
    select {[x],[y]} on 0,
    {[Date].[Calendar].[Calendar Year].&[2010],
    [Date].[Calendar].[Calendar Year].&[2011]} on 1
    from
    [Adventure Works]

    Regards,


    Simon Hou
    TechNet Community Support


    Monday, May 9, 2016 7:30 AM