none
Union different dimensionality data MDX

    Question

  • Hi Friends,

    I have a small issue. I was trying to UNION different dimensionality data like this 

    WITH MEMBER [Segment].[Segment].[CK] AS 
    			(
    				  [Segment].[Segment].[All],
    				  [Property].[Propertdetails].&[CK]
    				)
         MEMBER [Segment].[Segment].[PR] AS 
    			(
    				[Segment].[Segment].[All],
    				[Property].[Propertdetails].&[PR]
    				)
    
    SELECT Measures.[Amount] ON COLUMNS,
    
    UNION(
           [Segment].[Segment].[Segment].Members,     
           {
           [Segment].[Segment].[CK],
           [Segment].[Segment].[PR]
           }
           ) ON ROWS 
           
     FROM [Cube]

    It is working fine for mentioned member like CK and PR.

    I have many members under this attribute  [Property].[Propertdetails] ( it has 20 members might change in the future)

    I can't hard code for all the members like I mentioned in the code.

    The Query needs to take the values dynamically. Is there anyway to get the values dynamically?

    Please let know. Thanks for your help.

    Wednesday, November 20, 2013 4:11 PM

All replies

  • Hi Svia,

    Base on your description, you want to take the values dynamically and then union it, right? Please try the query below.

    WITH MEMBER [Segment].[Segment].[Union1] AS 
    			StrToMember("(
    				  [Segment].[Segment].[All],
    				  [Property].[Propertdetails].&["+@Parameter1+"]
    				)")
         MEMBER [Segment].[Segment].[Union2] AS 
    			StrToMember("(
    				[Segment].[Segment].[All],
    				[Property].[Propertdetails].&["+@Parameter2+"]
    				)")
    
    SELECT Measures.[Amount] ON COLUMNS,
    
    UNION(
           [Segment].[Segment].[Segment].Members,     
           {
           [Segment].[Segment].[Union1],
           [Segment].[Segment].[Union2]
           }
           ) ON ROWS 
           
     FROM [Cube]

    Regards,

    Charlie Liao
    TechNet Community Support

    Thursday, November 21, 2013 7:16 AM
    Moderator
  • Thanks for your reply. I don't want to pass the parameters.

    Just that needs to look at all the members under that attribute and pull the member which does have values and should replace Union1 name with that member name.

    Thanks for your help.

    Thursday, November 21, 2013 10:22 AM
  • Thanks for your reply. I don't want to pass the parameters.

    Just that needs to look at all the members under that attribute and pull the member which does have values and should replace Union1 name with that member name.

    Thanks for your help.

    The way I see it, use 2 openquery statements and dump the result set of the MDX queries in two temporary tables. Use same alias names for the column and use SQL UNION operator.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Thursday, November 21, 2013 12:10 PM