Skip to main content

5-4-5 Statistical functions

In statistical functions, the argument is specified with a field inside a "Repeat" used as a detail line, or with a field inside a detail record.

Five types of functions are provided for each usage.

Table 1. List of operations by function type

Type

Behavior

Basic functions

When the mode of the VrSetForm function is 1, the data from the beginning of the page up to where the VrEndPage function is output is treated as the target data for statistical processing.

When the mode of the VrSetForm function is 4 or 5, the data up to the key-break of the specified key is treated as the target data for statistical processing.

P-type functions

The data within a page break unit is treated as the target data for statistical processing. You cannot use P-type functions in calculation-formulas.

R-type functions

These are valid only when the mode of the VrSetForm function is 4 or 5. The cumulative total is calculated. The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

G-type functions

These are valid only when the mode of the VrSetForm function is 4 or 5. All of the data is used as the target for statistical processing.

Conditional Functions

These are valid only when the mode of the VrSetForm function is 4 or 5. The data meeting the specified conditions is used as the target for statistical processing.



Reference

  • For detailed explanations of the VrSetForm and VrEndPage functions, see "SVF Java PRODUCTS API Reference Manual".

  • Statistical functions cannot handle fields that use statistical functions.

Function name

Feature

SUM / PSUM / RSUM / GSUM

Calculates the total value in numeric fields.

AVG / PAVG / RAVG / GAVG

Calculates the average value in numeric fields.

MIN / PMIN / RMIN / GMIN

Calculates the minimum value in text and numeric fields.

MAX / PMAX / RMAX / GMAX

Calculates the maximum value in text and numeric fields.

COUNT / PCOUNT / RCOUNT / GCOUNT

Calculates the total number of output data items in text and numeric fields.

SUM / PSUM / RSUM / GSUM

Feature

Calculates the total value in numeric fields.

Description

There are basic, P-type, R-type, and G-type functions provided corresponding to each usage. For details of the differences between the operations according to each function type, see "List of operations by function type".

Format

  • SUM(numeric_field_name)

    Calculates the total value.

  • PSUM(numeric_field_name)

    Calculates the total value in a page unit.

  • RSUM(numeric_field_name)

    Calculates the cumulative sum of a range.

    The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

  • GSUM(numeric_field_name)

    Calculates the total value of all data.

Settings

In "numeric_field_name", specify the name of the numeric field that contains the numeric data to be totaled.

Usage examples

Prerequisite

When designing as follows and executing with the Report-writer mode (4 or 5) of the VrSetForm function

  • XML form file

    Statistical functions are set within fields in a form that has four rows of details per page (using repeat items).

    Each field is assumed to be configured as follows:

    Field name

    Number of digits, type

    Key

    Page break

    Clear accumulated value

    Calculation-formula

    KEY

    XXXX

    1

    maru.png

    NO

    NO

    XXXX

    CODE

    99

    PRICE

    9999

    XX indicates a text field, and 99 indicates a numeric field.

    NO

    CODE

    PRICE

    1001

    1

    100

    1001

    1

    200

    1001

    1

    300

    1001

    1

    400

    1001

    1

    500

    1001

    2

    600

    1002

    3

    700

    1002

    3

    800

    1002

    3

    900

    sdCommon_i3-4-5_002.png

    Repeat fields appear as "R" in SVFX-Designer and "X" in SVF Cloud Designer from the second line onward.

  • Example 1

    To retrieve the total value of "PRICE" up to the key-break as the value of the numeric field "Field"

    Use the SUM function.

    • Calculation-formula: SUM(PRICE)

    • Edit-formula: FORMAT(Field,"ZZ,ZZ9")

  • Example 2

    To retrieve the total value in a page unit as the value of the numeric field "P_Field"

    Use the PSUM function (use it in an edit-formula because it is not available for a calculation-formula).

    • Edit-formula: FORMAT(PSUM(PRICE),"ZZ,ZZ9")

  • Example 3

    To retrieve the cumulative sum up to the key-break in CODE as the value of the numeric field "R_Field"

    Use the RSUM function.

    • Calculation-formula: RSUM(PRICE)

    • Edit-formula: FORMAT(R_Field,"ZZ,ZZ9")

  • Example 4

    To retrieve the total value of all data as the value of the numeric field "G_Field"

    Use the GSUM function.

    • Calculation-formula: GSUM(PRICE)

    • Edit-formula: FORMAT(G_Field,"ZZ,ZZ9")

Result

sdCommon_i3-4-5_hyou03.png

The value of SUM is the result of summation on the record. When calculations are performed outside of records, the entire data will be the target.

In the RSUM calculations, all records are included in the calculation of the cumulative total, as Clear accumulated value is not specified in this design example.

