Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Dynamic Aggregation

  1. #1
    Join Date
    Apr 2010
    Posts
    24

    Default Dynamic Aggregation

    Hi-

    I am being asked to create a report with the following consideration.

    1. The reports should have a line chart and the table for the data points plotted on the chart.
    2. Two input parameters :- start date and end date to pull the sales data between these two entered dates.
    3. Incase the difference between the two dates entered <= 3days, the chart should plot the raw data from the DB(DB has the hour level granuality). i.e. the chart and the report table should have hourly level information.
    4. Incase the difference is greater than 3 days but less than or equal to quater, then the data points should be rolled up to daily level, hence both the chart graph and the table have the daily data points.
    5. In case the date interval is greater than 90 days, the data points should be rolled up to weekly.

    I am new to pentaho hence I dont have much indepth knowledge of it. But this is what i have thought of

    1. to create a report with two input parameters (start date and end date)
    2. to have a open formula to compute the date difference between the two.
    3. to create three subreports (each for hourly, daily, weekly agg where each has a chart and the table).
    4. to make only the respective report visible and others to go invisible according to the interval selected.

    I have implemented this and it works too, but I believe there must be a better way to handle it in pentaho. I mean by creating only one chart/table for the whole report and just changing the datasource of it according to the parameters provided, thus reducing the effort of creating three subreports. is it?

    thanks in advance. :-)

    vineet

  2. #2
    Join Date
    Apr 2010
    Posts
    127

    Default

    I suppose you could use a custom query so that depending on the time span you can extract single (hourly) records or group by day/week. At that point tables and graphs would be based on the relevant data.
    You know how to build custom queries?

    BTW your implementations is what I first thought of

  3. #3
    Join Date
    Apr 2010
    Posts
    24

    Default

    SCP, thanks for your reply..but you guessed right, i am not aware how to write the custom queries, can you help? and also, please note that both table and the XY Time series chart should show the same aggregated data points.

    thanks again.

    vineet

  4. #4
    Join Date
    Apr 2010
    Posts
    127

    Default

    From the Data tab, you add a new data source as Advanced > JDBC (Custom), selecting the actual db connection.
    Then, in the Structure tab, the top of the hierarchy is Master Report, whose first attribute field is Query. There you click on Formula and insert ="query text", where you can concatenate parameters or functions calculated on them.
    If parameters are strings, make sure they're wrapped in apostrophes and apostrophes contained are properly escaped (beware of SQL injections).

    So you can write something like
    ="SELECT blah blah FROM blahtable GROUP BY " & IF([date2]-[date1]>90;"week";IF([date2]-[date1]>3;"day";"hour"))

    (not sure you can subtract dates)

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.