Dynamic SQL Where Clause example (2.3.2)

SailRCG

This example uses a function to create where clauses on demand. Based on user selection (default selection) of none/all, one, or many parameters can be selected. To make interaction a bit more intuitive, this example uses a checkbox in conjunction with parameter validation to prohibit conflicting parameter selection. Finally, the data set you’ll use for the report has script in the beforeOpen of the 'report Data set' to tie the parameters to the dynamic where clause function.<br />
<br />
When selecting the “All Customers” checkbox, the where clause is empty allowing all values (Default ALL). Otherwise, when the Customer Name parameter has a value, the buildWhereClause function is called to build a where clause with or without quotes, and builds a where clause with or without a list of “in clause” values. Parameters can be stacked to give additional filtering on additional columns from your query. After the report runs, the list of parameter values chosen will be shown in the Parameters Listing dynamic text control.<br />
<br />
Parameters: Example Report output: <br />
<br />
Live Example: <br />
** to see the Live Example; send me a note with your email address for access **