Using the Selection Criteria Sheet

Use the Selection Criteria sheet of the Profile Properties dialog to specify selection criteria for your report when this profile is applied.

Base your selection criteria on the fields of your reporting view or the report fields that you have defined in the current design. For example, you can select all customers from a customer list who have the name Smith.

With any field type, you can apply standard comparison operators to make your selection. If a selection field is a string value, you can also use pattern matching with the starts with, ends with, and like comparison operators.

If a selection field is a database reference field, you can compare object classes with the is, is one of, and is empty comparison operators.

Comparison values must be compatible with the Character primitive type.

To specify or change the selection criteria

  1. Click the Selection Criteria tab of the Profile Properties dialog.

    The Selection Criteria sheet, shown in the following image, is then displayed. (The Selection Criteria sheet is displayed by default when you select the Selection Criteria command from the Profile menu to display the Profile Properties dialog.)

  2. Select a field to use as a selection field from the Available Fields list box at the left of the dialog. The following conditions apply.

    The selected field is then highlighted and the right arrow is enabled.

    The list of fields in the Available Fields list box is derived from the database fields that you specified in the reporting view during the configuration phase and the report fields that you defined in the current design. Available report scripts are only those that do not, directly or indirectly, reference a summary field. If you are creating profiles before starting to design your layout, only the database fields are displayed here. For details about configuring the JADE Report Writer, see Chapter 3, "Configuring the JADE Report Writer".

  3. Click the right arrow to complete the selection. Alternatively, double-click the field to select it. The selected field is then displayed in the Selection Field text box at the right of the dialog. In the previous image, the client name field has been selected.

  4. Select an operator from the Operator list box at the right of the dialog. The example in the following image shows the operator is equal to selected.

  5. In the Value text box, enter a value with which your selection field is compared. To make this entry, perform one of the following actions.

  6. Check the Not check box to negate your selection criterion formula. If you check this check box, the formula you have built as a selection criterion is negated. For example, if you formulate Clients::client name is equal to Smith and then check the Not check box, a report is produced that shows all clients except those named Smith when this profile is applied.

  7. Check the Case-sensitive check box to make the comparison case-sensitive. Case-sensitive comparisons match the capitalization (case) of string fields as well as the characters. The Case-sensitive check box is enabled only for string fields and checked by default. Uncheck this option, to remove case-sensitivity.

  8. Click the Update Criteria button to add your selection criterion formula to the Selection Items list box at the bottom of the sheet.

    You can enter any number of selection criteria, by following steps 2 through 8 of this procedure for each selection criterion that you create. For example, you could add another formula, as follows.

    Clients::email is null

    If you then select the box at the right of the Not check box for this formula, you effectively refine your selections to those clients named Smith who do not have an e-mail address.

    The following image shows the Selection Items list box as it would be displayed with these selection criteria applied.

  9. Selection criteria are first selected and then they are grouped. By default, multiple selection criteria are linked by a Boolean AND operator. This means that where you add more than one selection criterion, they must all apply. The example in the above image narrows the selection to all clients whose name is Smith and whose e-mail address is null (that is, they have no e-mail address). You can change the item grouping to use the Boolean logical OR operator, to group selection criteria in cases where one criterion or the other can apply.

  10. When you have entered several selection items, you can change their order by selecting the item to be moved and clicking on the up arrow to move items up in the list or the down arrow to move them down the list. You can move selection items within the list, to make them contiguous for grouping. (See step 8 in this instruction for details.)

    If you use grouped selection criteria, changing the order of these selection items invalidates these criteria. You can therefore move selection criteria only when there are no existing groups. To clear any existing groups, click the Clear All button.

  11. Update a selection criterion in the Selection Items list box by double-clicking the entry, if required. You can change the values that are then redisplayed in the relevant fields to meet your requirements.

  12. To delete an existing selection criterion, click once on the selection criterion in the Selection Items list box. The Remove Selected Item button, displayed as a left arrow to the upper right of the Selection Items list box, is then enabled, changing from gray to blue. Click this button to remove the selection criterion.

  13. Click another tab if you want to make changes on other sheets of the Profile Properties dialog.

  14. Click the Apply button to apply the additions or changes you have made or click the Close button to close the Profile Properties dialog. To save the recent additions or changes when you close the dialog, you must click the Apply button immediately prior to closing the dialog. To abandon any additions and changes that you have made but not yet applied, do not click the Apply button immediately prior to clicking the Close button.

Using the starts with, ends with, or like comparison operators with string values enables pattern matching.

To use pattern matching with string selection criteria

  1. Select one of the following operators from the Operator list box.

  2. Enter selection criteria in the Value text box, by using a single pattern string. The pattern string can be any sequence of characters and can include any of the following wildcard characters.

    You can use the backslash (\) character to nullify any wildcard character so that it is treated as a normal character in the pattern string. For example, \? is treated as a literal question mark character (that is, ?) and not as a wildcard character.

Selecting the is one of or is between comparison operators enables you to specify multiple comparison values.

To specify multiple comparison values

  1. Select the is one of or is between comparison operator from the Operator list box. A list box is then displayed underneath the Value text box, and two buttons are displayed to enable you to add multiple comparison values, as shown in the following image.

  2. Specify a value in the Value text box and then click the Add value to list button to add this value to the list of comparison values. Add any additional values to the list, as required.

  3. To remove a comparison value from the list, select the required value and then click the Remove value from list button. The selected value is then deleted from the list of comparison values.

  4. Click the Update Criteria button when you have finished building your list of selection values.

The following image shows a selection item where the selection criterion is all client names between Smith and Jones.

To specify selection criteria involving related data

It is important to understand how selection criteria involving a feature of a related entity are evaluated when a report is run or previewed. For example, if a report is based on a root collection of sale items and each sale item is supposed to have a related agent.

It is impossible to evaluate one of its features if for some reason the related entity does not exist. For example, design a sale items report where the related entity is the agent offering the item for sale, as shown in the following image.

In this example, either of the following selection criteria must be met.

If a sale item does not have an agent (the agent is null or empty) in the first part of the condition, it is not possible to evaluate the name of the agent. The Report Writer discards the sale item without attempting to evaluate the second part of the condition regarding the date of sale.

You can guard against sale items not having a valid agent, as shown in the following image.

In this example, you could also have reversed the order of the conditions, as shown in the following image.

To gather statistics when running a report, set the following parameters in the JADE initialization file (jade.ini).

[JadeReportWriter]
QueryDataFailureTraceOn   = true
QueryExecTraceOn          = true
QueryOptimizationTraceOn  = true
QueryPrepTraceOn          = true
QueryReadFailureOptionOff = true
QueryStatisticsTraceOn    = true

Examine the JADE message log file (jommsg.log) for the number of NumIfDefineds, which are null or empty references to entities encountered when running the report.

2004/08/19 12:28:22 02838-2794 JadeAQE: NumIfDefineds :     0