Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Formatting a date in a dimension member

  1. #1
    Join Date
    Mar 2006

    Question Formatting a date in a dimension member

    I have a time dimension with Year, month and week levels. The unique thing is for the Week level the database field that I display is actually a single date value representing the "week ending date". So in the datbase each group of consectuive 7 days will have the same WeekEndingDate value in the datbase.

    When I use the Week level in Jpivt, the column heade displays the WeekEndingDate value in "YYYY-MM-DD" format, and I want to be able to display it in "MM/DD/YY" format ? I assume I need to do something in my mondrian.schemea.xml file.

    I originally had the the levelType on th "Weeks" leve set as "TimeWeeks", then I tried "TimeDays" to see if that would change anything.

    Here is the Dimension definition:

    <Dimension name="Fiscal Time" foreignKey="DBNDATE">
    <Hierarchy name="Fiscal Weekly" hasAll="true" allMemberName="All Weeks" primaryKey="Date">
    <Table name="Dim_FiscalWeekly_Time"/>
    <Level name="Years" column="Year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
    <Level name="Months" column="MonthName" uniqueMembers="false" ordinalColumn="MonthNbr" levelType="TimeMonths"/>
    <Level name="Weeks" column="WeekEnd" uniqueMembers="false" levelType="TimeDays"/>

  2. #2
    Join Date
    Jul 2007


    I can't remember if the format string property can be added to the XML on levels which would be the obvious answer.
    The other option is to use a member formatter (
    This allows you to be able to write your own code to format the value as you want.

    Everything is hard before it is easy

  3. #3
    Join Date
    Mar 2006

    Smile Solution found:

    I was able to find somevailed references to something called a captionColumn in the modrian schema. Basically I added an additional column to the database table housing my time demsion that conatined the date in a varchar type, formatted the way I wanted it displayed, "MM/DD/YY". The in the modrian schema file for my Time dimension I added the captionColumn="<new varchar date column name>" and it displayed that column's data as the headiing.

    So the entry in the schema became:

    <Level name="Weeks" column="WeekEnd" captionColumn="WeekEndDisplay" uniqueMembers="false" levelType="TimeDays"/>

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.