Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Multi Valued Parameter in MDX RS Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2008 1:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 11:28 PM
Points: 1, Visits: 48
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
Post #514203
Posted Thursday, September 25, 2008 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2008 2:47 PM
Points: 4, Visits: 34
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.
Post #576351
Posted Tuesday, October 27, 2009 11:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:19 AM
Points: 64, Visits: 246
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
Post #809486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse