Selecting Data

In this section:

How to:

You can produce a report based on selected data, or a subset of the records in a data source. You do this by specifying certain selection criteria. Report Painter includes only the records that meet those conditions in the report.

For example, you might want a report that lists only those employees hired since the beginning of 2001. If your data source contains the field START_DATE, you would specify record selection criteria in which the START_DATE is greater than 12/31/00.

There are four types of record selections you can create: WHERE, IF, WHERE TOTAL, and Retrieval Limits.

You can also:


Top of page

x
Procedure: How to Add Record Selection Criteria

To add record selection criteria:

  1. Depending on the type of field you want to select records from (Detail, Sum), do one of the following:
    • If you want data evaluated before it is retrieved, click Where from the Where/If drop-down menu to open the Expression Builder.
    • If you want data evaluated based on a Boolean expression, click If from the Where/If drop-down menu to open the Expression Builder.
    • If you want data selected after all the data has been retrieved and processed, click Where Total from the Where/If drop-down menu to open the Expression Builder.

    The Expression Builder opens. The Expression Builder assists you in constructing selection criteria. For details about the Expression Builder, see Using the Basic Expression Builder.

    Note: If you are typing an expression with multiple criteria directly into the Advanced section of the Expression Builder, you must explicitly include parentheses to control the order of evaluation.

  2. Click OK to confirm your expression. Report Painter checks your syntax before exiting. If you have made any errors, you will be unable to exit until you fix them.


Example: Creating Record Selection Criteria

Suppose that you want to see total sales for specific products. The following example shows how to create a report of the Gotham Grinds Latte and Espresso sales for each store within each region.

  1. Open the GGSALES data source in Report Painter.
  2. Identify and format your report columns.
    1. Identify your sort columns. Click By on the Report Painter toolbar and double-click or drag REGION, STCD, and PRODUCT from the Fields tab of the Object Inspector.
    2. Identify your summary column. Click Sum on the Report Painter toolbar and double-click or drag DOLLARS from the Fields tab of the Object Inspector.
    3. Format the Dollar Sales column. Right-click Dollar Sales in the Report Painter window and click Format. Click Decimal in the Format Types box. Click Floating dollar -- M from the Edit Options list. Click OK.
  3. Apply selection criteria:
    1. Click Where from the Where/If drop-down menu on the Columns toolbar to open the Expression Builder.
    2. Click PRODUCT from the Fields list.
    3. Click equals from the Logical Relations list.
    4. Click Value from the Compare Type list to retrieve a list of data available for the PRODUCT field.
    5. Double-click the Compare Value section to bring up the Multiple Value Builder dialog box. In the Data Source section click Select Field and click Latte and Espresso. Click OK to close the Multiple Value Builder dialog box.
    6. Click OK to return to the Report Painter window.
  4. Run the report by clicking the Run button on the toolbar.

    The report appears in the browser.


Top of page

x
Retrieving a Certain Number of Records

How to:

You can retrieve a specified number of records on a report. It is useful to limit record retrieval when:



x
Procedure: How to Retrieve a Certain Number of Records
  1. Click Retrieval Limit from the Where/If drop-down menu on the Columns toolbar to open the Retrieval Limits dialog box.
  2. Enter the number of records to retrieve in the Record Limit box.
  3. Click OK.

When you run the report, only that number of records appears. You can also restrict record retrieval. For more information, see Reading a Certain Number of Records.


Top of page

x
Reading a Certain Number of Records

How to:

You can specify the maximum number of physical records read. Retrieval stops at that point.



x
Procedure: How to Read a Certain Number of Records

To specify the maximum number of records to read:

  1. Click Retrieval Limit from the Where/If drop-down menu on the Columns toolbar to open the Retrieval Limits dialog box.
  2. Enter the number of records to retrieve in the Read Limit box.
  3. Click OK.

    Note: This feature is only available for non-FOCUS data sources.


Top of page

x
Creating a List of Acceptable Values

How to:

Reference:

You can create a list of acceptable values for one or more selection criteria (Where statements) and use each set of values to run a parameterized report. The user is prompted for a value when the report is run.

To ensure that a supplied value is valid and being used properly in a procedure, you can test it for presence, type, and length by screening a value. You screen a value by adding suffixes to the variable value.

If you call Report Painter from the HTML Composer and create a procedure that defines acceptable values for one or more fields, the values populate drop-down lists from which a user can choose. For information about the HTML Composer, see the Designing a User Interface for a Web Application With the HTML Composer manual.

Note: To avoid conflicts, do not name variables beginning with Date, IBI, or WF, as variables beginning with these values are reserved for Information Builder use.



