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

**URL:** <https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632>\
**Category:** Ignition\
**Created:** [September 27, 2019, 11:07pm UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632 "2019-09-27T23:07:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![danieltalbot](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/danieltalbot/32/11201_2.png) [@danieltalbot](https://forum.inductiveautomation.com/u/danieltalbot)\
**Post date:** [September 27, 2019, 11:07pm UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632/1 "2019-09-27T23:07:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [September 27, 2019, 11:57pm UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632/2 "2019-09-27T23:57:03Z")

</div>

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:

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![danieltalbot](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/danieltalbot/32/11201_2.png) [@danieltalbot](https://forum.inductiveautomation.com/u/danieltalbot)\
**Post date:** [September 29, 2019, 4:31am UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632/3 "2019-09-29T04:31:38Z")

</div>

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:

```auto
	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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d0c60a23ca9fb1bd78fed08f8ad6abd9c3afa64c.png)

Result (there is a page for both customers):

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4ea63246bb6fe5b8bffa59c6b403e766b08d8642.png)

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/21d263115ec562ff06216eb1a898acf7b6a966aa.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [September 30, 2019, 5:00pm UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632/4 "2019-09-30T17:00:17Z")

</div>

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

> [@Report with Scripted Nested Data](http://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/2):
>
> So, I figured this out on my own with a lot of help from inspect.py, which showed me this: Java Type: \<type 'com.inductiveautomation.reporting.common.api.QueryResults'\> M: \<type 'void'\> addNestedQueryResults(\<type 'java.lang.String'\>,\<type '[Lcom.inductiveautomation.reporting.common.api.QueryResults;'\>) M: getCoreResults() returns \<type 'com.inductiveautomation.ignition.common.Dataset'\> M: \<type 'java.util.TreeMap'\> getNestedQueryResults() M: \<type 'java.lang.Object'\> get(\<type 'i…

---

<div class="post-metadata">

**Author:** ![danieltalbot](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/danieltalbot/32/11201_2.png) [@danieltalbot](https://forum.inductiveautomation.com/u/danieltalbot)\
**Post date:** [October 1, 2019, 2:18pm UTC](https://forum.inductiveautomation.com/t/not-possible-to-pass-comma-delimited-list-to-a-query-via-report-parameter/28632/5 "2019-10-01T14:18:36Z")

</div>

Thanks PGriffith, I’ll try that out.