Restriction

  • To restore the accumulated value to "0" for each specified key, you need to select Clear accumulated value in the properties of the key field ("KEY" in the above example) for which you want to clear the accumulated value.

  • If you have selected Clear accumulated value for multiple keys, the values are restored to "0" at every key-break of the keys you specified. This means that you cannot retrieve the values retained for each key.

AVG / PAVG / RAVG / GAVG

Feature

Calculates the average value in numeric fields.

Description

There are basic, P-type, R-type, and G-type functions provided corresponding to each usage. For details of the differences between the operations according to each function type, see "List of operations by function type".

Format

  • AVG(numeric_field_name)

    Calculates the average value.

  • PAVG(numeric_field_name)

    Calculates the average value in a page unit.

  • RAVG(numeric_field_name)

    Calculates the cumulative average of a range.

    The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

  • GAVG(numeric_field_name)

    Calculates the average value of all data.

Settings

In "numeric_field_name", specify the name of the numeric field that contains the numeric data to be averaged.

Usage examples

Prerequisite

When designing as follows and executing with the Report-writer mode (4 or 5) of the VrSetForm function

  • XML form file

    Statistical functions are set within fields in a form that has four rows of details per page (using repeat items).

    Each field is assumed to be configured as follows:

    Field name

    Number of digits, type

    Key

    Page break

    Clear accumulated value

    Calculation-formula

    KEY

    XXXX

    1

    maru.png

    NO

    NO

    XXXX

    CODE

    99

    PRICE

    9999

    XX indicates a text field, and 99 indicates a numeric field.

    NO

    CODE

    PRICE

    1001

    1

    100

    1001

    1

    200

    1001

    1

    300

    1001

    1

    400

    1001

    1

    500

    1001

    2

    600

    1002

    3

    700

    1002

    3

    800

    1002

    3

    900

    sdCommon_i3-4-5_003.png

    Repeat fields appear as "R" in SVFX-Designer and "X" in SVF Cloud Designer from the second line onward.

  • Example 1

    To retrieve the average value of "PRICE" up to the key-break as the value of the numeric field "Field"

    Use the AVG function.

    • Calculation-formula: AVG(PRICE)

    • Edit-formula: FORMAT(Field,"ZZ,ZZ9")

  • Example 2

    To retrieve the average value in a page unit as the value of the numeric field "P_Field"

    Use the PAVG function (use it in an edit-formula because it is not available for a calculation-formula).

    • Edit-formula: FORMAT(PAVG(PRICE),"ZZ,ZZ9")

  • Example 3

    To retrieve the average value up to the key-break in CODE as the value of the numeric field "R_Field"

    Use the RAVG function.

    • Calculation-formula: RAVG(PRICE)

    • Edit-formula: FORMAT(R_Field,"ZZ,ZZ9")

  • Example 4

    To retrieve the average value of all data as the value of the numeric field "G_Field"

    Use the GAVG function.

    • Calculation-formula: GAVG(PRICE)

    • Edit-formula: FORMAT(G_Field,"ZZ,ZZ9")

Result

sdCommon_i3-4-5_hyou06.png

The value of AVG is calculated using data in records. When calculations are performed outside of records, the entire data will be the target.

In the RAVG calculations, all records are included in the calculation of the cumulative total, as Clear accumulated value is not specified in this design example.

Restriction

If you have selected Clear accumulated value for multiple keys, the values are restored to "0" at every key-break of the keys you specified. This means that you cannot retrieve the values retained for each key.

MIN / PMIN / RMIN / GMIN

Feature

Calculates the minimum value in text and numeric fields.

Description

There are basic, P-type, R-type, and G-type functions provided corresponding to each usage. For details of the differences between the operations according to each function type, see "List of operations by function type".

Format

  • MIN(field_name)

    Retrieves the minimum value.

  • PMIN(field_name)

    Retrieves the minimum value in a page unit.

  • RMIN(field_name)

    Calculates the cumulative minimum value of a range.

    The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

  • GMIN(field_name)

    Retrieves the minimum value of all data.

Settings

In "field_name", specify the name of the field that contains the numeric data from which to retrieve the minimum value.

Usage examples

Prerequisite

