Pass string parameter to SQL over jdbc query in pentaho CDE

Question

I'm trying to pass a string parameter obtained by another query to another query in the same dashboard but when I use the parameter no results was selected. What is the proper syntax or where is the problem.

I'm beginner in pentaho biserver-CE. I use version 6.1. with JDBC connection to SQL Server 2016 SP1.

Now I'm making a sales dashboard from invoices and i want to make a dynamic filter by the time units (which works fine) and by the country shortcuts (which didn't work). If i pass shortcut of some country directly with quotes it works fine but when I replace it by the parameter it doesn't select nothing (Also in CDA preview). When I made some reports in Jasper i used exclamation inside of the parameter to pass quoted value but here I didn't find anything like this for Pentaho.

select  top 10
        invrow.item as ITEM,
    invrow.agent as AGENT,
    sum(invrow.qt) as MEASURE,
    sum(invrow.val) as VALUE
from    invrow
left join   invhead on invrow.type = invhead.type 
                and invrow.nr = invhead.nr
left join   art on invrow.item = art.item
where   left(invhead.date,4) = ${year}
and invhead.country like ${Country}
group by    invrow.item, invrow.agent, invhead.country
order by MEASURE DESC

parameter ${Country} was acquired by another query by the same field. There is a query to gain the parameter:

select distinct 
        invhead.country
from
        invhead
where
        left(invhead.date,4) = ${year}

The original query shows nothing but when I Replace parameter ${Country} by for example 'UK' like this.

It works fine:

select  top 10
        invrow.item as ITEM,
    invrow.agent as AGENT,
    sum(invrow.qt) as MEASURE,
    sum(invrow.val) as VALUE
from    invrow
left join   invhead on invrow.type = invhead.type 
                and invrow.nr = invhead.nr
left join   art on invrow.item = art.item
where   left(invhead.date,4) = ${year}
and invhead.country like 'UK'
group by    invrow.item, invrow.agent, invhead.country
order by MEASURE DESC

Now when I use parameter I have nothing in the select list but there are rows, which should be selected.

How many countries are you returning from the country query? — SS_DBA
number of countries depends on the selected time range because it differs in each year/month. The feasible values of this parameter are given dynamicaly by another select due to chosen time range, but most usual it is between 12 and 20 countries. — O.Gee

nsousa nsousa · Accepted Answer · 2019-07-31T15:06:44

The syntax is correct, but that way of passing parameters only works for single valued parameters.

To pass an array of parameters, as you seem to be trying, you need to pass the parameter as right hand side of a in operator,

(...)
and invhead.country in ( ${Country} )
(...)

and set the parameter to be of type StringArray.

Pass string parameter to SQL over jdbc query in pentaho CDE

1 Answers