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 three types of record selections you can create: WHERE, IF, and WHERE TOTAL.
You can also:
To add record selection criteria:
Choose one of the following:
Note: If you are typing an expression with multiple criteria directly into the Where or Where Total dialog box, you must explicitly include parentheses to control the order of evaluation.
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.
The report appears in the browser.
How to: |
You can retrieve a specified number of records on a report. It is useful to limit record retrieval when:
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.
How to: |
You can specify the maximum number of physical records read. Retrieval stops at that point.
To specify the maximum number of records to read:
Note: This feature is only available for non-FOCUS data sources.
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.
or
Click the Where/If button on the Columns toolbar.
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.
If no Prompt text is entered, the Name entered in the preceding step is used.
The Value Retrieval list provides a list of available fields in your data source. Double-click a field, select Get Data, or click OK to close the Value Retrieval field list and return to the Variable Editor. The available values are listed.
or
Click the Where/If button on the Columns toolbar.
The Multiple Value Builder dialog box opens.
Tip: Repeat this process to add other values to the list.
The Variable Editor opens.
This name will appear as the Column title if the field is added to the report.
If no Prompt text is entered, the name entered in the preceding step is used.
or
Click the Where/If button on the Columns toolbar.
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.
The Variable Editor opens.
This name appears as the Column title when the field is added to a report.
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 Variable Editor opens.
This name will appear as the Column title if the field is added to the report.
If no Prompt text is entered, the Name entered in the Name field is used.
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.
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.
The Variable Editor opens.
This name appears as the Column title if the field is added to the report.
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.
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.
The Variable Editor opens.
This name appears as the Column title if the field is added to the report.
If no Prompt text is entered, the name entered in the Name field is used.
Note: You may also click the Populate field list ellipsis button to populate the field list for that data source.
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.
The variable appears in the fields list as a Report Variable field.
The display value appears as the return field parameter.
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.
The Variable Editor opens.
This name appears as the Column title if the field is added to the report.
If no Prompt text is entered, the name entered in the Name field is used.
No Selection is added as the Display Value with _FOC_NULL as the Return Value, as shown in the image below.
When you run the report, No Selection appears in the parameter list as shown in the image below.
The following is an example of adding multiple values with the Variable Editor.
The Report Options dialog box opens at the Where tab.
The Variable Editor opens.
This name will appear as the Column title if the field is added to the report.
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.
The Variable Editor dialog box is used to:
The dialog box includes the following fields:
If you are defining a list of acceptable values, this field displays the name of the variable that you specify in the Expression Builder. 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.
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.
Choose the type of selection the user will have. The options are:
Single Select where you can select one value from a list of accepted values.
Multiselect OR where you can choose multiple OR values from a list of accepted values.
Multiselect AND where you can 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 where you can choose multiple values from a list of vertical sort values.
Multiselect ACROSS where you can choose multiple values from a list of horizontal sort values.
Simple where you can screen a value by adding suffixes to the variable value.
Range enables you to specify a range of values rather than a list of acceptable values.
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.
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:
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.
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.
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.
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:
The Synonym Editor opens.
Note: OR is the default Accept value.
Note: The only Accept Type supported in the Variable Editor is Accept list from file.
Note: 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.
The following image shows the Accept clause syntax as defined in the Master File.
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:
The Report Options dialog box opens.
The following image shows the Expression Builder populated with all selection criteria (except the Compare Value field).
The Variable Editor opens.
The following image shows the Variable Editor populated with the field values and selection criteria required to create a variable parameter.
If no Prompt text is entered, the Name in the preceding step is used.
The following image shows the Report Options dialog box populated with the Where clause created in the Expression Builder.
The following image shows the resulting source code added to the procedure in the Where clause.
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.
The report output appears as shown in the following image.
WebFOCUS |