When designing as follows and executing with the Report-writer mode (4 or 5) of the VrSetForm function

  • XML form file

    Statistical functions are set within fields in a form that has four rows of details per page (using repeat items).

    Each field is assumed to be configured as follows:

    Field name

    Number of digits, type

    Key

    Page break

    Clear accumulated value

    Calculation-formula

    KEY

    XXXX

    1

    maru.png

    NO

    NO

    XXXX

    CODE

    99

    PRICE

    9999

    XX indicates a text field, and 99 indicates a numeric field.

    NO

    CODE

    PRICE

    1001

    1

    100

    1001

    1

    200

    1001

    1

    300

    1001

    1

    400

    1001

    1

    500

    1001

    2

    600

    1002

    3

    700

    1002

    3

    800

    1002

    3

    900

    sdCommon_i3-4-5_005.png

    Repeat fields appear as "R" in SVFX-Designer and "X" in SVF Cloud Designer from the second line onward.

  • Example 1

    To retrieve the minimum value of "PRICE" up to the key-break as the value of the numeric field "Field"

    Use the MIN function.

    • Calculation-formula: MIN(PRICE)

    • Edit-formula: FORMAT(Field,"ZZ,ZZ9")

  • Example 2

    To retrieve the minimum value in a page unit as the value of the numeric field "P_Field"

    Use the PMIN function (use it in an edit-formula because it is not available for a calculation-formula).

    • Edit-formula: FORMAT(PMIN(PRICE),"ZZ,ZZ9")

  • Example 3

    To retrieve the minimum value up to the key-break in CODE as the value of the numeric field "R_Field"

    Use the RMIN function.

    • Calculation-formula: RMIN(PRICE)

    • Edit-formula: FORMAT(R_Field,"ZZ,ZZ9")

  • Example 4

    To retrieve the minimum value of all data as the value of the numeric field "G_Field"

    Use the GMIN function.

    • Calculation-formula: GMIN(PRICE)

    • Edit-formula: FORMAT(G_Field,"ZZ,ZZ9")

Result

sdCommon_i3-4-5_hyou09.png

The value of MIN is calculated using data in records. When calculations are performed outside of records, the entire data will be the target.

In the RMIN calculations, all records are included in the calculation of the cumulative total, as Clear accumulated value is not specified in this design example.

Restriction

If you have selected Clear accumulated value for multiple keys, the values are restored to "0" at every key-break of the keys you specified. This means that you cannot retrieve the values retained for each key.

MAX / PMAX / RMAX / GMAX

Feature

Calculates the maximum value in text and numeric fields.

Description

There are basic, P-type, R-type, and G-type functions provided corresponding to each usage. For details of the differences between the operations according to each function type, see "List of operations by function type".

Format

  • MAX(field_name)

    Retrieves the maximum value.

  • PMAX(field_name)

    Retrieves the maximum value in a page unit.

  • RMAX(field_name)

    Calculates the cumulative maximum value of a range.

    The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

  • GMAX(field_name)

    Retrieves the maximum value of all data.

Settings

In "field_name", specify the name of the field that contains the numeric data from which to retrieve the maximum value.

Usage examples

Prerequisite

When designing as follows and executing with the Report-writer mode (4 or 5) of the VrSetForm function

  • XML form file

    Statistical functions are set within fields in a form that has four rows of details per page (using repeat items).

    Each field is assumed to be configured as follows:

    Field name

    Number of digits, type

    Key

    Page break

    Clear accumulated value

    Calculation-formula

    KEY

    XXXX

    1

    maru.png

    NO

    NO

    XXXX

    CODE

    99

    PRICE

    9999

    XX indicates a text field, and 99 indicates a numeric field.

    NO

    CODE

    PRICE

    1001

    1

    100

    1001

    1

    200

    1001

    1

    300

    1001

    1

    400

    1001

    1

    500

    1001

    2

    600

    1002

    3

    700

    1002

    3

    800

    1002

    3

    900

    sdCommon_i3-4-5_004.png

    Repeat fields appear as "R" in SVFX-Designer and "X" in SVF Cloud Designer from the second line onward.

  • Example 1

    To retrieve the maximum value of "PRICE" up to the key-break as the value of the numeric field "Field"

    Use the MAX function.

    • Calculation-formula: MAX(PRICE)

    • Edit-formula: FORMAT(Field,"ZZ,ZZ9")

  • Example 2

    To retrieve the maximum value in a page unit as the value of the numeric field "P_Field"

    Use the PMAX function (use it in an edit-formula because it is not available for a calculation-formula).

    • Edit-formula: FORMAT(PMAX(PRICE),"ZZ,ZZ9")

  • Example 3

    To retrieve the maximum value up to the key-break in CODE as the value of the numeric field "R_Field"

    Use the RMAX function.

    • Calculation-formula: RMAX(PRICE)

    • Edit-formula: FORMAT(R_Field,"ZZ,ZZ9")

  • Example 4

    To retrieve the maximum value of all data as the value of the numeric field "G_Field"

    Use the GMAX function.

    • Calculation-formula: GMAX(PRICE)

    • Edit-formula: FORMAT(G_Field,"ZZ,ZZ9")

