Pentaho CDE中JDBC传递字符串参数至SQL查询无结果问题求助

Hey there! Let's work through why your dynamic country parameter isn't returning results in your sales dashboard. The core issue here is how Pentaho handles string parameter substitution compared to Jasper, so let's break down the fixes and checks you need to do.

Key Problem: Missing Quotes Around String Parameters #

When you hardcode 'UK' in your query, SQL Server recognizes it as a string literal. But when you use ${Country}, Pentaho inserts the raw parameter value (like UK without quotes) into the query. This makes SQL Server treat UK as a column name instead of a string, hence no matching results.

Solutions to Try #

1. Use Pentaho's Built-in quote() Function (Recommended) #

Pentaho has a handy function to automatically wrap string parameters in single quotes and escape any internal single quotes (preventing SQL injection and syntax errors). Modify your WHERE clause to use ${quote(Country)}:

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 = ${quote(Country)} 
group by invrow.item, invrow.agent, invhead.country 
order by MEASURE DESC

Note: I switched like to = since you're matching exact country codes—use like '%${quote(Country)}%' only if you need fuzzy matching.

2. Manually Add Single Quotes (Quick Test) #

If you want to verify the quote issue first, you can wrap the parameter in single quotes directly in the query:

invhead.country = '${Country}'

This works for simple cases but isn't as safe as quote() if your parameter values might contain single quotes (e.g., Cote d'Ivoire).

3. Verify Parameter Configuration in CDA #

Double-check your parameter setup in the CDA file:

• Ensure the parameter type is set to String (not Numeric or any other type).
• Confirm the parameter query select distinct invhead.country from invhead where left(invhead.date,4) = ${year} returns raw country codes (e.g., UK, not 'UK'—the quotes should be handled in the main query, not the parameter source).

Troubleshooting Steps #

• Check Generated SQL: Enable Pentaho's SQL logging to see the exact query sent to SQL Server. This will show you if the parameter is being substituted correctly (with or without quotes).
• Test Parameter Value: Add a debug line to your query to inspect the parameter value:

  select '${Country}' as debug_param, top 10 invrow.item as ITEM...
  

  Run this in CDA preview to confirm the parameter is being passed as expected.

内容的提问来源于stack exchange，提问作者O.Gee