AX2083

Defining the sort for an Axiom query

You can sort the data for an Axiom query in one of two ways:

  • A database sort, which sorts the data before it is inserted into the data range (Data sort)
  • A spreadsheet column sort, which sorts the data after it is inserted into the data range (Sheet sort)

The database sort only applies when data is first inserted into the data range. It does not apply when updating existing data. If data already exists in a data range, and the refresh behavior is update and insert instead of rebuild, then any new records are inserted at the end of the range, and a sort is not applied.

The spreadsheet column sort applies every time a refresh is performed.

If neither sort setting is defined, the data is sorted by the columns specified in the Sum By setting, in ascending order.

Database sort

To set a database sort, enter one or more database column names into the Data Sort setting in the Sheet Assistant. You can type column names or use the column picker to select and order columns. In the Control Sheet, the corresponding setting is labeled Sort by database columns.

In all cases, the database sort level can use the same column(s) defined as the sum level for the query. If a column is valid to define the sum level, it is also valid to define the sort level, as long as both entries are the same. For more information on valid columns for the sum level, see Defining the sum level for an Axiom query. However, the sort level is not required to be the same as the sum level.

Valid database sort entries depend on the primary table and on other tables included in the query.

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 sort level:

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 sort level:

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

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 sort level entries:

  • If the sort 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 sort 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 sort 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 sort 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. For example, ACCT.ACCT,DEPT.DEPT sorts first by account, then by department, both in ascending order (the default).

If you have defined a unique name for a column that uses special syntax (such as AxAggregate or column filters), then you can use that unique name to define the sort level as long as the column would otherwise be valid for use as detailed previously.

Spreadsheet column sort

To set a spreadsheet sort, enter one or more spreadsheet column letters into the Sheet Sort setting in the Sheet Assistant. In the Control Sheet, the corresponding setting is labeled Sort results by these columns.

You can sort by any column in the sheet. You can specify multiple columns, separated by commas. For example, C,D sorts first by column C, then by column D, both in ascending order (the default).

NOTE: The spreadsheet column sort is only supported for use with standard vertical Axiom queries. This setting is ignored for horizontal queries.

Keep in mind that the column sort is applied every time the data is refreshed, which may impact performance unnecessarily. Also, certain queries are not suited to a column sort—for example, when inserting rows in a plan file that use a multi-row calc method.

Specifying ascending or descending order

You can specify ascending or descending sort order by placing Asc or Desc after the database column name or the spreadsheet column letter. If omitted, the sort is ascending order by default. For example:

Acct Asc; NYB1 Desc

This example sorts first by accounts in ascending order, then by Next Year Budget values in descending order.

See also

AX2083