Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to add more caption columns in Cube?

  1. #1

    Default How to add more caption columns in Cube?

    Hi everyone,

    I'm desiging a test cube with schema workbench. So far so good, my cube now looks like this
    Code:
    <Schema name="TestSchema">
    <Cube name="TestItemSales" visible="true" cache="true" enabled="true">
    <Table name="Item_Ledg_Entry"></Table>
    <Dimension type="StandardDimension" visible="true" foreignKey="Source No_" highCardinality="false" name="Teritory">
    <Hierarchy name="CustTeritory" visible="true" hasAll="true">
    <Table name="Customer"></Table>
    <Level name="CustCountry" visible="true" table="Customer" column="Country Code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"></Level>
    <Level name="CustCity" visible="true" table="Customer" column="Post Code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" captionColumn="City"></Level>
    <Level name="CustName" visible="true" table="Customer" column="No_" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" captionColumn="Name"></Level>
    </Hierarchy>
    </Dimension>
    <Measure name="SalesQtyb" column="Quantity" aggregator="sum" visible="true"></Measure>
    <Measure name="NoOfItems" column="Item No_" aggregator="distinct-count" visible="true"></Measure>
    </Cube>
    </Schema>
    Now I would like to have in the level "CustName" more caption columns. Currently it is only "Name", but need also "Name 2", since customer names are so long that they must be splitted into two fields. Do you have any idea how could I acchieve this? Thanks for your help.

  2. #2

    Default

    You should add a CaptionExpression to the Level in Schema Workbench, which achieves what you want. I believe it overrides the captionColumn value. For example, to have a user's first and last name as caption you would do:

    HTML Code:
    <Level name="CustName" visible="true" table="Customer" column="No_" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
      <CaptionExpression>
        <SQL dialect="generic">
           <![CDATA[Customer.firstName || ' ' || Customer.lastName]]>
        </SQL>
      </CaptionExpression>
     </Level>
    This assumes you have columns 'firstName' and 'lastName' in your Customer table.

    You can add a CaptionExpression via Schema Workbench by right-clicking on a Level and selecting 'Add Caption Expression'.
    Last edited by dknippers; 10-17-2012 at 10:05 AM.

  3. #3

    Default

    You should add a CaptionExpression to the Level in Schema Workbench, which achieves what you want. I believe it overrides the captionColumn value. For example, to have a user's first and last name as caption you would do:

    HTML Code:


    <Level name="CustName" visible="true" table="Customer" column="No_" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"> <CaptionExpression> <SQL dialect="generic"> <![CDATA[Customer.firstName || ' ' || Customer.lastName]]> </SQL> </CaptionExpression> </Level>
    This assumes you have columns 'firstName' and 'lastName' in your Customer table.

    You can add a CaptionExpression via Schema Workbench by right-clicking on a Level and selecting 'Add Caption Expression'.
    Almost perfect, I did change the SQL expression a bit, but you did point me to the right direction, much thanks. Here is the corrected part

    HTML Code:
    <Level name="CustName" visible="true" table="Customer" column="No_" type="String" uniqueMembers="true" levelType="Regular"hideMemberIf="Never">
    <CaptionExpression><SQL dialect="generic"><![CDATA[ CONCAT(Customer.Name,' ',Customer.`Name 2`) ]]></SQL></CaptionExpression></Level>

  4. #4

    Default

    Yeah the exact syntax depends on the DBMS you are using. In PostgreSQL the || operator does concatenation :-)

    Glad I could help!

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.