Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Relative Date Range - Scriptable Data Source

  1. #1

    Default Relative Date Range - Scriptable Data Source

    Hello,
    I am trying to create a parameter where the user would choose a relative date, ie "yesterday", "last week", "this month", etc. I created a "scriptable" beanshell datasource (posted here) that has three columns: key, date from, date to.

    My original plan was to create a dropdown parameter for the "key", and then two hidden parameters (date from and date to) that would have the display name = key and the value = DateFrom / DateTo respectively. It will have the default value set to the value choosen for the "key". I am pretty sure this won't work though, since the value is being set, not the display name.

    My question is if anyone has any ideas that I could use to create a relative date parameter? I thought I was heading in the right direction with the scriptable datasource, however I have hit a roadblock.

    Any suggestions would be most welcome!

    Thanks,
    Dan

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

    Default

    I guess all you need is a table datasource, with a int-key and a user-readable text

    Id Value
    1 Today
    2 Yesterday
    3 Current Month


    and then two hidden parameter with formulas that use the Choose-Function to select how to compute the date.

    Something like:

    Start:
    =CHOOSE([Id]; DATEVALUE(Now()); DATEVALUE(Now() - 1); DATE(1; MONTH(Now()); YEAR(Now()))

    End:
    =CHOOSE([Id]; DATEVALUE(Now()); DATEVALUE(Now() - 1); DATE(1; MONTH(Now()) + 1; YEAR(Now()))


    or so.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default Thanks!

    Thanks Taqua, that worked a charm. For future reference, here is the formula I used:

    Date From:
    Code:
    =CHOOSE([relative_date]; DATEVALUE(NOW());  DATEVALUE(NOW()-1);  DATEVALUE(NOW()-7);  DATEVALUE(NOW()-30); DATEVALUE(NOW()-90);  DATEVALUE(NOW()-WEEKDAY(NOW())+1); DATEVALUE(NOW()-WEEKDAY(NOW())-6); DATEVALUE(NOW()-DAY(NOW())+1); DATE(YEAR(NOW());1;1); DATE(YEAR(NOW())-1;1;1)      )
    Date To:
    Code:
    =CHOOSE([relative_date]; DATEVALUE(NOW());  DATEVALUE(NOW()-1);  DATEVALUE(NOW());  DATEVALUE(NOW()); DATEVALUE(NOW());   DATEVALUE(NOW()); DATEVALUE(NOW()-WEEKDAY(NOW())); DATEVALUE(NOW()); DATEVALUE(NOW()); DATEVALUE(DATE(YEAR(NOW());1;1)-1)      )
    And the RelativeDates table datasource looks like:
    1 | Today
    2 | Yesterday
    3 | Past 7 Days
    4 | Past 30 Days
    6 |Past 90 Days
    7 | This Week
    8 | Last Week
    9 | MTD
    10 |YTD
    11| Last Year
    A bit of a pain to manage, but hey, if it works then I can't complain!!

    Thanks again,
    Dan

  4. #4
    Join Date
    Mar 2014
    Posts
    3

    Default

    Hi Taqua,

    This is really a good solution to me. Except the relative date,I also have another requirement that we need to let the user choose the From date and End date using a date picker after select "customize" in the dropdown filter . And the date picker should not be displayed only if the user select the "customize".

    I was simply thinking of adding a column named "customize" to the table datasource......and thinking of when user chosen the "customize", then the date picker display.And user can customize the date as he want.

    but I don't know how to combine this with the relative date using the same params?
    And how to write the javascript to control the display of the date picker?

    It would be very appreciate that you can offer me some advice about how to do 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.