The previous sections described how to create a report parameter and a data set parameter. The sections also described how to bind both types of parameters to replace a ? marker in the WHERE clause of a SQL query with a report parameter value. This technique works well when the WHERE clause uses a filter condition that substitutes one value for each ? marker.
You cannot, however, use this technique for a WHERE clause that uses a membership filter condition, as shown in the following example:
WHERE city IN ('San Francisco', 'San Jose', 'Los Angeles')
The following WHERE clause accepts only one value to replace the ? marker:
WHERE city IN (?)
For cases such as this, where you need to update a WHERE clause with multiple report parameter values for a single ? marker, use the data set’s property binding feature to update the entire query at run time.
How to update a query at run time
1 In the Query page of a data set, write a SQL query, such as:
SELECT CustomerName FROM customers
2 Create three report parameters, for example, City1, City2, and City3.
3 In Edit Data Set, choose Property Binding, and write the following query text to use the values from the three report parameters, as shown in Figure 16‑7:
"SELECT CustomerName from customers where customers.City IN ( " + "'" + params["City1"] + "' , '" + params["City2"] + "' , '" + params["City3"] + "' )"
Figure 16‑7 Property Binding showing the dynamic query
The query you specify in Property Binding replaces the query you specified on the Query page.
