To add an expression to a query:
Enter a name for the expression in the Name field.
You use expression names in reports in the same way you use column names. Assign a name to help you identify the expression later. Valid characters are A-Z, 0-9, and underscore. You cannot use spaces.
Enter the expression in the Expression field. To enter an expression:
To delete an expression from the Expression field, click Clear. (This also deletes the name of the expression from the Name field.)
The types of functions that you can use in expressions appear under Operations. For example, aggregate functions, numeric functions, and arithmetic operators (see Function Examples).
To view available options for a function type, click the plus sign (+). The functions that appear depend on the database selected on the Select Production Reporting Database page of the Create Data Connection wizard. (See step 5 under Creating a New Data Source Connection.) As a result, if you change the database, the available functions may change.
Some functions have the same name as Production Reporting commands. When you use a function in an expression, do not confuse the function with the Production Reporting command. Functions used to create expressions in queries are coded to the database format (not Production Reporting).
For example, a datediff function used inside an expression takes the format (parameters, return type) of the database queried. This function used in a variable outside of the query, however, takes the format of the Production Reporting command.
Insert the expression into the query.
Click Validate to validate the expression before inserting it in the query.
If the expression is valid, the question mark that appears on Validate changes to a check mark. If the expression is not valid, the question mark changes to an “X." After you click Validate, click Insert Only to insert the expression into your query.
Click Insert and Validate to validate the expression and insert it into the query.
If the expression is valid, the question mark that appears on Validate changes to a check mark. If the expression is not valid, the question mark changes to an “X."
Click Insert Only to insert the expression into your query without validating it first.
The Expression Builder remains open for additional entries. Before adding additional expressions, click Clear to clear the input fields in the Expression Builder.
Click Close to close the Expression Builder.
SQR Production Reporting Studio enables the Show Values button when you click a text or numeric column in the left pane. Click Show Values to display the values contained in the selected column. You can use these values in your expression if desired.
Expressions that do not reference a column from the select list may not be used in the Group By statement. SQR Production Reporting Studio does not verify the presence of a column reference in an expression, and will attempt to use the expression in the Group By statement if "Generate Group By Statement Automatically" is selected in the Group By dialog box. If you use an expression that does not reference an existing column, make sure that "Generate Group By Statement Automatically" is not checked, and add the desired Group By fields.