x
Procedure: How to Create a List of Acceptable Values for a Parameter
  1. Click Where from the Where/If drop-down menu on the Columns toolbar to open the Retrieval Limits dialog box.
  2. Double-click a field name from the list on the left side of the window to add it to the Column to filter column.

    If you want to change the field name in the Column to filter column, click the down arrow and select a different field name from the list.

  3. Click the down arrow in the Logical Relations column to select a relation from the drop-down list.
  4. Click the down arrow in the Compare Type column and click Parameter from the drop-down list.
  5. Double-click the Compare Value column. The Variable Editor opens.
  6. In the Name input box, type a name for the variable. This name will appear as the Column title if the field is added to the report.
  7. Optionally, enter text in the Prompt input box to prompt a user to supply a value for the variable.

    If no Prompt text is entered, the Name entered in the preceding step is used.

  8. From the Variable Type drop-down list, click Multiselect OR to add values for fields you want to include in your list of valid values.
  9. In the Data source area, click Values for field.
  10. Click the Select a field ellipsis button to get values for a field. The Value Retrieval dialog box opens.

    The Value Retrieval list provides a list of available fields in your data source. Double-click a field, click Get Data, or click OK to close the Value Retrieval field list and return to the Variable Editor. The available values are listed.

  11. Double-click values to add them to the Accept List.
  12. Click OK to close the Variable Editor.
  13. Click OK to close the Expression Builder.


x
Procedure: How to Edit Multiple Values
  1. Click Where from the Where/If drop-down menu on the Columns toolbar to open the Retrieval Limits dialog box.
  2. Double-click a field name from the list on the left side of the window to add it to the Column to filter column.
  3. Click the down arrow in the Logical Relations column to select a relation from the drop-down list.
  4. Click the down arrow in the Compare Type column and click Value from the drop-down list.
  5. Double-click the Compare Value column.

    The Multiple Value Builder dialog box opens.

  6. Click the Select a field ellipsis button in the Data source area to select a field from the Value Retrieval list.
  7. The Value Retrieval list provides a list of available fields in your data source. Double-click a field, click Get Data, or click OK to close the Value Retrieval field list and return to the Multiple Value Builder dialog box. The available values are listed.
  8. Double-click values to add them to the Values List.

    Tip: Repeat this process to add other values to the list.

  9. Click OK to close the Multiple Value Builder dialog box and return to the Expression Builder.
  10. Click OK to close the Expression Builder.


x
Procedure: How to Specify a Range of Values for Variables in Your Report
  1. Click inside the Fields tab on the Object Inspector.
  2. Right-click the Variables folder and click New Report Variable Field from the menu that opens.

    The Variable Editor opens.

  3. In the Name input box, type a name for the variable.

    This name will appear as the Column title if the field is added to the report.

  4. Optionally, enter text in the Prompt input box to prompt a user to supply a value for the variable.

    If no Prompt text is entered, the name entered in the preceding step is used.

  5. From the Variable Type drop-down list, click Range.
  6. A list box appears, providing a list of available fields in your data source. Select a field to populate the From and To selection lists.
  7. Click a value from the From selection list to add it as the From range value.
  8. Click a value from the To selection list to add it as the To range value.
  9. Click OK to close the Variable Editor. The variable appears in Report Painter as a Report Variable field.


x
Procedure: How to Compare the Selected Field to Another Field
  1. Click Where from the Where/If drop-down menu on the Columns toolbar to open the Retrieval Limits dialog box.
  2. Double-click a field name from the list on the left side of the window to add it to the Column to filter column.

    If you want to change the field name in the Column to filter column, click the down arrow and select a different field name from the list.

  3. Click the down arrow in the Logical Relations column to select a relation from the drop-down list.
  4. Click the down arrow in the Compare Type column and click Field from the drop-down list.
  5. Double-click in the Compare Value column to open the Single Value Builder dialog box.
  6. Double-click a field from the data source to add it to the Values List. The field selected is compared to the selected field in the report.
  7. Click OK to close the Single Value Builder dialog box and return to the Expression Builder.
  8. Click OK to close the Expression Builder.


x
Procedure: How to Create a Variable Field in Your Report
  1. Click inside the Fields tab on the Object Inspector.
  2. Right-click the Variables folder and click New Report Variable Field from the menu that opens.

    The Variable Editor opens.

  3. In the Name input box, type a name for the variable.

    This name appears as the Column title when the field is added to a report.

  4. Optionally, provide text for the message that prompts a user to supply a value for the variable in the Prompt input box.
  5. From the Variable Type drop-down list, ensure that Single Select is selected. This is the default option.
  6. In the Data Context area, click Values for field.
  7. Click the Select a field ellipsis button to get values for a field. The Value Retrieval dialog box opens.

    The Value Retrieval dialog box provides a list of available fields in your data source. Double-click a field, or select a field and click Get Data to close the Value Retrieval dialog box and return to the Variable Editor. The available values for the field are listed.

  8. Double-click values to add them to the Accept List.
  9. Click OK to close the Variable Editor. The variable appears in the fields list as a Report Variable field.


