Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: EMCA Script for multi value list parameter

  1. #1
    Join Date
    Sep 2011

    Default EMCA Script for multi value list parameter

    Hi ,

    I am using EMCA script to change the query at run time or based on parameter selection.

    below is my country parameter query which is static and do not use EMCA :

    select '-None-' as country
    SELECT distinct
    FROM customer
    ORDER BY country

    And I need to display the states based on the selection of the country parameter, and if user selects "-None-" then I need to run the separate query, for that I have written below EMCA script:

    function computeQuery (query, queryName, dataRow)
    var sCountry=dataRow.get("sCountry");
    if(dataRow.get("sCountry") == '-None-')
    query=" select 'None1' as state_province";
    if(dataRow.get("sCountry") !='-None-')
    query="SELECT distinct `state_province` AS state_province FROM customer WHERE country IN (${sCountry}) ";

    if(dataRow.get("sCountry") == null)
    query="select '-None5-' as state_province";

    return query;

    Problem : When I use single select of country parameter then switching of query is working fine.
    but when i make country as multi valued parameter then only condition
    if(dataRow.get("sCountry") !='-None-') is getting executed , even we select "-None-" in the parameter value list.

    I think this might be because object data type returned by the country parameter, But I am not sure.

    Please help me to solve this problem , how switching of query can be performed when the filter is multiselect.


  2. #2
    Join Date
    Mar 2003


    When you receive a Multi-select parameter, you get an Array of values, not a single value.
    Therefore you have to change your conditionals to reflect that.

    The safest option is to
    (1) check for <null> first, return the computed query for that case.
    (2) check for an empty array and treat it the same as null, return that computed query for that case.
    (3) filter the arrays values for the "<null>" placeholder ("-None-") and store that result in temporary array
    (4) If that array is empty, the user has selected "-None", otherwise the user selected a valid number of countries. Return a appropriate query for that case.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Sep 2011


    Thanks Taqua,

    Should I store the values using

    var sCountry=new Array(dataRow.get("sCountry"));

    and then using a loop I need to find the "-None-".

    Or there is some other way to handle with array of values?

    Thanks again

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.