locked
Dynamiclly created member name RRS feed

  • Question

  • Hello ,

     

    We need to perform set of last 6 months and last 6 weeks. To do that I brought weeks hierarchy structure to months structure  and add it to the last 6 months.

    create member CURRENTCUBE.[TIME chodesh].[ByTaarich].[week-2] as

    (

          [TIME chodesh].[ByTaarich].[All]

        , [TIME chodesh].[ByWeeks].LASTCHILD.lag(2)

    );

     

    My question: Is it possible to created  the member [week-2] (for example), dynamically so it holds the value of the week.

    If we are in 2012w01, instead of [week-2], it will look like  [2011w51]

     

    Thank you

    Assaf

    Wednesday, January 4, 2012 10:07 AM

Answers

  • Hi Assaf,

    As far as i know, there is no process to dynamically change the calculated member name. I found a workaround to approarch this by dynamic set. As we know, dynamic sets are not calculated once, they are calculated before each query. So, you can create a dynamic named set like this:

    create dynamic set currentcube.[Week-2]
    as
    [TIME chodesh].[ByWeeks].LASTCHILD.lag(2)

    then, we can use .item() to get the member. From the defintion of the named set, there is only one member in the set. So, we can write [Week-2].item(0).item(0) as the member which can directly be used in your work.

    See http://msdn.microsoft.com/en-us/library/ms145501.aspx for more information about Item mdx function.

    Regards,
    Jerry

    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:22 PM
    Friday, January 6, 2012 7:57 AM

All replies

  • Hi

     

    Instead of creating a member, try a set.

    Then the returned member name will be dynamic.

     

    Check the behaviour of the following and make the necessary changes to your script:

    with dynamic set [myset]
    as [Date].[Calendar].lastchild.lag(2)
    
    select [myset] on columns,
    	[Measures].[Internet Sales Amount] on rows
    from [Adventure Works]
    


    Regards,

    Jason

     


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)
    Wednesday, January 4, 2012 11:27 AM
  • Hi Assaf,

    As far as i know, there is no process to dynamically change the calculated member name. I found a workaround to approarch this by dynamic set. As we know, dynamic sets are not calculated once, they are calculated before each query. So, you can create a dynamic named set like this:

    create dynamic set currentcube.[Week-2]
    as
    [TIME chodesh].[ByWeeks].LASTCHILD.lag(2)

    then, we can use .item() to get the member. From the defintion of the named set, there is only one member in the set. So, we can write [Week-2].item(0).item(0) as the member which can directly be used in your work.

    See http://msdn.microsoft.com/en-us/library/ms145501.aspx for more information about Item mdx function.

    Regards,
    Jerry

    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:22 PM
    Friday, January 6, 2012 7:57 AM