x
Procedure: How to Add Multiple Values With the Variable Editor
  1. Click inside the Fields tab on the Object Inspector.
  2. Right-click the Variables folder and click New Report Variable Field from the menu that opens.

    The Variable Editor opens.

  3. In the Name input box, type a name for the variable.

    This name will appear as the Column title if the field is added to the report.

  4. Optionally, enter text in the Prompt input box to prompt a user to supply a value for the variable.

    If no Prompt text is entered, the Name entered in the Name field is used.

  5. From the Variable Type drop-down list, click Multiselect OR.
  6. In the Data Context area, select one of the following:
    • Fields from database. Click this to retrieve fields from a database.
    • Values for field. Click the Select a field ellipsis button to get values for a field. The Value Retrieval dialog box opens.

      The Value Retrieval dialog box provides a list of available fields in your data source. Double-click a field, or select a field and click Get Data to close the Value Retrieval dialog box and return to the Variable Editor. The available values for the field are listed.

    The Data Context list displays available values.

  7. In the Data Context list, double-click values to add to the Accept List table.

    To customize the display of values, double-click a value in the Return Value column and type the name as you want it to appear in the output.

  8. Click OK. The field appears in the Report Variables folder of the Object Inspector.


x
Procedure: How to Screen a Simple Variable With Prefix Operators
  1. In Report Painter, right-click inside the Object Inspector and click New Report Variable Field from the context menu.

    The Variable Editor opens.

  2. Click Simple from the Variable Type drop-down list.
  3. Type a name for the variable in the Name input field.

    This name appears as the Column title if the field is added to the report.

  4. Use the Operator drop-down list to select a suffix to add to the variable value. None is the default option.
  5. Click OK to close the Variable Editor.

    The variable appears in the fields list as a Report Variable field.

For more information about screening a variable value, see the Developing Reporting Applications manual.



x
Procedure: How to Set the Display Value for Dynamic Lists With the Variable Editor

When using a Dynamic Accept list, you may select a value to be displayed as a parameter. A dynamic list retrieves values from a specified data source when the request is run. The display value can be different from the return value by using the Values for Displayed Field option in the Variable Editor.

Note: Setting the display value for dynamic lists is only applicable if you are using a Data source that contains both a code and description field.

  1. In Report Painter, right-click inside the Object Inspector and click New Report Variable Field from the context menu.

    The Variable Editor opens.

  2. Type a name for the variable in the Name input field.

    This name appears as the Column title if the field is added to the report.

  3. Optionally, enter text in the Prompt input box to prompt a user to supply a value for the variable.

    If no Prompt text is entered, the name entered in the Name field is used.

  4. Click Single Select or Multiselect OR from the Variable Type drop-down list, depending on whether you are supplying single or multiple values for your parameter.
  5. Select Dynamic list from the Accept List section of the Variable Editor.
  6. From the Source File list, double-click the data source to be used for the report.

    Note: You may also click the Populate field list ellipsis button to populate the field list for that data source.

  7. In the Values for Return Fields list, select a return field from the selected data source.
  8. In the Values for Displayed Fields list, select a value field to be displayed as the return field.

    The following image is an example of the Variable Editor populated with the field values and selection criteria required to create a dynamic list parameter with a display value.

  9. Click OK to close the Variable Editor.

    The variable appears in the fields list as a Report Variable field.

  10. Save and run the report.

    The display value appears as the return field parameter.



x
Procedure: How to Add No Selection to a Static List of Parameter Values

No Selection sends _FOC_NULL to the list of available parameters at run time and is only available when using a Static Accept list. When selected, _FOC_NULL removes the parameter from the procedure and the report will run without the Where clause.

  1. In Report Painter, right-click inside the Object Inspector and click New Report Variable Field from the context menu.

    The Variable Editor opens.

  2. Type a name for the variable in the Name input field.

    This name appears as the Column title if the field is added to the report.

  3. Optionally, enter text in the Prompt input box to prompt a user to supply a value for the variable.

    If no Prompt text is entered, the name entered in the Name field is used.

  4. Click Single Select or Multiselect OR from the Variable Type drop-down list, depending on whether you are supplying single or multiple values for your parameter.
  5. Select Static list from the Accept List section of the Variable Editor.
  6. From the Data Context area, add the Fields from database or Values for fields, and select the values to be used for the report.
  7. Select the Add No Selection option from the Add New Item drop-down list, as shown in the following image.

    Add No Selection Option

    No Selection is added as the Display Value with _FOC_NULL as the Return Value, as shown in the image below.

    Edit No Selection Display

  8. Optionally, to customize the default Display Value, double-click No Selection in the Display Value column and type the name as you want it to appear in the output.
  9. Click OK to close the Variable Editor.

