AX2084

Defining the sum level for an Axiom query

Each Axiom query must have a specified "sum level" that determines the level of summation for the rows in the query. To specify the sum level, list one or more columns in the Sum By setting in the Sheet Assistant.

For example, if the sum level is ACCT.ACCT, then each record of data would represent the total value for each account. If the sum level is ACCT.ACCT,DEPT.DEPT, then the same data is retrieved, but in this case each record of data represents a total for each account/department combination, and therefore more rows (or columns, if the query is horizontal) are inserted into the data range to reach the same overall total.

The sum setting can be a key column or a non-key column. If you are summing by a key column, then all key columns in the field definition should also be listed in the sum setting. For example, if your field definition contains entries for the key columns ACCT and DEPT, then the sum setting should be ACCT.ACCT,DEPT.DEPT. If the sum is set to just ACCT, then individual DEPT data would not exist due to the summation, and the DEPT field definition in the sheet would not return data as intended.

On the Control Sheet, the sum by setting is labeled Sum data by these fields.

What can be used for the sum by

Valid "sum level" entries depend on the primary table and on other tables included in the query.

Data table as primary table

When the primary table is a data table and no other data tables are included in the query, the following database columns can be used to define the sum level:

  • Any column in the primary table. You can use fully qualified Table.Column syntax or just the column name. If just the column name is used, and the column is a validated column, the associated lookup table will be assumed (such as ACCT.ACCT for an entry of ACCT).

  • Any column in a lookup table. You must use fully qualified Table.Column syntax. Multiple levels of lookups can be used.

When the primary table is a data table and additional data tables are included in the query, the following database columns can be used to define the sum level:

  • Any shared validated column in the data tables, key or non-key. The column reference must be to the shared lookup column, not to the column in the data tables. For example, you can specify DEPT (which assumes the DEPT table), or DEPT.Dept, but not GL1.Dept. (Note that if you do enter GL1.Dept in this configuration, and GL1 is the primary table, then behind the scenes the entry will be converted to Dept.Dept so that the query can be run.)

  • Any column in a shared lookup table. You must use fully qualified Table.Column syntax (such as DEPT.Region or ACCT.Category). Multiple levels of lookups can be used.

  • Any shared non-validated column in the data tables, key or non-key. In this case you must specify only the column name—do not use fully qualified syntax. For example, enter just Detail where both tables contain a non-validated column named Detail. This results in an intentional ambiguity which allows the specified column to apply to all data tables in the query. However, note that if any lookup tables also contain a column with the same name, the ambiguity will not be allowed and the column will be invalid for use in this context.

Reference table as primary table

If the primary table is a reference table, the following database columns can be used to define the sum level:

  • Any column in the primary table. You can use fully qualified Table.Column syntax, or just the column name. If just the column name is used, and the column is a validated column, the associated lookup table will be assumed.

  • Any column in a lookup reference table. You must use fully qualified Table.Column syntax. Multiple levels of lookups can be used.

If the primary table is a reference table and one or more data tables are included in the query, then the following requirements apply to the allowed sum level entries:

  • If the sum level is a column in the primary table, and that column is a validated column, then you must use fully qualified Table.Column syntax.

    For example, if the primary table Dept contains a validated column Region, and you want to use that column as the sum level, then you must enter this as Dept.Region. If instead the entry is just Region, then the lookup column will be assumed (such as Region.Region), which will make the query invalid because the table Region cannot be joined to the data table.

  • If the sum level is a column in a lookup reference table, the entry must use multiple-level lookup syntax that starts with the primary table.

    For example, if the primary table Dept contains a validated column Region, and you want to use Region.RegionType in the lookup table as the sum level, then you must enter this as Dept.Region.RegionType. If instead the entry is just Region.RegionType, this will make the query invalid because the table Region cannot be joined to the data table.

You can specify multiple columns, separated by commas. The order of the columns determines the order of the Axiom query keys in the data control column for data updates.

NOTE: Calculated fields cannot be used as the sum by.

Order of sum by columns

If you are using multiple sum by columns, the order of the columns is not important in terms of the returned data. Whether the sum by is ACCT.ACCT,DEPT.DEPT or DEPT.DEPT,ACCT.ACCT, the same data is returned.

The only thing the column order determines is the order of the codes placed in the data control column. For example, imagine that one record of data is summed by Dept 40000 and Acct 2000. If the sum by is ACCT.ACCT,DEPT.DEPT, the codes are written as 2000;40000. If the sum by is DEPT.DEPT,ACCT.ACCT, the codes are written as 40000;2000.

This order is only important if the Axiom query is using update behavior, and you are manually inserting rows into the range (such as by inserting a calc method into a plan file). In this example, the calc method must be designed to place the correct codes into the data control column, in the correct order. If the codes are in a different order than the sum by order, the row will not be updated because the Axiom query will not find matches for the codes. For more information, see Manually placing data control codes for update queries.

Omitting the sum by column

If no sum level is specified for a query, then by default the data is summed using the key columns in the primary table. If the primary table is the only data table in the query then all keys are used; whereas if multiple data tables are in the query then only validated keys are used. For example, if the primary table GL2022 has two validated key columns, DEPT and ACCT, then the default sum level is DEPT.DEPT,ACCT.ACCT.

When the sum level is not explicitly defined, the assumed sum level displays in the Sheet Assistant in gray font and with the text (inferred).

See also