Multiselecting parameters and binding them to a dataset through Property Binding

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:

  1. Create a new report called multiSelect.rptdesign.
    Multiselecting parameters and binding them to a dataset through Property Binding
  2. Add in a data source for Classic Models.
  3. First, let's create a dataset that will be used in our multiselect parameter. Create a new dataset called setGetCustomers using the following query:
    select
CUSTOMERS.CUSTOMERNUMBER,
CUSTOMERS.CUSTOMERNAME
from
CUSTOMERS
  4. In the Data Explorer, right-click on Report Parameters and select New Parameter.
    Multiselecting parameters and binding them to a dataset through Property Binding
  5. In the Edit Parameter dialog, set the parameters as shown next. Be sure to check the Allow Multiple Values checkbox.
    Multiselecting parameters and binding them to a dataset through Property Binding
  6. Now we need to create the main query that will be used in the display of our report. Create a new dataset called 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
  7. Drag setGetCustomerOrders over to the Report Designer.
    Multiselecting parameters and binding them to a dataset through Property Binding
  8. In the newly created table, right-click and choose Insert Group | Above.
    Multiselecting parameters and binding them to a dataset through Property Binding
  9. Call the new group grpCustomer. Set the Group On drop-down list to CUSTOMERNAME. Click OK when finished.
    Multiselecting parameters and binding them to a dataset through Property Binding
  10. Right-click the table, select Insert Group | Below.
    Multiselecting parameters and binding them to a dataset through Property Binding
  11. Call the new group grpOrderNumber. Set the Group On expression to ORDERNUMBER.
    Multiselecting parameters and binding them to a dataset through Property Binding
  12. In the detail row, delete the CUSTOMERNAME and ORDERNUMBER fields.
    Multiselecting parameters and binding them to a dataset through Property Binding
  13. In the grpOrderNumber header row, move the ORDERNUMBER field to the second column.
    Multiselecting parameters and binding them to a dataset through Property Binding
  14. Move the ORDERDATE and STATUS data report items from the Detail row to the grpOrderNumber header row.
    Multiselecting parameters and binding them to a dataset through Property Binding
  15. Select the PRICEEACH column, and right-justify.
    Multiselecting parameters and binding them to a dataset through Property Binding
  16. Now that we have the basic report, we need to set the filtering. In step 6, we did not add in a question mark noting a parameter binding in the query. This is because the parameter binding in the WHERE clause is going to be added in the Property Binding section of the dataset. Double-click on setGetCustomerOrders in the Data Explorer.
    Multiselecting parameters and binding them to a dataset through Property Binding
  17. In the dataset editor, select the Property Binding section, and click on the Expression editor button next to the Query Text text area.
    Multiselecting parameters and binding them to a dataset through Property Binding
  18. Use the following as the Property Binding expression:
    //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;
  19. Run the report.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset