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:
    • Constant. Enter a value that will serve as a constant (a value that will not change). For example, enter vehicle.

      After you click OK, the constant, vehicle, appears in the WHERE statement which reads BODYTYPE EQUAL to vehicle.

      WHERE Statement

    • Parameter. Enter the name of the variable. For example, moped as shown in the following image with the Parameter option button selected and the variable name entered as moped.

      Parameter option

      You can set parameter properties, for details see Setting Parameter Properties.

      After you click OK, the parameter, &moped, appears in the WHERE statement which reads BODYTYPE EQUAL to &moped.

      WHERE Statement

      Note: The ampersand is automatically added by WebFOCUS.

    • Fields. Select a field from the list.

      After you click OK, the field BHP appears in the Where statement which reads CAR.COMP.CAR EQUAL to CAR.SPECS.BHP.

      WHERE Statement

    • Values. Select a value for the field you initially chose (in this case, car).

      After you click OK, the value, Audi, appears in the Where statement which reads CAR.COMP.CAR EQUAL to AUDI.

      Note: In order to add a blank static value for a parameter value list, do not enclose it in quotes. Press the spacebar to create the blank value and add it. This is different than adding a blank value to a Define, Compute, or Where because in those contexts an expression is being created so quotes are needed. A value list is not an expression.

      WHERE Statement

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

Top of page
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.

Top of page
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.
Top of page
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.
Top of page
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

Top of page
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.
Top of page
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.
Top of page
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