Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to make Date parameters optional

  1. #1
    Join Date
    Jul 2012
    Posts
    27

    Default How to make Date parameters optional

    Hi All,

    I am using pentaho report designer 3.9.0.
    I have BeginDate and EndDate parameters and I want it to be optional. That is user can either select them or user can also run the report without selecting both of them. My query runs like:
    Select date,sum(txns)
    from table
    where date between ${Begindate} and ${EndDate}
    group by date.

    so basically is there any property to be set for parameters which can make them optional? Like we have "Mandatory" check box don't we have optional? And if not then how the where clause of the query should be changed so that if the user does not select parameters values the query runs.

    Please let me know.

    Regards,
    Anush

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

    Default

    If a parameter is not mandatory, then it is optional.

    Your best bet would be to create a hidden parameter (lets call it "UnsetFlag") that tests whether the parameter are set, by using this post-processing formula:
    Code:
    =OR(ISNA([Begindate]); ISNA([EndDate]))
    and then in your where clause, reference that new parameter:

    Code:
    where ${UnsetFlag} = TRUE OR (date between ${Begindate} and ${EndDate})
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jul 2012
    Posts
    27

    Default

    Hi Taqua
    Thanks for your help.. I tried doing the way you suggested. However I could not achieve what i want. basically I have two sets of prompts. one is Begindate and enddate, while other date range prompt. the value of this prompt depends on sysdate. The lov is : yetserday,previous week,previous month etc. So when user selects yesterday then report automatically runs for 21-AUG-2012 considering today is 22-AUG-2012.

    Well, my problem is when user does not select begin date and end date then user will select date range. So in query I want according to your above logic.
    Where case ${unsetflag} when 'true' then (complete date range logic) else date between ${Begindate} and {$enddate}.
    However this is not working. Please provide some insights.
    Waiting for your positive response..

    Thanks,
    Anush

  4. #4
    Join Date
    Jul 2012
    Posts
    27

    Default

    Hi taqua,

    Thanks for your reply. I tried the way you suggested however there is one more thing I am trying to implement. I have two sets of parameters:begin date and end date and other one is date range parameters. my date range parameter consists of values like yesterday,previous week,previous month etc. so when user selects yesterday then report will automatically fetch data for yesterday depending on the current date. so if current date is 22-AUG-2012 and user selects yesterday then report will fetch data for 21-AUG-2012.

    Well, now the issue is when user selects date range it will not select begindate and enddate parameter. SO as per your logic if, ${unsetflag} is true then date range logic should run. So it should be like:

    where case ${unsetflag} when 'true' then (complete date range logic will come) else date between ${begindate} and ${endate}
    I tried modifying the where clause as above but it did not work. Please provide some insights on the same. Waiting for positive response.

    Thanks,
    Anush

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.