Creating a Calculated Value With Report Painter

In this section:

How to:

Reference:

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.


Top of page

x
Procedure: How to Create a Calculated Value

In Report Painter:

  1. Click Computes from the Report menu or click the Compute icon located on the Setup toolbar.

    The Report Options dialog box opens at the Computes tab.

  2. Type the field name in the Field input box.
  3. Enter the desired expression in the expression box.
  4. Click OK.

Top of page

x
Procedure: How to Assign an Alphanumeric Format With the Computes Tab
  1. From the Report Options Computes tab, click the Format button.

    The Format dialog box opens.

  2. Click Alphanumeric as the Format Type.

    The default length is 20. It appears in the Length input area.

  3. To assign a different length, specify a number between 1 and 4096 in the Length input area.
  4. Click OK.

    The Format dialog box closes.

For more information on field formats, see Format Dialog Box.


Top of page

x
Procedure: How to Assign a Numeric Format With the Computes Tab
  1. From the Report Options Computes tab, click the Format button.

    The Format dialog box opens.

  2. Select one of the option buttons in the Format Types section.

    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.

  3. To assign a different length, specify a number between 1 and 9 for Floating Point, between 1 and 11 for an Integer, between 1 and 20 for Decimal, or between 1 and 33 for Packed in the Length input area.
  4. To assign a different number of decimal places for Floating Point, Decimal, or Packed, specify the desired number in the Decimal input area.
  5. If you wish to include numeric display options, select the desired options in the Edit Options list box.
  6. Click OK.

    The Format dialog box closes.

For more information on field formats, see Format Dialog Box.


Top of page

x
Reference: 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 is shown in the following image.

Format

The Format dialog box has the following fields and options:

Format Types

Specifies the format of the current field. The field type can be alphanumeric, numeric, or date/time.

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.

Length

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.

Decimal

Specifies the number of decimal places to the right of the decimal point in a Decimal, Packed Decimal, or Floating Point field.

Add minus sign if value is negative

Check this option to display a minus sign to the right of negative numeric data.

Edit Options

Adds display options to numeric field formats to control how the field will appear on reports.



x
Assigning Date/Time Formats

How to:

Reference:

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.



x
Reference: Date and Time Formats Dialog Box

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. The Date and Time Formats dialog box is shown in the following image.

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.

Field Format

Displays the type of field being created or edited.

  • Date. Assigns a Date format that represents a date or date component and enables the Date-only options.
  • DateTime. Assigns a DateTime format that represents a date and time, or date and time component, and enables the Date and Time options.

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.

Format String

Displays the current format string in FOCUS syntax, updated as changes are made in the dialog.

Sample Data

Displays a sample date and/or time value based on the current date/time and current format string.

Date Options
Format

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

Century

Opens the Century/Base dialog box, which enables you to set Century and Base options added for Y2K.

  • Century value assigns the two-digit century value to control the century values of a field.
  • Base year assigns the two-digit base year to control the century values of a field.
Month Name

Displays the month name instead of a number for the field. The options vary depending on the field format selected.

Day Name

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.

Prefix

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.

Separator

Displays the separator used in numeric dates.

  • Default applies slashes (/) as the separator.
  • Blank applies blank spaces as the separator.
  • None applies no separators.
  • Dash (--) applies dashes (-) as the separator.
  • Period (.) applies periods (.) as the separator.
  • Comma applies commas (,) as the separator.

    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.

  • T applies the letter “T” as the separator.

    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.

Time Options
Format

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.

Zero Suppression

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.

24-hour

Represent the 24-hour time format.

12-hour

Represents the 12-hour time format.

Suffix

Options vary depending on the time hour selected.

  • For 24-hour formats, the options are None or Z suffix.

    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.

  • For 12-hour formats, the options are shown as AM or PM values, depending on the time of day.


x
Procedure: How to Assign a Date/Time Format to a Field
  1. Select the date field in the Report Painter window that you want to change.
  2. Right-click and click Format from the context menu.

    or

    Click Format from the Properties menu.

    The Format dialog box opens.

  3. Click the Date/Time button on the Format dialog box.

    The Date and Time Formats dialog box opens.

  4. Use the Format drop-down list to change the Date format.

    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.

  5. Optionally, select the Date or DateTime options for the field.

    For details about the available options, see Date and Time Formats Dialog Box.

  6. Click OK to close the Date and Time Formats dialog box.
  7. Click OK to close the Formats dialog box.


x
Procedure: How to Assign a Date/Time Format With the Define Tool

In the Define tool window:

  1. Click the Format button.

    The Format dialog box opens.

  2. Click the Date/Time button from the Format Type section.

    The Date and Time Formats dialog box opens.

  3. Select Date as the Field Format.

    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.

  4. Select a date format from the Format drop-down list.

    When a date format is selected, the Format String and Sample Date are displayed for the format selected.

  5. Optionally, you may select the Month Name, Day Name, Prefix, and Separator options.
  6. Click OK.

    The Date and Time Formats dialog box closes. The selected format appears in the Date/Time Format section of the Format dialog box.

  7. Click OK to close 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.



