Using Multi Valued Parameter in MDX RS

• sipho.nombebe

  Valued Member

  Points: 71

  More actions

  June 10, 2008 at 1:39 am

  #188738

  Hi,

  I have a report where usesr can select Multi valued Parameter and I use a join in my mdx code like this:

  ="SELECT NON EMPTY { [Measures].[Alternate Single Manager],

  [Measures].[Retail Structured]} ON COLUMNS,

  NON EMPTY { ([Agent].[Agent Consultant].[Consultant Id].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( " & Parameters!FundFund.Value & ") ON COLUMNS FROM ( SELECT ( " & Join(Parameters!TransactionTypeTransactionTypeHierarchy.Value,",") & ") ON COLUMNS FROM [Cube]))))

  WHERE ( " & Join(Parameters!TransactionTypeTransactionTypeHierarchy.Value,",") & "," & Parameters!FundFund.Value & ")

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

  If the user select Multi Valued Parameter, the query returns an error : The 'Transaction Type Hierarchy' hierarchy appears more than once in the tuple.

  The mdx code that gets build looks like this:

  SELECT NON EMPTY { [Measures].[Alternate Single Manager],

  [Measures].[Retail Structured] } ON COLUMNS,

  NON EMPTY { ([Agent].[Agent Consultant].[Consultant Id].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( [Fund].[Fund].[All]) ON COLUMNS FROM ( SELECT ( [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],[Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733]) ON COLUMNS

  FROM [Cube])))

  WHERE ( [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],[Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733])

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

  Please help on this

  Sipho

• Nicholas Large

  SSC Enthusiast

  Points: 136

  More actions

  September 25, 2008 at 1:15 pm

  #877294

  Hi.

  You need to specify the TType as a set:

  SELECT NON EMPTY { [Measures].[Alternate Single Manager],

  [Measures].[Retail Structured] } ON COLUMNS,

  NON EMPTY { ([Agent].[Agent Consultant].[Consultant Id].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( [Fund].[Fund].[All]) ON COLUMNS FROM ( SELECT ( [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],[Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733]) ON COLUMNS

  FROM [Cube])))

  WHERE ( [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],[Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733])

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

  becomes

  SELECT NON EMPTY { [Measures].[Alternate Single Manager],

  [Measures].[Retail Structured] } ON COLUMNS,

  NON EMPTY { ([Agent].[Agent Consultant].[Consultant Id].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( [Fund].[Fund].[All]) ON COLUMNS

  FROM ( SELECT ( { [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],

  [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733] } ) ON COLUMNS

  FROM [Cube])))

  WHERE ( { [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1732],

  [Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[1733] })

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

  You also do not need the where clause, you already have the data in the subselect.

  Hope this helps,

  Nick.

• ashish sinha-404083

  SSC Veteran

  Points: 296

  More actions

  October 27, 2009 at 11:28 am

  #1071358

  Hi,

  How did you get the first part of the parameter, that is - "[Transaction Type].[Transaction Type Hierarchy].[Transaction Type Detail].&[".

  I am struggling with that.

  Please help.

  Regards,

  Ashish Sinha