Selection Criteria Tab in Graph Assistant of the Managed Reporting Environment

In this section:

How to:

Reference:

You can create WHERE and WHERE TOTAL statements from the Selection Criteria tab in the Graph Assistant.

Note: This section applies to using the Graph Assistant in the Managed Reporting environment.
Top of page
x
Procedure: How to Select Records Based on Values, Parameters, Fields, or a Constant
  1. Click the Selection criteria tab.
  2. Select a field from the Available Fields window and drag the field to the Screening conditions pane.

    or

    Press Alt+M, Tab to go to the Available Fields window, tab to the field and press Enter to add the chosen field.

    The field name and EQUAL to and Select values appear in the Screening conditions pane.

  3. Select WHERE or WHERE TOTAL.
  4. Select an operator from the drop-down list (for example, EQUAL to) in the right pane.
  5. Click Select values to complete the expression.
  6. Select one of the following compare type option buttons:
Top of page
x
Procedure: How to Create a Where Statement Using Date and Time Values

In the Graph Assistant:

  1. Click the Selection criteria tab.
  2. Select a field from the Available Fields window and drag the field to the Screening conditions pane.

    or

    Press Alt+M, Tab to go to the Available Fields window, tab to the field and press Enter to add the chosen field.

  3. Click EQUAL to in the right pane to display a list of operators. Click the operator of your choice.
  4. Click Select values to complete the expression.
  5. Click Field to access date and time values for that field. For example, YEAR.
  6. Click Values and click the value of your choice. For example, 1989/01/18.

    After you click OK, the value, 1989/01/18, appears in the WHERE statement which reads START_DATE EQUAL to 19890118.

    WHERE Statement

x
Importing Values From External Files for WHERE Statements

How to:

When developing a query, you can use a locally saved, external file as selection criteria to limit query results. This enables you to quickly build a query containing a large number of WHERE statement values without having to manually enter repetitive or readily available data.

x
Procedure: How to Create a Value List From an External File
  1. Navigate to the Selection criteria area.
  2. Begin creating a WHERE statement by selecting a field from the list of available fields.
  3. Select a data comparison option that can accept multiple values.

    Valid options include EQUAL to, NOT EQUAL to, IN literal list, NOT IN literal list, EXCLUDES literal list, and INCLUDES literal list.

  4. Click Select values.

    The Values dialog box opens.

  5. Click the Select from file button, as shown in the following image.

    A dialog box opens, as shown in the following image.

  6. Select either the Flat file or Excel Spreadsheet (XLS) File Format option.
  7. Click the Browse button.

    A standard file selection dialog opens.

  8. Select an external file to import from your local machine or network.

    Note: The external file must only contain text with new line delimiters.

  9. Click OK.

    The values contained in the file are displayed in the Multiple values entered list in the right pane of the dialog box.

  10. Optionally, you can remove specific values from the Multiple values entered list or move values up or down within the list.
  11. Click OK.

    You can save the query for future use and reopen the query to append new or remove existing values.

x
Setting Parameter Properties

How to:

Reference:

When you create parameters for Selection Criteria (WHERE statements) you can set the properties for the parameter. For example, you can create a dynamic or static list of values to choose from at run time.

x
Procedure: How to Set Parameter Properties

In the Graph Assistant:

  1. Click the Selection criteria tab.
  2. Create a WHERE or WHERE TOTAL expression.
  3. Select an operator. Some operators are not supported with the Variable Editor, see Supported Operators for the Variable Editor for details.
  4. Click Select values.

    The Variable Editor dialog box opens.

  5. Select the Parameter option.
  6. Click the Auto Prompt button.

    The Parameter Properties dialog box opens.

  7. Verify the Name and Description fields, which are automatically populated based on the field you are screening on. You can edit the populated values as needed.

    Note:

    • If the Description is a single word and the parameter is for a numeric field, you must add a space at the end of the description to avoid a parsing error.
    • The Description cannot contain a period (.) because a period is the delimiter to specify the beginning and end of the description value.
  8. Choose the Selection type, Dynamic or Static.

    The Dynamic option is selected by default.

    Optionally, choose the Select multiple values at run time check box to provide multiple values to the report from the auto prompt page at run time.

  9. Set the parameter values.
    • For the Dynamic selection type, the Data Source and Field values are selected by default based on the field you are screening on and can be changed as needed.

      If you want to see the fully qualified Field name, select the Display fully qualified field name check box. This setting is for display purposes only and is not preserved.

      Note: When working with a Reporting Object, you can select only fields from data sources that the Reporting Object is defined to access.

    • For the Static selection type, select the Constant or Show field values option. For Constant, enter one or more values. For Show field values, all of the values for the selected field appear in the list box. Move the values you want from the selection list to the Prompt values list.
  10. Click OK to exit the Parameter Properties dialog box.
x
Reference: Supported Operators for the Variable Editor

Operator

Enabled for the Variable editor?

Equal to

Yes

Not equal to

Yes

Greater than

Yes

Less than

Yes

Greater than or equal to

Yes

Less than or equal to

Yes

In literal list

No

Not in literal list

No

Missing

No

Not missing

No

From-to

No

Not-from-to

No

Includes literal list

Yes

Excludes literal list

Yes

Contains characters

Yes

Omits characters

Yes

Like character mask

Yes

Not like character mask

Yes

x
Combining Expressions

How to:

Use the right pane of the Selection Criteria tab to use or delete existing Where statements and to combine expressions.