When you run the report, No Selection appears in the parameter list, as shown in the image below.



x
Procedure: How to Edit Variables With the Variable Editor
  1. Open the Variables folder in the Object Inspector.
  2. Open the Report Variables folder.
  3. Right-click the variable and click Edit Report Variable Field.


Example: Adding Multiple Values With the Variable Editor

The following is an example of adding multiple values with the Variable Editor.

  1. Create a new procedure, using CENTORD as the data source.
  2. In the Report Painter main window, add the following fields to your report:
    • ORDER_DATE
    • PROD_NUM
    • PRODNAME
    • PRICE
    • QUANTITY
  3. Select the ORDER_DATE field, and click By.
  4. Click the Where button from the Where/If drop-down menu.

    The Expression Builder opens.

  5. Double-click PROD_NUM in the list on the left side of the window to add it to the Column to filter column.
  6. Click the down arrow in the Logical Relations column, and click equals from the drop-down list.
  7. Click the down arrow in the Compare Type column and click Parameter from the drop-down list.
  8. Double-click the Compare Value column.

    The Variable Editor opens.

  9. In the Name input box, enter Product Number.

    This name will appear as the Column title if the field is added to the report.

  10. Enter Product Number in the Prompt input box.
  11. From the Variable Type drop-down list, click Multiselect OR to add values for fields you want to include in your list of valid values.
  12. In the Data Context area, click Values for field.
  13. Click the Select a field ellipsis button to get values for the PROD_NUM field. The Value Retrieval dialog box opens.

    The Value Retrieval dialog box provides a list of available fields in your data source. Double-click the PROD_NUM field to close the Value Retrieval dialog box and return to the Variable Editor. The available values for the PROD_NUM field are listed.

  14. Double-click 1020 in the list box to add it to the Accept List table. Do the same for the values 1022 and 1024.
  15. Click OK to close the Variable Editor.
  16. Click OK to close the Expression Builder.
  17. The field appears in the Report Variables folder of the Object Inspector.


x
Reference: Variable Editor Dialog Box

The Variable Editor dialog box is shown in the following image.

The Variable Editor dialog box is used to:

The dialog box includes the following fields:

Name

If you are defining a list of acceptable values, this field displays the name of the variable that you specify in the Expression Builder. For more information, see How to Create a List of Acceptable Values for a Parameter.

If you are creating a Variable field, enter its name in this box. See How to Create a Variable Field in Your Report.

Prompt

An optional text field that is used to prompt a user to supply a value for the variable or to select a value from those you identify as acceptable. If no text is entered in this Prompt field, then the value from the Name field is used by default.

Variable Type

Choose the type of selection the user will have. The options are:

Single Select. Select one value from a list of accepted values.

Multiselect OR. Choose multiple OR values from a list of accepted values.

Multiselect AND. Choose multiple AND values from a list of accepted values.

Note: Multiselect AND is not applicable for selecting data values, and should only be used when selecting field name values for a variable. Multiselect AND variable type with field name values is useful for application development using the HTML Composer. For more information, see Creating Parameter Values in the Designing a User Interface for a Web Application With the HTML Composer manual.

Multiselect BY. Choose multiple values from a list of vertical sort values.

Multiselect ACROSS. Choose multiple values from a list of horizontal sort values.

Simple. Screen a value by adding suffixes to the variable value.

Range. Specify a range of values rather than a list of acceptable values.

Validate Input

When checked, at run-time, this validates whether the data entry is consistent with the definition of a parameter.

Accept List

Displays the values of the selected field.

Populate the variable with:

Static list. Consists of a list of values you supply. These values do not change unless you change them.

Dynamic list. Retrieves values from a specified data source when the request is run.

Accept list from file. Retrieves values from an Accept list defined in the Master File for the selected field.

Data Context

These options are only available when you select the Static list option in the Accept List area. You can choose from the following options:

Fields from database displays a selection menu which enables you to select the field names you want to include in an Accept list for the variable field.

Values for field enables you to use the Value Retrieval dialog box to select the values for the selected field that you want to include in an Accept list for the variable field.

