Not possible to pass comma-delimited list to a query via report parameter?

Hi,

I'm trying to figure out how to pass a list of names to a query in the reporting module. I want to put this report on a page and tie it to the view dataset of a power table, so that when a user selects only a couple of customer names to show in the table, the report will only look at those customer names as well.

WHERE c.CustomerName IN (?)

"'D&L Supply Northwest','D&L Supply TX'"
"D&L Supply Northwest, D&L Supply TX"

I've tried everything I can think of and nothing has worked so far. Any ideas would be appreciated. Is it even possible to do this?

This isn’t directly possible, due to the way JDBC parameter substitution works. You have a few possible options. The safest one is to run that query in a script; take the list of names you’re passing in as a single string, then split it yourself. Then construct your query with a fixed number of ? parameter placeholders, and call system.db.runPrepQuery to actually execute the query. The obvious downside is that this takes a fair amount more work.

The other approach is to use a ‘Basic’ SQL query type, rather than a SQL query - which lets you directly substitute parameters you’re given as strings into the query, before it’s evaluated. However, this is a big SQL injection risk, and therefore not recommended.

The Python route could look something like this:

rawParameter = data['nameParameter']

query = """
SELECT * 
FROM 
	customerData c
WHERE c.CustomerName IN (%s)
"""

splitParams = rawParameter.split(",")

placeholders = ", ".join("?" * len(splitParams))

data['myQuery'] = system.db.runPrepQuery(query % placeholders, splitParams)
2 Likes

I think I’ve almost got it using a script. I get no errors but I don’t get any data from the subqueries that are tied to the Query-ByCustomers data source. Can this still work if there are subqueries involved? Or maybe I’m not seeing my mistake?

Here’s the script:

	rawParameter = data['CustomerNames']
	startDate = data['StartDate']
	endDate = data['EndDate']
	
	splitParams = rawParameter.split(",")
	placeholders = ",".join("?" * len(splitParams))
	splitParams = ",".join(splitParams)
	splitParams = startDate+","+endDate+","+splitParams+","+startDate+","+endDate+","+splitParams
	splitParams = splitParams.split(",")
	
	query = """
	SELECT 
		x.CustomerID,
		x.CustomerName,
		COALESCE(FORMAT(x.OnTimeLines,0),0) AS OnTimeLines,
		COALESCE(FORMAT(y.LateLines,0),0) AS LateLines,
		COALESCE(x.OnTimeLines,0)/(COALESCE(x.OnTimeLines,0)+COALESCE(y.LateLines,0))*100 AS PercentOnTime
	FROM
	(SELECT 
		otl.CustomerID,
		c.CustomerName,
		COUNT(otl.DaysLate) AS OnTimeLines
	FROM
		partsdb.vw_ontimelines otl
		LEFT JOIN crm.customers c ON c.ID = otl.CustomerID
	WHERE
		otl.DaysLate <= 0
		AND otl.PromiseDate BETWEEN ? AND ?
		AND c.CustomerName IN (%s)
	GROUP BY otl.CustomerID) x
	LEFT JOIN 
	(SELECT
		otl.CustomerID,
		c.CustomerName,
		COUNT(otl.DaysLate) AS LateLines
	FROM 
		partsdb.vw_ontimelines otl
		LEFT JOIN crm.customers c ON c.ID = otl.CustomerID
	WHERE
		otl.DaysLate > 0
		AND otl.PromiseDate BETWEEN ? AND ?
		AND c.CustomerName IN (%s)
	GROUP BY otl.CustomerID) y ON y.CustomerID = x.CustomerID
	"""%(placeholders,placeholders)
	
	data['ByCustomer'] = system.db.runPrepQuery(query, splitParams)

Subqueries:
image

Result (there is a page for both customers):

What it should look like (there are 9 pages total):

There's extra complications if you're trying to put things properly into a subquery relationship; see @pturmel's post here:

1 Like

Thanks PGriffith, I’ll try that out.