Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Pentaho Report designer- different timezones?

  1. #1
    Join Date
    Jun 2016
    Posts
    5

    Smile Pentaho Report designer- different timezones?

    Hi,

    I have report generated with connection to mysql database and table contains the column date.Client wants to have filter for different timezones and based on that date it should automatically convert the existing date according to timezone selected.Is it possible on front end without doing any manipulation in backend tables?

    Please help me out if anyone has any clue on this.Any help is appreciated.

  2. #2
    Join Date
    Aug 2016
    Posts
    142

    Default

    In your query use a convert_tz function on the table timestamp in combination with some query scripting

    So in your query use something like this

    CONVERT_TZ (TableColumn,'dbServerTimezone','FILTER Timezone')

    Then in query scripting select Groovy and use something like this

    Code:
    def computeQuery (query, queryName, dataRow)
    {
         query = query.replaceAll("FILTER Timezone", dataRow.get("parameter1"))
         return query
    }
    You will need to create a parameter and whatever it is called replace parameter1 above with it

    This parameter can then be powered by a series of simple select statements that are joined with a UNION so something like

    S E L E C T 'UTC' as timezone
    UNION
    S E L E C T 'Europe/Paris'

    Hopefully that makes sense

    **I had to put spaces in the word select as the forum does not like the full word

  3. #3
    Join Date
    Jun 2016
    Posts
    5

    Default

    Thanks a lot for the answer i will try this.

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.