As we have discussed Expressions, now would be a good time to discuss Proeprty Binding. Property Binding is a mechanism in data sources and datasets that allows us to override values at run time through scripting. For example, let's say we need to change the value of a data source to point to a production database in a runtime environment, but want it to point to a development database while we are designing the report. Property Binding could be one way to accomplish this. We could set a report parameter, or a system environment variable, and using Property Binding, can change the JDBC URL on the fly. Property Binding will replace the value at runtime.
In the following example, we are going to look at using Property Binding to change a dataset's SQL query to use a multiselect parameter, to allow a report user to select multiple customers from a database and see their orders:
multiSelect.rptdesign
. setGetCustomers
using the following query:select CUSTOMERS.CUSTOMERNUMBER, CUSTOMERS.CUSTOMERNAME from CUSTOMERS
setGetCustomerOrders
, using the following query:select CLASSICMODELS.CUSTOMERS.CUSTOMERNAME, ORDERS.ORDERNUMBER, ORDERS.ORDERDATE, ORDERS.STATUS, ORDERDETAILS.PRODUCTCODE, ORDERDETAILS.QUANTITYORDERED, ORDERDETAILS.PRICEEACH from ORDERS, ORDERDETAILS, CUSTOMERS where CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER and CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
setGetCustomerOrders
over to the Report Designer. grpCustomer
. Set the Group On drop-down list to CUSTOMERNAME
. Click OK when finished. grpOrderNumber
. Set the Group On expression to ORDERNUMBER
. CUSTOMERNAME
and ORDERNUMBER
fields. grpOrderNumber
header row, move the ORDERNUMBER
field to the second column. ORDERDATE
and STATUS
data report items from the Detail row to the grpOrderNumber
header row. PRICEEACH
column, and right-justify. WHERE
clause is going to be added in the Property Binding section of the dataset. Double-click on setGetCustomerOrders
in the Data Explorer.//the base query as defined in the Query Section of the edit dialog. Notice the space at the end //of each line. Be sure to put a space at the end of the very last line SQL = "SELECT CUSTOMERS.CUSTOMERNAME, " +" ORDERS.ORDERNUMBER , " +" ORDERS.ORDERDATE , " +" ORDERS.STATUS , " +" ORDERDETAILS.PRODUCTCODE , " +" ORDERDETAILS.QUANTITYORDERED , " +" ORDERDETAILS.PRICEEACH " +"FROM ORDERS , " +" ORDERDETAILS, " +" CUSTOMERS " +"WHERE CUSTOMERS.CUSTOMERNUMBER = CLASSICMODELS.ORDERS.CUSTOMERNUMBER " +"AND ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER " //append the IN statement for our multi-select parameter +"AND CUSTOMERS.CUSTOMERNUMBER in ("; //iterate over the multi-select parameter, and append the values to our query for (x = 0; x < params["rprmGetCustomers"].value.length; x++) { if (x > 0) { SQL = SQL + ", "; } SQL = SQL + params["rprmGetCustomers"].value[x]; } //close out the IN statement SQL = SQL + ")"; //return the new SQL statement SQL;