Dynamic SQL Where Clause example (2.3.2)

SailRCGSailRCG Junior Member
edited June 26, 2013 in DevShare Downloads
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.



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.



Parameters: 4516536658_fbd6850786_t.jpg Example Report output: 4515901751_096920c9b2_s.jpg



Live Example:

** to see the Live Example; send me a note with your email address for access **
Sign In or Register to comment.