The Advanced Criteria tab of the Ad-Hoc Report Definition form is used to define criteria for the SQL WHERE clause that will be used to select data for the report. For example, you may want to create a report that shows the hours each employee posted on a certain date, but you want to exclude Meal events. You would create a record on the Advanced Criteria tab that specified which event name to exclude. The Validate button in the Ad-Hoc Report Definition form will check to see that a report’s Advanced Criteria are able to select data for the report.
See Also:
Advanced Criteria Tab – Field Descriptions
Modify or Delete an Advanced Criterion
Report Name
Name of the Ad Hoc Report selected on the Ad-Hoc Report Definition form.
And Or
Indicates whether the criterion is preceded by “And” or “Or.” Note that the first criterion in this tab will be preceded by “Where,” regardless of what is selected in the And Or field.
Left and Right Parenthesis ( )
Use these fields to add parenthesis to the criterion.
Column Name
Select the column to be used for this criterion. Available options will depend on the report’s Context.
Operand Source
Indicates whether the operand value comes from a Filter, Semantic, Statement, or Static value. The available options will depend on the Column Name you selected.
Operator
Select an SQL operator for the criterion (Equal, Greater Than, Like, etc.).
Operand1
Select the operand for the criterion. Available options will depend on the Operand Source.
If your Operand Source is Filter, the Operand1 value is taken from the report filter. If the filter is not set, this criterion is not used.
If your Operand Source is Semantic, the Operand1 value is taken from a semantic (e.g., Current Date).
If your Operand Source is Statement, the Operand1 value will be a predefined statement (e.g., SUPERVISION_PERSON_IDS). The statement will be used with an IN or NOT IN Operator (statements are essentially sub-queries). The available statements are based on the report context and the Column Name you selected. You can define parameters for the statement using the Statement Parameters form.
If your Operand Source is Static, use the Operand1 field to enter the operand value.
Empty Boolean Value
Indicates whether the criterion should be evaluated as true or false when the value is empty. If this box is checked, the criterion will be evaluated as true when the value is empty. For example, a report includes a filter for Employee and includes the following Advanced Criteria:
And Or = And
Column Name = Employee (person_num)
Operand Source = Filter
Operator = Like
Operand1 = person_search
Empty Boolean Value = Checked
When you run the report without entering a value in the Employee filter, you get 200 records. However, if Empty Boolean Value is not checked, you get 0 records. Because the Employee filter was empty, the criterion was invalid.
This setting is needed when a criterion with an Operator Source of Filter needs to be excluded from the criterion when it is empty, but you do not want to affect the criteria logic by removing that criterion.
In general, if the criterion is preceded by And, then Empty Boolean Value should be checked. If the criterion is preceded by Or, then Empty Boolean Value should not be checked.
Sequence Num
Determines the order in which the criteria will be run. You can use the Move Up and Move Down buttons to change the sequence.
Enabled
Check this box to include the record in your Advanced Criteria. If you are not ready to execute the criteria and want to further test it, do not check this box.
Click Main Menu > Configuration > Form Configuration > Ad-Hoc Report Def and select the Ad Hoc Report Definition.
On the Advanced Criteria tab, click Add.
Set up the first criterion; refer to the field descriptions above for more information.
Click Save and Add to save the first criterion and add another one. Or click Save to save this criterion and return to the Advanced Criteria tab.
Click the Validate button on the Ad-Hoc Report Definition form to make sure the report’s Advanced Criteria are able to select data for the report.
You can change all the properties of a criterion except the Report Name, Column Name, and Operand Source. To do so, select the criterion on the Advanced Criteria tab of the Ad-Hoc Report Definition form and click Modify. Change the settings and click Save.
To delete a criterion, select it on the Advanced Criteria tab of the Ad-Hoc Report Definition form and click Delete.
If a criterion’s Operand Source is Statement, you can use the Statement Parameters button to add, modify, and delete parameters for the statement.
For example, a supervisor runs a Person report with the following criterion enabled:
The report will only include persons for which the supervisor has authority (e.g., members of an employee group for which the supervisor is defined as a manager).
If the following Statement Parameter is added, then the report will only include persons who are members of the specified facility.
To use the Statement Parameters form:
On the Advanced Criteria tab of the Ad-Hoc Report Definition form, select a criterion with an Operand Source of Statement.
Click the Statement Parameters button.
If there are any existing parameters defined for the statement, you can change their Parameter Source and Parameter Value. To do so, select the parameter and click Modify.
To delete a parameter, select it and click Delete.
To define a new parameter, click Add.
The Statement Name will be set to SUPERVISION_PERSON_IDS. Currently the SUPERVISION_PERSON_IDS statement is the only available option.
The Statement Name determines which parameters can be set. Each Statement Name has a predefined list of parameters.
Select the Parameter Name. The available options depend on the Statement Name you selected.
Select the Parameter Source. If you select Filter, the parameter will come from a filter value that is entered when the report is run. If you select Static, the parameter will come from the Parameter Value field (see below).
In the Parameter Value field, select the Filter or enter the Static value for the parameter.
Click Save when you are done.
The criteria you define on the Advanced Criteria tab are placed in the where clause in the order they appear on the form. You can use the Move Up and Move Down buttons on the Advanced Criteria tab to change the order of the criteria.
When you add a new criterion, you can include one or more left and right parenthesis. You can also use the Add Parenthesis and Delete Parenthesis buttons on the Advanced Criteria tab to add or delete parenthesis from your criteria.
