none
Upper case function in MDX

    Question

  • Hi Experts,

    I would like to convert Upper cases to First Letter Upper remaining Lower and where space Occurs then Upper and Lower.

    at the Moment I have letters like this JOHN BRAIN

    So I would like to convert them into John Brain

    this is my code

    WITH MEMBER [Measures].[ParameterCaption] 
    AS LCASE([Dim_Sales].[Name].CURRENTMEMBER.MEMBER_CAPTION )
    
    SELECT {[Measures].[ParameterCaption]} 
    ON COLUMNS , 
    [Dim_Sales].[Name].[Name]
    
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS  
    
    FROM [Cube] 

    Can anyone Guide me through please

    Thanks,

    RK

    Monday, December 10, 2012 11:22 AM

Answers

  • Hi there,

    You could VBA!StrConv([Dim_Sales].[Name].CURRENTMEMBER.MEMBER_CAPTION,3)

    Otherwise convert it using functions in SQL Server with a named query in the data source view and exposure a member attribute that is proper case.

    cheers,

    Andrew


    Andrew Sears, T4G Limited, http://www.performancepointing.com

    • Marked as answer by BI Siva Monday, December 10, 2012 3:13 PM
    Monday, December 10, 2012 3:04 PM

All replies

  • Hello,

    You can achieve this by writing a custom assembly and add this to SSAS database. Then reference the function in the MDX.

    E.g. :

    WITH MEMBER [Measures].[ParameterCaption] 
    AS MyAssebly.ConvertToCamelCase([Dim_Sales].[Name].CURRENTMEMBER.MEMBER_CAPTION )
    
    SELECT {[Measures].[ParameterCaption]} 
    ON COLUMNS , 
    [Dim_Sales].[Name].[Name]
    
      DIMENSION PROPERTIES 
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS  
    
    FROM [Cube] 

    The following URL would give you details on codebase for assembly in C#:

    http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase.aspx

    Hope this helps.

    Thanks,

    Ranjith

    Monday, December 10, 2012 11:56 AM
  • Thanks for your reply. I don't want to make complex process.

    I was using this just for one report. is there any other to achieve this by using MDX?

    Please let me know

    Thanks, 

    RK

    Monday, December 10, 2012 12:01 PM
  • Hello,

    We do not have any built-in functions which does this in MDX. I think custom assembly is the only possiblity and this would get you the right title case conversions for all the captions possiblie.

    Thanks,

    Ranjith

    Monday, December 10, 2012 12:14 PM
  • Hi there,

    You could VBA!StrConv([Dim_Sales].[Name].CURRENTMEMBER.MEMBER_CAPTION,3)

    Otherwise convert it using functions in SQL Server with a named query in the data source view and exposure a member attribute that is proper case.

    cheers,

    Andrew


    Andrew Sears, T4G Limited, http://www.performancepointing.com

    • Marked as answer by BI Siva Monday, December 10, 2012 3:13 PM
    Monday, December 10, 2012 3:04 PM