Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: CSVTEXT with Dates and In Clause

  1. #1

    Default CSVTEXT with Dates and In Clause

    I am somewhat new to this but I am just not getting it.

    I have a report that prompts for a multi select value and builds the report.

    The Date is passed back to the query via a Parameter. Single dates work fine. When multiple dates are selected it errors out at me.

    I am attempting to figure out how to modify the Parameter "Dates"
    to be passed to the

    eventstartdate in (${Dates}) statement in the sql statement via the
    default Value Formula using the CSVTEXT option.

    Current values of Dates is 2010-06-15

    End Goal is
    eventstartdate in (${Dates})
    or with values
    (eventstartdate in ('2010-06-15','2010-06-14')

    What do I put for the Formula?

    CSVTEXT([Dates]; logical; ',';) Does not seem to work


    Thank you

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

    Default

    No, if you use a SQL-in clause with a multi-selection parameter, then do *NOT* use something like CSVTEXT. CSVText produces a single string and then of course, your database tries to find that single string - and of course will fail as strings are not nicely matchable to dates.

    Whats wrong with just writing

    WHERE eventstartdate in (${Dates})

    assuming "Dates" is your multiselect date parameter with [NO!!!!] post-processing formula on it.


    A note on the post processing formulas: The formula is executed before any validation is done - so if your formula returns a string for a parameter whose value type is declared as date, then validation will fail, as a string can never be matched as a date.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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.