Constant or one-off expressions allow you to hand-code anything you want, as long as it is legal SQL. This can be a constant of some kind, or maybe a complete expression.
For example, you could create a report that lists the items with a sales price less that or equal to $500.
In this example, we will select Evaluate a Constant or One-off Expression on the left side of the Where clause and Against a Database Column or Expression on the right side of the Where Clause.
To create a Where clause to evaluate a constant or one-off expression:
Click Add Clause in the main Where Clauses dialog box.
Select Evaluate a Constant or One-off Expression as the type of clause.
Enter the constant or expression to evaluate.
SQR Production Reporting Studio places the constant or expression you select on the left side of the Where clause equation. For example, to limit the report to items with a sales price less than or equal to $500, you would enter 500 as the constant.
Choose the comparison operator or SQL predicate to use in the clause.
SQR Production Reporting Studio uses this operator or SQL predicate to evaluate the constant or expression. For example, to limit the report to items with a sales price less than or equal to $500, you would choose Less than or equal to as the comparison operator.
If you choose a comparison operator, you can modify it with the ANY or ALL operators. Comparison operators are mutually exclusive – you can select ANY, ALL, or None, but not all three. If you choose ANY or ALL, the right side of the Where clause has to be a subquery, and the appropriate dialog boxes will appear.
When you evaluate an expression, you can also the SQL predicates LIKE and NOT LIKE. The LIKE predicate supports the use of wildcard characters where comparing text strings. For example, PRODUCT.DESCRIPTION LIKE %LIGHT% returns all products descriptions containing the word "light" (light bulbs, desk light).
If you are creating a list, use IN or NOT IN. If you choose IS NULL or IS NOT NULL, the Where clause does not have a right side.
Choose how to evaluate the value entered.
Depending on the operator or SQL predicate selected, you can evaluate the value against column or expression, against another constant or one-off-expression, against a value prompted at runtime, or against one or more values from a subquery.
In our example, we want to evaluate the constant value 500 against the expression to calculate the sales price. So, we would select Against a Column or Expression as the option to place on the right side of the Where clause.
Enter the column or expression against which to evaluate the constant.
In our example, we would select an expression to calculate the sale amount.
If you chose to evaluate the constant or one-off expression against another constant or one-off-expression, against a value prompted at runtime, or against one or more values from a subquery, the appropriate dialog boxes would appear.
Verify that the Where clause is correct and click Finish to return to the main Where Clauses dialog box.
In our example, the Where clause to limit the report to items with a sales price less than or equal to $500 would appear in the main Where Clauses dialog box as:
500<=(ORDLINES.QUANTITY*PRODUCTS.PRICE)