Display Value/Return Value shows the selected fields that were added to the Static Accept list from the Data Context area. You may Redo/Undo the last field added, move items up or down the list, and delete selected items. In addition:

  • To customize the name of the values in the Static Accept list, double-click a Display value or Return Value and type the name as you want it to appear in the output.
  • To manually enter a Display value or Return value, click the Add New Item button (or press Ctrl + Enter) to add a new Display and Return value.
  • To add No Selection as a Display Value, click Add No Selection from the Add New Item drop-down list. For more information, see How to Add No Selection to a Static List of Parameter Values.
Source File

This section is only available when you select the Dynamic list or Accept list from file options in the Accept List area. This enables you to select a Master File from a list.

Values for Return Fields

This section is only available when you select the Dynamic list or the Accept list from file options in the Accept List area. This enables you to select a return field from the selected Master File.

Values for Displayed Fields

This section is only available when you select Dynamic or Accept list. This enables you to set the display value as something different from the return value. For more information, see How to Set the Display Value for Dynamic Lists With the Variable Editor.


Top of page

x
Creating Variable Parameters With Accept Values in the Variable Editor

How to:

When you create a variable parameter that references an Accept clause in a procedure, only the list of values defined in the Master File are valid. All other values, including values that exist in the database, are rejected.

You can use the Variable Editor to create a variable parameter in a procedure, utilizing an Accept clause defined in a Master File, by performing the following:



x
Procedure: How to Create an Accept Clause With the Synonym Editor
  1. From the Projects area in Developer Studio, double-click a synonym from the Master Files folder, or right-click the synonym and click Edit in Synonym Editor.

    The Synonym Editor opens.

  2. Select the field name in the left pane and use the Accept value drop-down list in the right pane to select OR as the validation criteria. OR enables you to specify an acceptable value.

    OR is the default Accept value, as shown in the following image.

    The only Accept Type supported in the Variable Editor is Accept list from file.

  3. In the Value field, type a valid accept field value.

    You may type multiple OR value fields, as shown in this example:

    Or

    Click the Value ellipsis button to open the Accept Value(s) dialog box where you may add and delete values, as shown in the image below.

  4. Click Save from the File menu to save the accept value in the synonym.
  5. Click Close from the File menu to close the Synonym Editor and return to the Projects area of Developer Studio.
  6. To view the resulting Accept clause syntax, right-click the Master File and click Edit in Text Editor.

    The following image shows the Accept clause syntax as defined in the Master File.



x
Procedure: How to Create a Variable Parameter in a Procedure With the Variable Editor

Open a procedure with Report Painter and add several fields, including the field you used to create the Accept clause in the previous steps. Then, add a Where clause to the procedure and add a variable parameter with the variable editor by performing the following steps:

  1. In Report Painter, click Where from the Where/If drop-down menu.

    The Expression Builder opens.

    The following image shows the Expression Builder populated with all selection criteria (except the Compare Value field).

  2. Double-click the field name (used to create the Accept clause) in the file directory list in the left pane to add it to the Column to filter column.
    1. Click the down arrow in the Logical Relation column and select a relational operator from the drop-down list.
    2. Click the down arrow in the Compare Type column and click Parameter from the drop-down list.
    3. Double-click the Compare Value column.

      The Variable Editor opens.

  3. In the Variable Editor, click the Accept list from file option and perform the following steps to create a variable parameter.

    The following image shows the Variable Editor populated with the field values and selection criteria required to create a variable parameter.

    1. In the Variable Type drop-down list, click Single Select.
    2. In the Name input box, the field name (used to create the Accept clause) appears as the name for the variable.
    3. In the (optional) Prompt input box, type the text you want used to prompt a user to select a value.

      If no Prompt text is entered, the Name in the preceding step is used.

    4. From the Source File list, the Master File (used to create the Accept clause) for the report is selected.
    5. In the Values for Return Fields list, the field name in red (used to create the Accept clause) is selected.
    6. Click OK to save the variable parameter, close the Variable Editor, and return to the Expression Builder window.
  4. Click OK to close the Expression Builder.

    The following image shows the Report Options dialog box populated with the Where clause created in the Expression Builder.

  5. To verify that the source code is correct and the resulting procedure will be executed successfully, perform the following:
    1. Click the Source tab below the Object Inspector.

      The following image shows the resulting source code added to the procedure in the Where clause.

    2. Click the Run icon.

      The following image shows the new window that opens prompting you to select a value from the list of valid Accept values for the variable you created.

    3. Select a value from the drop-down list and run the WebFOCUS query.

      The report output appears, as shown in the following image.


WebFOCUS