Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Group by Month - How!

  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Default Group by Month - How!

    I have looked everywhere for info on how to create a report that groups the results by month - right now my report is grouping by the full date stamp so each record is it's own group.

    Can anyone tell me how to do this and is there a user doc for Report Designer 1.7.0???

    Thanks,

    Tim

  2. #2
    Join Date
    Oct 2006
    Posts
    219

    Default Data Warehouse

    I think you may want to look into creating a data warehouse using Pentaho Data Integration.

    The idea is simple. If you have records that include a date timestamp you can take this data and place it into a data warehouse. Using the timestamp field you can create extra fields for this data like the day, month, year et

    Once you have these extra fields in your data warehouse you will be able to query your data grouped by month and then use that data in report groups.

    I would suggest you look into Data warehousing on google or something. This will really make you BI life run alot more smoothly.

    Hope that gets you started!

    Harris
    www.ivy-is.co.uk - Ivy Information Systems

    Come join the non official Pentaho IRC chat room on irc.freenode.net in the ##pentaho channel

  3. #3
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Actually, for that requirement there is no dataware-house needed.

    You need:

    (1) Sort your data by the date.
    (2) Add a "message-format expression" to the report with the pattern set to "$(your-timestamp-field, date, yyyyMM)"
    (3) Add a Group that uses the name of the message-format-expression as field on which to group on

    The message format expression will format your timestamp by year and month and thus all timestamps of the same year/month will yield the same text and thus allow the grouping to work.

    Make sure that your database actually returns a DATE and not a NUMBER, or you will run into trouble. If the column in your database is declared to be DATETIME or TIMESTAMP, then you will be fine, but the whole process will fail if the column is declared as INTEGER or NUMBER column.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4
    Join Date
    Apr 2008
    Posts
    4

    Default

    Thanks for your help - got it working...

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.