Pentaho

Hi,

Let's say that I have a sql (over sqlJndi) query (ClickHouse database) that collects options that I would like to present in multi select component.

For sake of simplicity I am using jsonScriptable over scripting as Multi select (multiSelectTest1) datasource:

{

"resultset":[

["All", 0],

["1-st", 1],

["2-nd", 2],

["3-rd", 3]

   ],

"metadata":[

{"colIndex":0,"colType":"String","colName":"name"},

{"colIndex":1,"colType":"Integer","colName":"value"}

   ]

}

Multi select component is tied to multiSelTest1Param simple parameter.

Aside of multi select I have created a “show param” button with the following Expression:

function f(){

    var param_val = dashboard.getParameterValue('multiSelTest1Param');

    var param_typ = typeof dashboard.getParameterValue('multiSelTest1Param');

    var param_message = param_val + ' ('+param_typ + ')'

document.getElementById('colShowParamMultiSelect').innerHTML = param_message;

    }

When I start the page, I have non-selected multi select. When I press the “show param” button I get “(object)” as a param_message.

Now I have created a new “set default” button that will set the default value of the multi select component with the following Expression:

function f(){

    dashboard.fireChange('multiSelTest1Param', 'All');

     }

After pressing “set default” button, “show param” shows “All (string)”, and “All” is properly selected in multi select component.

So, object type has changed from object to string now.

When I multi select let’s say “1-st” and “2-nd”, “show param” shows “1-st,2-nd (object)”, now it is object again.

When I unselect these two options, “show param” shows “(object)” again.

How to use parameter ${multiSelTest1Param} in query properly?

For example, I would like to have select like:

Select *

From table

Where field …something... ${multiSelTest1Param}

When nothing is selected, query should be executed as there is no where condition.

If single value is selected, query should be executed as “where filed = ${multiSelTest1Param}”.

If multiple values are selected, query should be executed as “where filed in [I assume] (${multiSelTest1Param})”.

If ${multiSelTest1Param} is of an object type, how to construct the select to properly respect its value?

I am really confused.

Regards.