AX2079

Specifying the primary table for an Axiom query

The Primary Table setting defines the default table for the Axiom query. In most cases, the primary table is a data table, but it does not have to be. For example, the primary table could be a reference table such as ACCT, if you wanted to generate a list of all current accounts.

The primary table affects which tables and columns can be used in other Axiom query settings. All settings must be valid in the context of the primary table. Generally speaking, you can use columns from tables other than the primary table as follows:

  • If the primary table is a data table, then you can also query data from any lookup reference tables, and from additional data tables that share at least one column with the data table.

  • If the primary table is a reference table, then you can also query data from any lookup reference tables, and from any tables with a direct lookup to the primary table.

Columns from other tables must be specified using full Table.Column syntax. For more information, see Using multiple tables in an Axiom query.

Applying the primary table to column-only settings in the Axiom query

If any settings in the Axiom query use only a column name (instead of a fully qualified Table.Column name), then the primary table will be assumed as the table for that column. For example, if you want to bring in data from the M1 column of the GL2018 table, then you can enter either of the following into the field definition:

In this example, if the primary table is GL2018 then both entries will return the same values, because the M1 entry is interpreted as PrimaryTable.M1 (GL2018.M1). But if the primary table is GL2017, then the M1 entry will be interpreted as GL2017.M1 instead.

However, if a column-only entry is a validated column (such as ACCT), then Axiom Software assumes the lookup table instead of the primary table. For example, an entry of ACCT will be interpreted as ACCT.ACCT instead of GL2018.ACCT. This is done so that the query can support pulling in data from multiple data tables that all use ACCT as a lookup table.

Omitting the primary table

If you are using alias names in the field definition, you can omit the primary table setting, and Axiom Software will assume the primary table based on the entries in the field definition. This allows you to change the table that the alias names point to, without needing to update the primary table setting on the report.

When the primary table is omitted, Axiom Software assumes the primary table based on the first data column it finds in the field definition (moving from left to right for vertical queries, and top to bottom for horizontal queries). Reference table columns are ignored. The data column must be an alias name or a fully qualified Table.Column name in order to assume the primary table.

If you want to see which table is being assumed as the primary table for an Axiom query, check the Sheet Assistant. When the primary table is not explicitly defined, the assumed table name displays here in gray font and with the text (inferred) after the table name.

Axiom Software will only assume a reference table as the primary table if no data columns can be identified in the field definition (either no data columns exist, or they cannot be associated with a table).

Primary table design considerations

In many cases, you can achieve the same basic query results using different primary tables. For example, if you are reporting on data from GL2019 and BGT2019, and setting the sum by to the Dept lookup column, then the primary table can be any of the following:

  • GL2019
  • BGT2019
  • Any other data table with compatible dimensions (GL2018, BGT2020, and so on)
  • Dept

However, the primary table affects the tables and columns that you can use in other Axiom query settings, and the features available to the query. The following design considerations can impact what you choose to set as the primary table:

  • Which table columns do you want to include in the field definition? For example, if a data table has lookups to the Dept and Acct reference tables, then when the data table is the primary table you can include columns from either or both of the Dept or Acct reference tables. But if the primary table is Dept then you cannot include columns from the Acct table (unless the Dept table has a lookup to it, which is not likely).

  • Which table columns do you want to use in the sum by, sort, or filter? For example, you might want to sum by Dept but filter the query by Acct. That is possible if the primary table is a data table, assuming the data table looks up to both reference tables. But if the primary table is Dept then you cannot filter by the Acct table (unless the Dept table has a lookup to it, which is not likely). Other, more advanced query settings are subject to the same basic considerations.

  • When the sum by is a lookup column, do you want to return all records in the lookup table, or just the records for which you have data? For example, if the sum by is Dept and the primary table is the Dept reference table, then the query will return all departments in that table. But if the sum by is Dept and the primary table is the GL2019 data table, then the query will only return the departments used in GL2019. (If you need to return all departments but use a data table as the primary table, then you must use a nested query setup where the first query brings in all departments from the Dept table, and then the second query brings in the data from the data table.)

  • Do you need to be able to drill the report, and if so what dimensions do you want to drill by? If the primary table is a data table, then you can drill using any dimensions that the primary table looks up to. If the primary table is a reference table, then you can only drill by that dimension (or a "child" dimension that the reference table looks up to).

See also