A calculated value is a temporary field that is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed. Calculated values are available only for the specified report request.
Use the Computes tab to create a calculated value. You can access the Computes tab only when you are creating reports in Report Painter.
In Report Painter:
The Report Options dialog box opens at the Computes tab.
The Format dialog box opens.
The default length is 20. It appears in the Length input area.
The Format dialog box closes.
For more information on field formats, see Format Dialog Box.
The Format dialog box opens.
The default length appears in the Length input area. The Decimal input area shows the number of decimal places for Floating Point, Decimal, and Packed.
The Format dialog box closes.
For more information on field formats, see Format Dialog Box.
The Format dialog box enables you to define the format of the output to be generated by a calculation.
Note: The Format button is available in the Compute, Define, and RECAP dialog boxes to define field formats for temporary fields and subtotal calculations. Click the Format button to open the Format dialog box.
For more information about the RECAP dialog box in the Financial Report Painter, see Creating Reports With Financial Report Painter in the Creating Financial Reports manual.
Tip: You can also access the Format dialog box from the context or Properties menu for a selected field in the Report Painter window to change a field format.
The Format dialog box has the following fields and options:
Specifies the format of the current field. The field type can be alphanumeric, numeric, or date/time.
Opens the Date and Time Formats dialog box, where you can assign date and time formats, and apply date and time display options.
For more information, see Assigning Date/Time Formats.
Specifies the length, in characters, of a field. Enter a number in the Length box, or click the arrow buttons to specify a number.
Field Format |
Length |
---|---|
Alphanumeric |
1-4096 (default, 20) |
Floating Point |
1-9 (default, 7.2) |
Integer |
1-11 (default, 5) |
VarChar |
1-4096 (default, 20) |
Decimal |
20.18 (default, 12.2) |
Packed |
33.31 (default, 12.2) |
Dynamic |
Select a Format Field. |
Note: For numeric fields, include the decimal place in the length.
Specifies the number of decimal places to the right of the decimal point in a Decimal, Packed Decimal, or Floating Point field.
Check this option to display a minus sign to the right of negative numeric data.
Adds display options to numeric field formats to control how the field will appear on reports.
You can assign the Date/Time format to a field from the Define, Compute, and Recap tools with the Date and Time Formats dialog box. You may also apply the Date/Time (DT) expression in a Where, Compute, and Define tool as a value or a function. This section describes how to assign the Date/Time format, value, and function with these graphical tools.
Tip: You can also insert the current date into an object area in the Report Painter window.
The following image is an example of the Report Painter with sample data for a Date and Time field.
Access the Date and Time Formats dialog box by clicking the Date/Time button in the Format dialog box, available from the Define, Compute, and Recap tools.
Note: The Date and Time Formats dialog box enforces valid Date-only and DateTime format combinations, based on the input entered and the field selected. Some options may not be available for your selections.
Displays the type of field being created or edited.
Note: When creating a new field, both formats are enabled, allowing you to create either type of field format. When editing an existing field, only one field format is enabled, enforcing a visual indication of what type of field is being edited and indicating the applicable options.
Displays the current format string in FOCUS syntax, updated as changes are made in the dialog.
Displays a sample date and/or time value based on the current date/time and current format string.
Lists all the valid date combinations for Year, Month, Day, and so on. The options vary depending on the field format selected.
Note: The default date format is None, from which you can select a Day name option (for a Date field format), or a Time format option (for a DateTime field format).
Opens the Century/Base dialog box, which enables you to set Century and Base options added for Y2K.
Displays the month name instead of a number for the field. The options vary depending on the field format selected.
Displays the day name with the date using translate options. The options vary depending on the field format and the Month Name selected.
Note: This option appears as Day when the DateTime field format is selected. The options are Default (2-digit) or Zero suppression (zero-suppressed) number for a Day.
When the Date field format is selected, the Prefix option controls if the name appears before (default) or after the other date options.
Note: Prefix is only available when a Day Name is selected.
Displays the separator used in numeric dates.
Note: The comma separator option is available for DateTime field formats, in which a month or day is followed by a year, and a Month Name is translated to a short or full name.
Note: The T separator option is available for DateTime field formats (which is the U separator in the Master File), and enables recognition and output of the ISO standard format, where T is the delimiter between date and time.
For more information about describing a Date-Time field in a Master File, see the Describing Data With WebFOCUS Language manual.
Lists all the valid time combinations for hour, minute, seconds, and so on, for a DateTime field format. The options vary depending on whether the DateTime format contains date options versus time-only options.
For more information about time formats, see the Using Functions manual.
Applies a zero-suppressed number, when the time format is hour or minute. Zero suppressed is indicated by default, for example, when AM/PM options are selected.
Note: Zero Suppression is only enabled for Time-only formats.
Represent the 24-hour time format.
Represents the 12-hour time format.
Options vary depending on the time hour selected.
Note: Z suffix enables recognition and output of the ISO standard formats by using Z at the end of universal times, and acceptance of inputs with time zone information.
or
Choose Format from the Properties menu.
The Format dialog box opens.
The Date and Time Formats dialog box opens.
Note: The Date and Time Formats dialog box enforces valid Date-only and DateTime format combinations, based on the input entered and the field selected. Some options may not be available for your selections.
For details about the available options, see Date and Time Formats Dialog Box.
In the Define tool window:
The Format dialog box opens.
The Date and Time Formats dialog box opens.
The Date section options are enabled.
Note: The Date and Time Formats dialog box enforces valid Date-only and DateTime format combinations, based on the input entered and the field selected. Some options may not be available for your selections.
When a date format is selected, the Format String and Sample Date are displayed for the format selected.
The Date and Time Formats dialog box closes. The selected format appears in the Date/Time Format section of the Format dialog box.
Note: Administrators and developers should note that date options set in a virtual field will override date options set in the Master File or with a SET command.
The Format dialog box opens.
The Date and Time Formats dialog box opens.
Note: The Date and Time Formats dialog box enforces valid Date-only and DateTime format combinations, based on the input entered and the field selected. Some options may not be available for your selections.
When a date format is selected, the Format String and Sample Data are displayed for the format selected.
If the field format is DateTime, you may select the Month Name, Day, Separator, Time Format, and Time Options.
For details about the options in the Date and Time Formats dialog box, see Assigning Date/Time Formats.
Note: Administrators and developers should note that date options set in a COMPUTE field override date options set in the Master File or SET command.
In addition to assigning the Date/Time field format, you may also apply the Date/Time (DT) function to be used in a Compute, Define, and Where expression. The Functions button, available from the Compute, Define, and Where tools, opens the Function Arguments dialog box, from which you can apply the DT function.
The selected graphical tool opens.
The Function Arguments dialog box opens.
The Function Arguments dialog box shows the date_time_string field.
For details about all of the available Date and Time functions, see Date and Time Functions in the Using Functions manual.
The Date Time Setup dialog box opens.
Options are Date and Time, Date Only, and Time Only. The dialog box options vary depending on the Date Time option selected.
The Date option is available when Date and Time, or Date Only, is selected.
Time options are available when Date and Time, or Time Only, is selected.
The date_time_string is shown on the bottom of the Date Time Setup dialog box as you select the options. The following image is an example of the Date Time Setup dialog box.
The date_time_string is shown in the Function Arguments dialog box.
The graphical tool shows the DT function in the expression. The following image is an example of the Where tool with a DT function.
There are multiple ways to apply the Date/Time (DT) value in an expression. The Expression Builder, available from the Where tool, opens the Value Builder dialog box, from which you apply the DT value. You can also type the DT value in the Compute and Define tools.
The selected graphical tool opens.
WHERE TESTDT EQ DT(2009-02-06 12:00:00AM);
The Expression Builder opens.
The Multiple Value Builder dialog box opens.
Note: From the Multiple Value Builder dialog box, you can search the database for an actual Date/Time value, or create the new Date/Time value with the Date Time Setup dialog box.
The following image shows the Multiple Value Builder dialog box with Date/Time values retrieved from the data source.
Options are Date and Time, Date Only, and Time Only. The dialog box options vary depending on the Date Time option selected.
The Date option is available when Date and Time, or Date Only, is selected.
Time options are available when Date and Time, or Time Only, is selected.
The date_time_string is shown on the bottom of the Date Time Setup dialog box as you select the options. The following image is an example of the Date Time Setup dialog box.
The following images shows the Multiple Value Builder dialog box with Date/Time value retrieved from the Date Time Setup dialog box.
The Date/Time (DT) value appears in the Compare Value field of the Expression Builder.
The Date/Time (DT) value appears in the expression window.
How to: |
You can view calculated values directly in the Computes tab.
In the Computes tab, click the down arrow at the right of the Field combo box, and select the desired field.
The corresponding information appears in the Field combo box, the Format box, and the expression box.
In the Computes tab:
This action displays a drop-down list that shows all the calculated values for this report.
This action displays the corresponding information in the Field combo box, the Format box, and the expression box.
The field is no longer available.
Reference: |
Computed fields that are created in the Master File are available in the field lists of Report Painter. This kind of computed field is identified by being listed in the Master File Computed Field folder of the field list and is differentiated from the Defined fields and the other Computed fields.
You can use a computed field:
Computed fields in the Master File appear in all field lists in Report Painter except for the field lists in the Define Tool and the Join Tool.
The following items are not supported when using Master File computed fields in Report Painter:
These dependencies apply to computed fields in the Master File.
When you include a computed field in selection criteria or in a page heading/footing, the field must exist in the report as either a BY sort field or a SUM/DETAIL field. If you create selection criteria with a computed field or add a computed field to a page heading/footing and the field does not already exist in the report, then it is automatically added to the report. The visible option is automatically deselected in the Field Properties General tab so that the computed field does not appear in the report output.
How to: |
Filters cannot be used in reports as regular fields and they cannot be dragged to a report, including Headings or Footings. However, if Filters are added to the report by manually editing the code, they appear as regular columns in Report Painter and return values of 0 (False) or 1 (True) at run time.
The Filter icon changes to indicate that the Filter is being used in the report request.
Note: If multiple entries are added for this Filter, they all get removed.
Note: If the Filter applied gets modified through the Expression Builder, or by manually changing the code to generate a complex statement, it no longer appears in the Field Tree as an applied Filter and it is not able to be removed from the context menu. This applies to any code that is different from the default code.
WebFOCUS |