Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: formatting a date in a level

  1. #1

    Default formatting a date in a level

    Hi,

    I have a date dimension, that has a 'Date' level. Its linked to my database table dim_date, that has dates stored as timestamps (2013-02-26 00:00:00)

    Now i want to publish this cube to the console, and have this date display just the date part (2013-02-26)

    How can i use the 'formatter' in the schema workbench's level tab to change the format of my field to dd-mm-yyyy ?

    I have tried dd.mm.yyyy, and also tried adding an annotation (maybe i'm doing it wrong)

    But i still see the entire date+time in the console.

    If someone could please help with the steps to change a date format in the schema workbench - that would be great !

    Thanks a lot !

  2. #2
    Join Date
    Aug 2007
    Posts
    151

    Default

    Sue, we're facing the same need: customer wants to a column to show the full date after analysing the cube. We have tryied basically the same things you had also to no avail. Have you ever solved it? Thanks, Fábio.

  3. #3
    Join Date
    Jan 2013
    Posts
    796

    Default

    Couple options:

    1) Brute force it in the SQL expression used for the name. E.g.

    <Level name="Day" column="the_date" uniqueMembers="false" type="Timestamp"
    levelType="TimeDays">
    <NameExpression>
    <SQL>
    DATE_FORMAT(the_date, '%Y-%m-%d')
    </SQL>
    </NameExpression>
    </Level>

    2) Implement a custom member formatter. This is simpler than it may sound. The MemberFormatter interface includes a single .formatMember method which returns the formatted string you'd like to see.

    String formatMember(Member member);

  4. #4
    Join Date
    Aug 2007
    Posts
    151

    Default

    Thanks, Campbell, we'll try it. (Mondrian 3, right? BI Server 4.x?)

    BTW, there seems to be a lot of usefull things one can do with the custom member formatter. I am at a total loss about it. Do I have to create a Java class, compile into a .class and then add to Mondrian JAR by hand?

    And how to use it? Like, do I have to enter "String formatMember(Member member);" in the formatter field on PSW? (Please see attached image.)

    Best reggards, Fábio.

    Name:  UsageOfFormatMember.jpg
Views: 500
Size:  43.9 KB

  5. #5
    Join Date
    Jan 2013
    Posts
    796

    Default

    That's right, Mondrian 3. Any BI Server version.

    And yes, to create a custom member formatter you need to compile a Java class. It doesn't need to be added to the Mondrian jar, just make sure it's available on the classpath. It cannot be done in PSW-- that just sets the schema attribute, which should reference the class name. If you're not too familiar with Java programming you'd probably want to brush up on some basics first. The actual class would end up being pretty straightforward, though--here's a very simple example:

    public static class FooBarMemberFormatter implements MemberFormatter {
    public String formatMember(Member member) {
    return "foo" + member.getName() + "bar";
    }
    }

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.