Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Default "All" for a multivalue list parameter

  1. #1
    Join Date
    Sep 2007
    Posts
    834

    Default Default "All" for a multivalue list parameter

    I have a multivalue list parameter in my report.

    How do I implement an "All" option?

    I'd like all the options selected by default.
    Or, as another option, if the user doesn't select any option I'd like to include all the values in my query.
    Something like this: where myfield in (${my_parameter}) or ${my_parameter} =null/empty/ ??
    How do I do this?

    thanks,

  2. #2
    Join Date
    Dec 2014
    Posts
    4

    Default

    Hi Maria,

    One way I knew about this is you can do it with the help of another hidden parameter.
    First add ALL option to your parameter options.
    Example : Select X from Y union Select ALL from dual
    Add a parameter my_parameter and use the above query as a drop down list
    Now add a hidden parameter my_parameter2 and give default formula as if [my_parameter]="ALL";"%";[my_parameter].
    Use this my_parameter2 in your base query as "where myfield like ${my_parameter2}".
    So whenever you select ALL option my_parameter2 returns % and it will return all values in your query.

    Hope it helps.

  3. #3
    Join Date
    Sep 2007
    Posts
    834

    Default

    Thanks Vinodkumar
    I think your solution would work for a simple parameter, but this is a multiselect parameter.
    In my query I have "myfield IN ${my_parameter}"
    myfield IN % will not work

  4. #4
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Common answer is usually to do something similar to what Vinodkumar suggested, but use a key parameter value that is in the correct type...

    Then in your overall query:
    Select desired,columns,etc from myTable where ("ALL" = ${Multiselect} or MultiColumn in ${Multiselect} )

    Some DB engines will allow you to put a CASE in a where statement, but that runs the risk or killing your query indexes.

  5. #5
    Join Date
    Sep 2007
    Posts
    834

    Default

    Thanks Gutlez
    I'll try it

  6. #6
    Join Date
    Sep 2007
    Posts
    834

    Default

    It worked!

    In fact, this: "ALL" = ${Multiselect}
    didn't worked, but I replaced it with
    "ALL" IN (${Multiselect})



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.