Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Scheduling a report with dynamic date ranges

  1. #1
    Join Date
    May 2015
    Posts
    1

    Default Scheduling a report with dynamic date ranges

    Does anybody know whether it is possible to dynamically set parameters when scheduling a report to run?

    We have a report that has 2 parameters for a start and end date. In the report designer these default to the first and last day of the previous month. Running the report manually - works fine.

    However, when we then try and schedule the report to run each month - it uses the dates entered at the time the report was scheduled. There doesn't appear to be any way to have these calculated dynamically.

    Any help greatly appreciated.

  2. #2
    Join Date
    Jan 2016
    Posts
    1

    Default

    hello Team
    i have same issue. please help me


    Thanks in advance

  3. #3
    Join Date
    Apr 2008
    Posts
    4,402

    Default

    The usual solution to this (Since BI Server sets the parameters when scheduled and doesn't change them) is to use a parameter of "isScheduled" and then do some calculations in the SQL Statement:

    Select stuff where invoiceDate > (CASE WHEN ${isScheduled} = 1 THEN DATEADD(NOW(), -1, d) END ELSE ${StartDate})

    ***NOTE: UNTESTED SQL STATEMENT ABOVE ***
    ***This is given only as a pointer to the correct direction ***

  4. #4

    Default

    Some time ago Taqua suggest the use of a bolean parameter and duplicated parameters for start and end time, the steps are:

    1. Create a boolean parameter for example: manual_execution default FALSE
    2. Create duplicated parameters for the dates for example: if you have start_date and end_date, then create start_date_hidden and end_date_hidden.
    3. The secondary parameters called hidden should be hidden (omg surprise ) and the default values are you desire first and last day of last month.
    4. The main parameters remove default value and fill the Default Value Formula with: =IF([manual_execution];[start_date];[start_date_hidden])

    In that way you shedule the report with manual_execution in false, it'll take the values in hidden parameters for the report.
    When you execute the report manually you should mark that field to use the dates you choose.

Tags for this Thread

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 - 2017 Pentaho Corporation. All Rights Reserved.