Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Multivalue parameter

  1. #1
    Join Date
    Jan 2013
    Posts
    100

    Default Multivalue parameter

    Hi. I am using a multivalue parameter. As a data source using the PDI transformation. How to pass a parameter to PDI in the form:
    'value1', 'value2','value3'
    Then, in the PDI use this parameter in the query WHERE IN (${Parameter})

  2. #2
    Join Date
    Jan 2013
    Posts
    100

    Default

    I am currently working under such scheme:
    1. Set the TextBox parameter (for example param = foo, bar).
    2. In PRD execute SQL-query:
    SELECT
    .....
    FROM Test_Function (${param})
    3. In DWH there is a table function which does the following:
    ALTER FUNCTION Test_Function (@param)
    ....
    SELECT
    ....
    WHERE foobar IN (SELECT fld FROM StringToTable(@param, DEFAULT))
    4. Function StringToTable converts a string 'foo, bar' into a table (array):
    fld
    foobar

    This method is working correctly. But how to do that would be possible to use MultiValue List instead of a TextBox? MultiValue List passes array into the function, but it accepts a string. I tried to apply postprocessing function for the parameter =CSVTEXT([param]), but it does not work.

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

    Default

    PDI itself only accepts plain strings. So even if you have a multi-selection parameter in the report, you still have to transform the selection into a plain string before you can use it in Kettle.

    One way of doing this is to add a hidden parameter to your report, that is of type string, and has a post-processing formula of =CSVTEXT([yourMultiSelectParameter])

    Then inside kettle you can use scripting or other means to parse that CSV string back into whatever form you need for your query.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4
    Join Date
    Jan 2013
    Posts
    100

    Default

    Quote Originally Posted by Taqua View Post
    One way of doing this is to add a hidden parameter to your report, that is of type string, and has a post-processing formula of =CSVTEXT([yourMultiSelectParameter])
    Thank you. I thought the formula should be used in the same parameter that is passed to sql. I created a new parameter and everything worked.

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

    Default

    If you use the post-processing formula on a parameter, the result of that formula *becomes* the new parameter value. This is useful if you want to correct invalid values, but can become outright wrong if your code goes back and forth to the UI multiple times.

    So what happens is:

    (1) You select a parameter
    (2) The post processing is called, modifies that value
    (3) the modified value is now the current value for that parameter and is sent back to the UI/user.
    (4) Go back to step 1


    A simple experiment to see this in action would be to simply create a text-parameter with the following post-processing formula

    =[parameter] & "a"

    Each time you submit the report, another "a" will be added to your parameter value and the text-input on the parameter-form.


    More useful uses for same-parameter-post-processings are things like expanding parameter entries or searches, rounding or formatting numbers and dates or for search and replaces (like accepting the word "yesterday" and returning the date-string of yesterday's date).

    =IF([parameter] = "yesterday"; DATEVALUE(NOW() - 1); [parameter])
    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.