x
Procedure: How to Combine Expressions
  1. Create an expression.
  2. When you create additional expressions, they are combined with the default operator AND as shown in the following image with two statements. The first is a WHERE statement and the second is an AND statement.

    WHERE AND Statement

  3. Toggle between AND, OR, WHERE, and WHERE TOTAL by clicking the AND to make your choice.
x
Procedure: How to Delete an Active Expression
  1. Select the check box next to the expression you want to delete. Click the Delete icon above the box.
  2. You are prompted to delete the expression. Click OK to delete.
x
Grouping Expressions Together With Parentheses

How to:

You can use parentheses to group expressions together to optimize the Where statement.

x
Procedure: How to Group Where Statements Together Using Parentheses

Click the grayed out parentheses to activate the parentheses and group expressions together.
x
Limiting Data With Filters in the Managed Reporting Environment

Filters enable you to quickly select predefined criteria that limit data included in a graph. Filters are selection criteria (WHERE statements) that an Administrator creates for you to apply as needed, without having to create your own selection criteria.

WebFOCUS displays filters in filter groups. Each filter group can contain multiple filters. Selecting a single filter within one group creates a report with simple filtering criteria. By selecting multiple filters within a group or combining filters from different groups, you can create complex filtering expressions.

Note: Filters are only available when you are creating a graph using a Reporting Object as a template. If you do not see filters, this means they have not been developed for this Reporting Object. Filters are not available when building a Standard Report or a Custom Report.

The following image shows a few sample filters as they appear in the Selection Criteria tab of the Graph Assistant. They are listed by Group and Name.

filters in the Selection Criteria tab

x
Reference: Selection Criteria Tab - Screen Conditions Window

The Screening conditions window shown in the following image allows you to drag and drop a field from the Available Fields window.

Screening conditions window x

WHERE/WHERE TOTAL

Use WHERE to select records based on the values of an individual field.

Use WHERE TOTAL to select records based on the aggregate value of a field, for example, the sum or average of a field's values.

Fieldname

Is the field you initially chose.

x
Operator Expression

Select an operator from the drop-down list, for example, EQUAL to.

x
Select Values

Click to open the Values dialog box to select or enter values for the WHERE or WHERE TOTAL statement you are creating. The following image shows the Values dialog box.

Values dialog box

Constant

Type a constant value. To use multiple constant values, click the plus sign (+) in the Multiple values entered list box after each constant value that you type.

Select from file (Import) button

Opens the Select file dialog box. For more information, see Importing Values From External Files for WHERE Statements.

Parameter

Type a parameter name. To use multiple parameter values, click the plus sign (+) in the Multiple values entered list box after each parameter name that you type.

Auto Prompt button

Opens the Parameter Properties dialog box. For details, see Parameter Properties Dialog Box (Dynamic Parameters) and Parameter Properties Dialog Box (Static Parameters).

Field

Select a field from the Field list box.

Values

Select a field value from the Values list box. To use multiple field values, click the plus sign (+) in the Multiple values entered list box after each field value that you select.

Multiple values entered

Displays the constants, imported values, parameters, or field values you added to the Multiple values list box. Use the available buttons to add, remove, and change the order of the selected values.

Top of page
x
Reference: Parameter Properties Dialog Box (Dynamic Parameters)

The following image shows the Parameter Properties dialog box with the Dynamic option selected.

Parameter Properties dialog box

Name

Name is the parameter name. This field is automatically filled in with information from the selected field. You can change the name if desired.

x
Description

Text that appears in the Auto prompt page as a prompt for the selection list. This field is automatically filled in with information from the selected field. You can change the description if desired.

Note:

  • If the Description is a single word and the parameter is for a numeric field, you must add a space at the end of the description to avoid a parsing error.
  • The Description cannot contain a period (.) because a period is the delimiter to specify the beginning and end of the description value.
Selection

Select the type of parameter:

  • Dynamic retrieves values from the specified data source when the request is run. This is the default selection.
  • Static contains a list of values you supply. These values do not change unless you change them.
Select multiple values at runtime

Selecting this check box enables you to provide more than one value to the report from the auto prompt page. In addition, the user is able to select all values from the values list.

Data Source

Select a data source that contains the values for the parameter. The data source must be on your APP PATH.

Field

Select the field from the data source whose values will populate the selection list.

x
Reference: Parameter Properties Dialog Box (Static Parameters)

The following image shows the Parameter Properties dialog box with the Static option selected.

Parameter Properties dialog box

Name

Is the parameter name. This field is automatically filled in with information from the selected field. You can change the name if desired.

Description

Text that appears in the Auto prompt page as a prompt for the selection list. This field is automatically filled in with information from the selected field. You can change the description if desired.

Note:

  • If the Description is a single word and the parameter is for a numeric field, you must add a space at the end of the description to avoid a parsing error.
  • The Description cannot contain a period (.) because a period is the delimiter to specify the beginning and end of the description value.
Selection

Select the type of parameter:

  • Dynamic retrieves values from the specified data source when the request is run. This is the default selection.
  • Static contains a list of values you supply. These values do not change unless you change them.
Select multiple values at runtime

Selecting this check box enables you to provide more than one value to the report from the auto prompt page. In addition, the user is able to select all values from the values list.

Constant

Enter one or more constant values.

Show field values

Shows the values in the selected field. Move the values you want in the selection list to the Prompt values list.

Prompt values

Prompt Values are the values that appear in the selection list. You can use the available buttons to add, remove, and change the order of the Prompt values.

x
Creating Temporary Fields in the Managed Reporting Environment

You can create temporary fields from the Graph Assistant. Temporary fields are created the same way in the Report and Graph Assistant.

For complete details, see the Creating Reports With Report Assistant manual.

WebFOCUS