x
Procedure: How to Assign a Date/Time Format With the Computes Tab
  1. From the Report Options Computes tab, click the Format button.

    The Format dialog box opens.

  2. Click the Date/Time button in the Format Types section.

    The Date and Time Formats dialog box opens.

  3. Select a Field Format, either Date or DateTime.

    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.

  4. Select a date format from the Format drop-down list.

    When a date format is selected, the Format String and Sample Data are displayed for the format selected.

  5. Optionally, you may select the Month Name, Day Name, Prefix, and Separator options for the Date field.

    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.

  6. Click OK to close the Date and Time Formats dialog box. The selected format appears in the Format box.
  7. Click OK to close the Format dialog box and return to the Computes tab.

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.



x
Procedure: How to Apply the Date/Time (DT) Function in Graphical Tools

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.

  1. In Report Painter, open the Compute, Define, or Where tool:
    • Click the Define icon from the Setup toolbar, or click Define from the Report menu.
    • Click the Computes icon from the Setup toolbar, or click Computes from the Report menu.
    • Click the Where button from the Where/If drop-down menu.

    The selected graphical tool opens.

  2. Click the Functions button.

    The Function Arguments dialog box opens.

  3. Select Date and Time as the category.
  4. Select DT as the function.

    The Function Arguments dialog box shows the date_time_string field and is shown in the following image.

    For details about all of the available Date and Time functions, see Date and Time Functions in the Using Functions manual.

  5. Click the Generate a date_time string button , located next to the date_time_string field.

    The Date Time Setup dialog box opens.

  6. Select the Date Time option.

    The options include Date and Time, Date Only, and Time Only. The dialog box options vary depending on the Date Time option selected.

  7. Select the Date drop-down list to open the calendar and select a date.

    The Date option is available when Date and Time, or Date Only, is selected.

  8. Select the Time option from the drop-down list, and enter the Hour, Minute, and Seconds.

    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.

  9. Click OK to close the Date Time Setup dialog box.

    The date_time_string is shown in the Function Arguments dialog box.

  10. Click OK to close the Function Arguments dialog box.
  11. Click OK to close the graphical tool.

The graphical tool shows the DT function in the expression. The following image is an example of the Where tool with a DT function.



x
Procedure: How to Apply the Date/Time (DT) Value in an Expression

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.

  1. To manually type a Date/Time (DT) value in the Compute and Define expression:
    1. In Report Painter, open the Compute or Define tool:
      • Select the Define button from the Setup toolbar, or click Define from the Report menu.
      • Select the Computes icon from the Setup toolbar, or click Computes from the Report menu.

      The selected graphical tool opens.

    2. In the expression window, manually type the Date/Time (DT) value. For example:
      WHERE TESTDT EQ DT(2009-02-06 12:00:00AM);
  2. To apply the Date/Time (DT) value in the Where tool:
    1. Click the Where button from the Where/If drop-down menu.

      The Expression Builder opens.

    2. Double-click a Date/Time field name from the list on the left side of the window to add it to the Column to filter column.
      • Click the down arrow in the Logical Relations column to select a relation from the drop-down list.
      • Click the down arrow in the Compare Type column and click Value from the drop-down list.
      • Double-click the Compare Value column.

        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.

    3. To search the database for an actual Date/Time value:
      • Click the Select a field ellipsis in the Data Context area to select the Date/Time field from the Value Retrieval list.
      • 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.
      • Double-click values to add them to the Values List.

        The following image shows the Multiple Value Builder dialog box with Date/Time values retrieved from the data source.

    4. To create the new Date/Time value with the Date Time Setup dialog box:
      • Click the Add new item icon to open the Date Time Setup dialog box.
      • Click the Date Time option.

        Options are Date and Time, Date Only, and Time Only. The dialog box options vary depending on the Date Time option selected.

      • Select the Date drop-down list to open the calendar and select a date.

        The Date option is available when Date and Time, or Date Only, is selected.

      • Select Time from the drop-down list, and enter the Hour, Minute, and Seconds.

        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.

      • Click OK to close 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.

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

      The Date/Time (DT) value appears in the Compare Value field of the Expression Builder.

    6. Click OK to close the Expression Builder.

      The Date/Time (DT) value appears in the expression window.

  3. Click OK to close the graphical tool and apply the Date/Time (DT) value to the field.

Top of page

x
Displaying Calculated Values

How to:

You can view calculated values directly in the Computes tab.



x
Procedure: How to Display Calculated Values

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.



x
Procedure: How to Delete a Calculated Value

In the Computes tab:

  1. Click the down arrow at the right of the Field combo box.

    This action displays a drop-down list that shows all the calculated values for this report.

  2. Choose the desired field.

    This action displays the corresponding information in the Field combo box, the Format box, and the expression box.

  3. Click the Delete button.

The field is no longer available.


Top of page

x
Using Master File Computed Fields

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.



x
Reference: Support for Computed Fields in the Master File in Report Painter

The following items are not supported when using Master File computed fields in Report Painter:



x
Reference: Calculated Value (Computed Field) Dependencies 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.


Top of page

x
Using Master File Filters in Report Painter

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.



x
Procedure: How to Add a WHERE Statement Using a Filter Field in Report Painter
  1. From the Object Inspector in Developer Studio, select the Filter field from the field list.
  2. Right-click and click Add filter to report from the context menu, as shown in the following image.

    The following image shows that the Filter icon changes to indicate that the Filter is being used in the report request.

  3. To remove the WHERE statement for the Filter field, right-click the Filter and click Remove filter from report from the context menu, as shown in the following image.

    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