Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: nameColumn composite of two columns

  1. #1
    Join Date
    Aug 2016
    Posts
    290

    Default nameColumn composite of two columns

    I want to show nameColumn for a dimension as composite of two strings from two columns.

    Example: nameColumn="FirstName" + " " + "LastName"

    So the pivot grid would show for example "Thomas Anderson".

    How to concatenate strings like this in mondrian schema?

  2. #2
    Join Date
    Aug 2006
    Posts
    287

    Default

    Using a sql expression to concatenate the two fields

  3. #3
    Join Date
    Aug 2016
    Posts
    290

    Default

    Ok, how would that look like exactly?

    Lets say I want the description for this dimension to be concatenated from two columns (COUNTRY & NAME):

    <!-- CustomerIDs -->
    <Dimension name="CustomerIDs" type="StandardDimension">
    <Hierarchy hasAll="true" primaryKey="ID" name="CustomerID" allLevelName="(ALL)">
    <Table name="DIM_OPERATORS"/>
    <Level name="Name" column="ID" nameColumn="NAME" ordinalColumn="NAME" type="Integer"
    uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>

    How can I set nameColumn to sql query?

    <Level name="Name" column="ID" nameColumn="SELECT CONCAT(DIM_OPERATORS.NAME, DIM_OPERATORS.COUNTRY) AS CONCATENATED_NAME FROM DIM_OPERATORS;" ordinalColumn="NAME" type="Integer"
    uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
    </Level>

  4. #4
    Join Date
    Aug 2006
    Posts
    287

    Default

    More like this:
    <Level name="NameLevel" visible="true" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    <KeyExpression>
    <SQL dialect="mysql">
    <![CDATA[CONCAT(`customer`.`lname`, ' ', `customer`.`lname`)]]>
    </SQL>
    </KeyExpression>
    </Level>

  5. #5
    Join Date
    Aug 2016
    Posts
    290

    Default

    Big thanks!

  6. #6
    Join Date
    Aug 2016
    Posts
    290

    Default

    That was very useful pointing me in the right direction! There was one problem when applying the solution of KeyExpression to multiple dimensions.

    Here the two columns have foreign key to the same dimension table, for instance Provider and Customer.

    While I was able to display a composite name from two columns for one dimension (Provider or Customer), I was not able to display composite for both. It resulted in error from Mondrian.

    Instead I used "CaptionExpression" which worked perfect.
    Last edited by Sparkles; 01-30-2017 at 11:45 AM.

  7. #7
    Join Date
    Aug 2006
    Posts
    287

    Default

    Perfect; this is good to know

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.