none
MDX and Concatenation

    Question

  • I have a query that I need to change so I can concatenate the to columns together. I am an SQL guy and dont know MDX can someone please help

    The MDX I have which the system I am using generates

    Select non empty HIERARCHIZE(DISTINCT({Crossjoin({
    [Sales Items].[Item Code].[Item Code].Members},
    {[Sales Items].[Sales Item Name].[Sales Item Name].Members}
    )})) on rows,
    non empty {[Measures].[Sales Item Quantity]} on columns
    From [Sales]

    What I am trying to achive is joining the two columns of data together with a '-' inbetween

    sales.items.Sales.Item Name + '-' + Sales.Item.Item Code

    Example of the output I am looking for is

    CMLCAKE DEAL - 000005


    • Edited by Greg Walsh Thursday, July 04, 2013 11:53 PM
    Thursday, July 04, 2013 11:52 PM

Answers

  • Hi

    It is always better to do this at design time of source (atleast I like to do this at source) However we can get this with MDX like

    WITH MEMBER [Measures].[ItemName - Code] AS
    [Sales Items].[Sales Item Name].CurrentMember.Member_Caption + "-" + [Sales Items].[Item Code].CurrentMember.Member_Caption
    
    
    
    Select {[Measures].[Sales Item Quantity],[Measures].[ItemName - Code]} on columns
     , NON EMPTY { HIERARCHIZE(DISTINCT(
                        nonempty( [Sales Items].[Item Code].[Item Code].Members *
    						      [Sales Items].[Sales Item Name].[Sales Item Name].Members
    					        , [Measures].[Sales Item Quantity]
    					         )
                        )
                    ) 
    				} on rows
      
     From [Sales]


    Prav

    Friday, July 05, 2013 12:19 AM
  • Just drop a space on either side of what Prav sent you

    WITH MEMBER [Measures].[ItemName - Code] AS
    [Sales Items].[Sales Item Name].CurrentMember.Member_Caption + " - " + [Sales Items].[Item Code].CurrentMember.Member_Caption
    
    
    
    Select {[Measures].[Sales Item Quantity],[Measures].[ItemName - Code]} on columns
     , NON EMPTY { HIERARCHIZE(DISTINCT(
                        nonempty( [Sales Items].[Item Code].[Item Code].Members *
    						      [Sales Items].[Sales Item Name].[Sales Item Name].Members
    					        , [Measures].[Sales Item Quantity]
    					         )
                        )
                    ) 
    				} on rows
      
     From [Sales]


    Thanks! Josh

    Friday, July 05, 2013 1:10 AM

All replies

  • Hi

    It is always better to do this at design time of source (atleast I like to do this at source) However we can get this with MDX like

    WITH MEMBER [Measures].[ItemName - Code] AS
    [Sales Items].[Sales Item Name].CurrentMember.Member_Caption + "-" + [Sales Items].[Item Code].CurrentMember.Member_Caption
    
    
    
    Select {[Measures].[Sales Item Quantity],[Measures].[ItemName - Code]} on columns
     , NON EMPTY { HIERARCHIZE(DISTINCT(
                        nonempty( [Sales Items].[Item Code].[Item Code].Members *
    						      [Sales Items].[Sales Item Name].[Sales Item Name].Members
    					        , [Measures].[Sales Item Quantity]
    					         )
                        )
                    ) 
    				} on rows
      
     From [Sales]


    Prav

    Friday, July 05, 2013 12:19 AM
  • You are a god in my eyes. Thanks so much it work however one little thing I need a space either side of the -

    at the moment it displays product-0002222 I want it to be product - 0002222

    Friday, July 05, 2013 1:04 AM
  • Just drop a space on either side of what Prav sent you

    WITH MEMBER [Measures].[ItemName - Code] AS
    [Sales Items].[Sales Item Name].CurrentMember.Member_Caption + " - " + [Sales Items].[Item Code].CurrentMember.Member_Caption
    
    
    
    Select {[Measures].[Sales Item Quantity],[Measures].[ItemName - Code]} on columns
     , NON EMPTY { HIERARCHIZE(DISTINCT(
                        nonempty( [Sales Items].[Item Code].[Item Code].Members *
    						      [Sales Items].[Sales Item Name].[Sales Item Name].Members
    					        , [Measures].[Sales Item Quantity]
    					         )
                        )
                    ) 
    				} on rows
      
     From [Sales]


    Thanks! Josh

    Friday, July 05, 2013 1:10 AM
  • I'm wondering, would it be possible to do this concatenation but only include the first 10 characters of the [Sales Item Name] field?
    Monday, July 08, 2013 12:47 PM
  • Yes!

    Try this:

    WITH MEMBER [Measures].[ItemName - Code] AS
    LEFT([Sales Items].[Sales Item Name].CurrentMember.Member_Caption,10) + " - " + [Sales Items].[Item Code].CurrentMember.Member_Caption
    
    
    
    Select {[Measures].[Sales Item Quantity],[Measures].[ItemName - Code]} on columns
     , NON EMPTY { HIERARCHIZE(DISTINCT(
                        nonempty( [Sales Items].[Item Code].[Item Code].Members *
    						      [Sales Items].[Sales Item Name].[Sales Item Name].Members
    					        , [Measures].[Sales Item Quantity]
    					         )
                        )
                    ) 
    				} on rows
      
     From [Sales]



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

    Tuesday, July 09, 2013 6:37 AM
  • Raunuck, thanks for your reply.  I can't get this to work. I'll start a new thread on it.  -John

    Friday, July 12, 2013 3:00 PM