WebFOCUS Online Help > Managed Reporting End User > Creating a Report With Report Assistant > Selecting Records for a Report

Selecting Records for a Report

In this section:

When generating a report and specifying which fields to display, you may not want to show every instance of a field. By including selection criteria, you can display only those field values that meet your needs. In effect, you can select a subset of the data, which you can easily redefine each time you issue the report request.

When developing a report request, you can define criteria to select records based on:

The Selection criteria tab enables you to create Where or Where Total statements for record selection.


Top of page

Creating a Where or Where Total Statement

Where and Where Total statements enable you to display only those records that pass your selection criteria. These statements select the data source records to be included in a report. The data is evaluated according to the selection criteria before it is retrieved from the data source. Use the Selection criteria tab to create a new Where or Where Total statement.

Note: In the Report Assistant, when you create a Where statement in the Selection criteria tab for a range of dates using FROM - TO, you will not be alerted if the TO field is chronologically before the FROM field. For example, if you enter something like "from 12/31/05 to 1/1/05", there will be no records in the resulting report.


Top of page

Procedure: How to Select Records Based on Values, Parameters, Fields, or a Constant
  1. In the Report Assistant, 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 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

Procedure: How to Create a Where Statement Using Date and Time Values

In the Report 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 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, EYEAR.
  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.


Top of page

Importing Values From External Files for WHERE Statements

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.


Top of page

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 opens.

  5. Click the import 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.

  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

Setting Parameter Properties

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.


Top of page

Procedure: How to Set Parameter Properties
  1. In the Report Assistant, 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 selected for the WHERE clause. 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 runtime 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.

Top of page

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


Top of page

Combining Expressions

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


Top of page

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 a WHERE and the second an AND statement.

  3. Use the drop-down list to select from AND, OR, WHERE, and WHERE TOTAL.

Top of page

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

Grouping Expressions Together With Parentheses

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


Top of page

Procedure: How to Group Where Statements Together Using Parentheses

Click the grayed out parentheses to activate the parentheses and group expressions together.


WebFOCUS