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.
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)
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)