Result

sdCommon_i3-4-5_hyou12.png

The value of MAX is calculated using data in records. When calculations are performed outside of records, the entire data will be the target.

In the RMAX calculations, all records are included in the calculation of the cumulative total, as Clear accumulated value is not specified in this design example.

Restriction

If you have selected Clear accumulated value for multiple keys, the values are restored to "0" at every key-break of the keys you specified. This means that you cannot retrieve the values retained for each key.

COUNT / PCOUNT / RCOUNT / GCOUNT

Feature

Calculates the total number of output data items in text and numeric fields.

Description

There are basic, P-type, R-type, and G-type functions provided corresponding to each usage. For details of the differences between the operations according to each function type, see "List of operations by function type".

Format

  • COUNT(field_name)

    Retrieves the number of data.

  • PCOUNT(field_name)

    Retrieves the number of data in a page unit.

  • RCOUNT(field_name)

    Calculates the cumulative number of data of a range.

    The value is restored to "0" when a key-break is encountered for a key field with Clear accumulated value selected in its properties.

  • GCOUNT(field_name)

    Retrieves the number of all data.

Settings

  1. In "field_name", specify the name of the field that contains data from which to retrieve the number of data items.

  2. In "field_name", specify text field, numeric field, or barcode item names from within repeat items or detail records.

  3. If you enclose the "field_name" with [ ] and attach @, the data in the specified field is counted only when its contents change.

Usage examples

Prerequisite

When designing as follows and executing with the Report-writer mode (4 or 5) of the VrSetForm function

  • XML form file

    Statistical functions are set within fields in a form that has four rows of details per page (using repeat items).

    Each field is assumed to be configured as follows:

    Field name

    Number of digits, type

    Key

    Page break

    Clear accumulated value

    Calculation-formula

    KEY

    XXXX

    1

    maru.png

    NO

    NO

    XXXX

    CODE

    99

    PRICE

    9999

    XX indicates a text field, and 99 indicates a numeric field.

    NO

    CODE

    PRICE

    1001

    1

    100

    1001

    1

    200

    1001

    1

    300

    1001

    1

    400

    1001

    1

    500

    1001

    2

    600

    1002

    3

    700

    1002

    3

    800

    1002

    3

    900

    sdCommon_i3-4-5_007.png

    Repeat fields appear as "R" in SVFX-Designer and "X" in SVF Cloud Designer from the second line onward.

  • Example 1

    To retrieve the number of data in "PRICE" up to the key-break as the value of the numeric field "Field"

    Use the COUNT function.

    • Calculation-formula: COUNT(PRICE)

    • Edit-formula: FORMAT(Field,"ZZ,ZZ9")

  • Example 2

    To retrieve the number of value in a page unit as the value of the numeric field "P_Field"

    Use the PCOUNT function (use it in an edit-formula because it is not available for a calculation-formula).

    • Edit-formula: FORMAT(PCOUNT(PRICE),"ZZ,ZZ9")

  • Example 3

    To retrieve the number of data up to the key-break in CODE as the value of the numeric field "R_Field"

    Use the RCOUNT function.

    • Calculation-formula: RCOUNT(PRICE)

    • Edit-formula: FORMAT(R_Field,"ZZ,ZZ9")

  • Example 4

    To retrieve the total number of data as the value of the numeric field "G_Field"

    Use the GCOUNT function.

    • Calculation-formula: GCOUNT(PRICE)

    • Edit-formula: FORMAT(G_Field,"ZZ,ZZ9")

Result

sdCommon_i3-4-5_hyou15.png

The value of COUNT is calculated using data in records. When calculations are performed outside of records, the entire data will be the target.

In the RCOUNT calculations, all records are included in the calculation of the cumulative total, as Clear accumulated value is not specified in this design example.

Restriction

If you have selected Clear accumulated value for multiple keys, the values are restored to "0" at every key-break of the keys you specified. This means that you cannot retrieve the values retained for each key.

Reference

In the COUNT function, you can retrieve the number of unique values in the specified field by specifying the field name with @ as the parameter. You can specify such a name in all formats of the COUNT functions.

For example, with the data in the above-mentioned examples, use the following setting to retrieve the number of unique values from the data in the CODE field up to the key-break, as the value of the "GROUP_CT" field:

  • Calculation-formula: COUNT([@CODE])

  • Edit-formula: FORMAT(GROUP_CT,"ZZ9")

    An example of field settings and their calculation results are as follows:

    sdCommon_i3-4-5_006.png

    Calculation results

    sdCommon_i3-4-5_hyou16.png
In this section: