none
MDX linkmember with a set

    Question

  • Hi,

     

    Within SSRS I was trying to convert a date parameter that could be used with more than one date dimension, so certian measures are sliced by one date dim and others sliced by another date dim.  I used linkmember and the following works fine for a single value, but I was hoping to use it on a multi-value parameter.  It means a set is passed into the my calculated measure, causing it to error (although in RS it comes up with random value). 

     

    Anyone know a way I can achieve the same effect as a linkmember in MDX, that will except a set?  Or if there is a way I can fudge this in RS?

     

    Code Snippet

    WITH MEMBER [Measures].[Order Count2]

    AS

    (

    linkmember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    ),

    [Ship Date].[Fiscal].[All Periods],

    [Measures].[Order Count]

    )

    ,

    FORMAT_STRING = "#,#",

    NON_EMPTY_BEHAVIOR = [Order Count]

    select

    [Measures].[Order Count2] on 0

    from

    [Adventure Works]

    where

    {

    [Ship Date].[Fiscal].[Fiscal Year].&[2003],[Ship Date].[Fiscal].[Fiscal Year].&[2004]

    }

     

     

    Many thanks

    Matt

    Friday, April 18, 2008 9:03 AM

Answers

  • Apparently the infinite recursion occurs because Generate() is trying to convert the LinkMember() tuple (without set braces) to a string. So explictly converting LinkMember() to a set seems to work:

     

    Code Snippet

    WITH

    MEMBER [Measures].[Order Count2] AS

    Aggregate(

    Generate

    (existing [Ship Date].[Fiscal].[Fiscal Year],

    {(LinkMember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    ),

    [Ship Date].[Fiscal].[All Periods])}),

    [Measures].[Order Count]

    )

    ,

    FORMAT_STRING = "#,#"

    select

    [Measures].[Order Count2] on 0

    from

    [Adventure Works]

    where

    {

    [Ship Date].[Fiscal].[Fiscal Year].&[2003],[Ship Date].[Fiscal].[Fiscal Year].&[2004]

    }

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Order Count2
    26,738

     

    PS: I've omitted the NEB clause because I'm not sure whether [Order Count2] is guaranteed to be null whenever [Order Count] is null, depending on the relation between the date dimensions.
    Monday, April 21, 2008 3:17 PM
    Moderator

All replies

  • Hi,

     

    Will the following MDX achieve what you want?

     

    Generate(existings [Ship Date].[Fiscal].members,

    linkmember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    )

    )

    Friday, April 18, 2008 3:01 PM
    Answerer
  • Hi,

     

    I got:

    "#Error - Infinite recursion detected. The loop in dependencies order count2 -> order count2

    Code Snippet

     

    WITH MEMBER [Measures].[Order Count2]

    AS

    Generate(existing [Ship Date].[Fiscal].members,

    linkmember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    )

    )

    ,

    FORMAT_STRING = "#,#",

    NON_EMPTY_BEHAVIOR = [Order Count]

    select

    [Measures].[Order Count2] on 0

    from

    [Adventure Works]

    where

    {

    [Ship Date].[Fiscal].[Fiscal Year].&[2003],[Ship Date].[Fiscal].[Fiscal Year].&[2004]

    }

     

     

    Friday, April 18, 2008 3:08 PM
  • Sorry about this.  THE mdx I provided just returns a set, and we still need to aggregate the set against your [Measures].[Order Count] as follows.

     

    WITH MEMBER [Measures].[Order Count2]

    AS

    Aggregate(

    Generate(existing [Ship Date].[Fiscal].members,

    linkmember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    )

    ),

    (

    [Ship Date].[Fiscal].[All Periods],

    [Measures].[Order Count]

    )

    )

    Friday, April 18, 2008 4:54 PM
    Answerer
  • Same error still

     

    Monday, April 21, 2008 7:39 AM
  • Hmm..., I cannot tell where the infinite recursion come from.

     

    But if you can try the following to see if it makes any difference.

    1. Remove the where clasue

    2. Put the where clause in the set directly

     

    WITH MEMBER [Measures].[Order Count2]

    AS

    Aggregate(

    Generate({[Ship Date].[Fiscal].[Fiscal Year].&[2003],[Ship Date].[Fiscal].[Fiscal Year].&[2004]},

    linkmember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    )

    ),

    (

    [Ship Date].[Fiscal].[All Periods],

    [Measures].[Order Count]

    )

    )

     

    Monday, April 21, 2008 3:05 PM
    Answerer
  • Apparently the infinite recursion occurs because Generate() is trying to convert the LinkMember() tuple (without set braces) to a string. So explictly converting LinkMember() to a set seems to work:

     

    Code Snippet

    WITH

    MEMBER [Measures].[Order Count2] AS

    Aggregate(

    Generate

    (existing [Ship Date].[Fiscal].[Fiscal Year],

    {(LinkMember

    (

    [Ship Date].[Fiscal].currentmember,

    [Delivery Date].[Fiscal]

    ),

    [Ship Date].[Fiscal].[All Periods])}),

    [Measures].[Order Count]

    )

    ,

    FORMAT_STRING = "#,#"

    select

    [Measures].[Order Count2] on 0

    from

    [Adventure Works]

    where

    {

    [Ship Date].[Fiscal].[Fiscal Year].&[2003],[Ship Date].[Fiscal].[Fiscal Year].&[2004]

    }

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Order Count2
    26,738

     

    PS: I've omitted the NEB clause because I'm not sure whether [Order Count2] is guaranteed to be null whenever [Order Count] is null, depending on the relation between the date dimensions.
    Monday, April 21, 2008 3:17 PM
    Moderator
  • Thanks to both of you for looking at this. 

    Monday, April 21, 2008 3:27 PM
  • Thanks to both of you for looking at this. 


    Matt, Is this solved by anything above? Or are you still fighting with this?
    Monday, May 16, 2011 7:16 PM
  • Can someone tell me how to change the following query so that the @month variable can accept multiple months rather than just a single month using Linkmember? I have a big demo tomorrow and this is CRITICAL that I get this fixed. Thank you so much!

     

     SELECT NON EMPTY { [Measures].[CM PC OP Surgical Count], [Measures].[MB CM PC OP Surgical %], [Measures].[CM PC OP Surgical], [Measures].[CM PC OP Surgical %] }

    ON COLUMNS FROM ( SELECT ( STRTOSET(@DimLocationLocationDescription, CONSTRAINED) )

    ON COLUMNS FROM ( SELECT ( STRTOSET(@AttPhysChairperson, CONSTRAINED) )

    ON COLUMNS FROM ( SELECT ( LinkMember(STRTOMEMBER(@Month),[Date Adm].[Month]) ) ON COLUMNS FROM [Visit])))

    WHERE ( IIF( LinkMember(STRTOMEMBER(@Month),[Date Adm].[Month]).Count = 1, LinkMember(STRTOMEMBER(@Month),[Date Adm].[Month]), [Date Adm].[Month].currentmember ), IIF( STRTOSET(@AttPhysChairperson, CONSTRAINED).Count = 1, STRTOSET(@AttPhysChairperson, CONSTRAINED), [Att Phys].[Chairperson].currentmember ), IIF( STRTOSET(@DimLocationLocationDescription, CONSTRAINED).Count = 1, STRTOSET(@DimLocationLocationDescription, CONSTRAINED), [Dim_Location].[Location Description].currentmember ) )

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Tuesday, May 17, 2011 2:17 PM