AX2052

Specifying an alternate aggregation method for a field definition

By default, when you query data using an Axiom query, the results are aggregated as follows:

• Numeric columns are summed based on the Sum by level for the query. "Numeric" in this context means any column with a data type of Integer (all types) or Numeric. The following exceptions apply:

  • Key columns, validated columns, and columns with a column classification of Dimension are not summed; instead the maximum value is returned.

  • If the numeric column is on a lookup tableA table that is the target of a Lookup Column relationship in another table. For example, if the column GL2018.Acct links to the column Acct.Acct, then the Acct reference table is known as a "lookup reference table"., and the column classification is Value, then special summing behavior is applied (LookupSum). The values are summed based on the rows returned from the lookup table versus the rows returned from the primary table. For more information, see Lookup aggregation types.

• All other column types return the maximum value. In most cases, the Sum by for the query is set so that there is only one possible value per record.

In certain cases you may want to return different information, such as a count of records. This option is specified at the database column level, in the field definition. The syntax for specifying an alternate aggregation is as follows:

The entire field definition entry cannot have any spaces. The column name can use fully-qualified syntax, column-only syntax, or alias names.

NOTE: Alternate aggregation is not supported when the primary table for the query is a system table, such as Axiom.Aliases.

Standard aggregation types

The following aggregation types are supported for use with AxAggregate:

• RowCount: Returns the count of records in the query. The codes Count and RCount are also valid and return the same result.
• DistinctCount: Returns the count of unique records in the query. For example, if the query returns two records that both have a value of 10, RowCount will return 2 but DistinctCount will return 1.
• RowAvg: Returns the average value of records in the query. Does not apply when querying string or date columns. The codes Avg and RAvg are also valid and return the same result.
• RowSum: Returns the sum of records in the query. Does not apply when querying string or date columns. The codes Sum and RSum are also valid and return the same result.
• Min: Returns the minimum record in the query.
• Max: Returns the maximum record in the query.

For example, if you want to return a count of unique records queried from the CPREQ2019 table, you would enter the following syntax into the field definition row or column:

When using the standard aggregation types, the results are based on the primary table if the specified column is from the primary table or from a lookup table. If the column is on a lookup table, you should consider whether to use a lookup aggregation type instead, depending on the desired results. If the specified column is on another data table, then the results are based on that table.

Lookup aggregation types

Special lookup aggregation types are available for use when the column you want to aggregate is on a lookup reference table. This means you have a primary table with a lookup to a reference table, and you want to aggregate a column on that lookup reference table.

When using these special aggregation types, the results are based on the lookup table instead of on the primary table.

• LookupCount: Returns the count of records returned from the lookup table. The code LCount is also valid and returns the same result.
• LookupAvg: Returns the average value of records returned from the lookup table. The code LAvg is also valid and returns the same result.
• LookupSum: Returns the sum value of records returned from the lookup table. The code LSum is also valid and returns the same result.

Imagine that you want to return the average of a column on a reference table that holds a numeric value, such as CPREQ2019.UnitCost, and that reference table is not the primary table. In this case, using the standard aggregation type of RowAvg would return the average based on the query results from the primary table, which likely is not the expected average value. Instead you can use LookupAvg to return the average based on the query results from the lookup table.

To illustrate the difference, imagine that the CPREQ2019 reference table has the following values:

The average UnitCost of this data is (100+25)/2, which is 62.5. In most cases this would be the expected average for the unit cost value

However, when the primary table of the query is a different table—such as CPData2019—then standard aggregation types are calculated on data that appends the lookup reference table values to each row of the primary table. This is an issue because in the primary table, the CAPREQ keys may be listed multiple times, causing the appended lookup values such as unit cost to be repeated. For example, the values returned by the query could look like this, where CAPREQ 27 is listed multiple times:

In this case, what RowAvg returns is (100+25+100)/3, which is 75. In order to get the expected average based on the lookup reference table instead of the primary table, you must use LookupAvg—which returns 62.5.

This issue does not apply when the reference table is the primary table of the query. If the primary table was CPREQ2019, then RowAvg would be calculated on the primary table and return the expected value of 62.5.

Using column filters with AxAggregate

You can use column filters and AxAggregate together on a field definition entry, so that the alternate aggregation is applied to a subset of data (as defined by the filter), instead of all the data returned by that column. For example:

All aggregation types can be used in combination with column filters.

Naming a column that uses alternate aggregation

You can optionally name a column that uses AxAggregate, so that it can be used in the following Axiom query features:

• Data Sort
• Post-Query Filter

Defining a name allows you to use the alternate aggregated results of the column in these settings, instead of the default aggregated results. If you use just the column name in an Axiom query setting, then the setting uses the default aggregation. But if you define a unique name for the AxAggregate column and then use the unique name, the setting then uses the alternate aggregation for that specific named column.

To name a column that uses AxAggregate, append a unique name to the base column name using a colon. For example: