AX2070
Standard Axiom queries are oriented vertically, with data coming into the sheet as rows. If desired, you can configure an Axiom query so that it is oriented horizontally instead, with data coming into the sheet as columns.
Horizontal Axiom queries can be used for any purpose, but one of the primary use cases is to use a horizontal query to build out components for a second, vertical Axiom query. For example, a horizontal query can be used to build out the field definition for a vertical query. Essentially, if the columns of the query need to be dynamic, a horizontal query should be used.
Configuring a query as horizontal
To configure an Axiom query as horizontal, set Change orientation to horizontal to On. In the Control Sheet, this setting is located in the Display / User Interaction Options section.
This setting is also available in the Sheet Assistant by selecting the Horizontal check box.
Once a query has been configured as horizontal, the orientation of the control rows and columns changes to match. For example, where standard vertical queries use field definition rows, horizontal queries use field definition columns. The Sheet Assistant updates dynamically for this changed orientation, so that you only see the applicable settings. When editing the Control Sheet directly, the applicable settings are located in the Horizontal Configuration section.
Any settings defined in the Vertical Configuration section are ignored.
Limitations of horizontal queries
Horizontal queries have the following limitations:
- Grouping does not apply to horizontal queries.
- Spreadsheet sorting does not apply to horizontal queries. There is no horizontal equivalent to the Sort results by these columns option; the setting is ignored.
- Data resulting only from a horizontal query cannot be drilled. However, the horizontal query is completely ignored as a drilling context, so if the data has another eligible context (from a vertical query, or by using GetData functions in the query) then that data can be drilled.
- Calc method libraries cannot be used with horizontal queries; only in-sheet calc methods can be used.
- If the horizontal query is in a template, plan file, or file group utility, then the insertion behavior must be Insert Range if the sheet has a corresponding calc method library. Inserting full columns is not supported in this context, as it would get the sheet out of sync with the calc method library.
All other Axiom query features apply to horizontal queries in the same way as vertical queries, within the context of the different orientation.
Design considerations for horizontal queries
One of the most common use cases for horizontal queries is to build out dynamic columns to be used within a standard vertical query. For example, the horizontal query can build out the field definition for the vertical query.
NOTES:
- For each sheet, Axiom queries are processed in numerical order (AQ1 first, then AQ2, etc.). If you have a configuration where a vertical query is dependent on a horizontal query, the horizontal query must be processed first.
- If you are using a horizontal query in conjunction with a vertical query or other report content, then you most likely want to set the insertion behavior for the query to InsertRange. When the query is refreshed, data will be inserted by inserting new cells within the range, rather than inserting new columns (thereby leaving content above and below the query intact). You must use the additional
[Stoprange]tag when using this option. For more information, see Insertion options for Axiom queries and Defining the insertion range when using Insert Range.
If you are building a horizontal query for the purposes of creating a field definition row for a standard vertical Axiom query, consider how you will use the horizontal query results for the second query.
In the following example, you cannot specify row 5 as the field definition row for the vertical query, because the "ColumnName" field definition for the horizontal query will not be recognized as a valid entry for the second query.
NOTE: In this example, we are querying the system table Axiom.Columns to return all columns for the GL2018 table, so "ColumnName" is a valid field definition code.
Instead, you can add a formula to the in-sheet calc method for the horizontal query, which copies the results down to the next row. In the following example, column C is the in-sheet calc method, and the formula has been placed in cell C6. The formula copies the horizontal query results in row 5 down to row 6 (and is configured to return blank if no content exists in row 5). Row 6 can now be specified as the field definition row for the vertical query.
In the examples so far, the horizontal results can be used "as is" because they are column names. If you use the horizontal query to return something else, such as departments, then you need to use a formula to create a relevant field definition row for the vertical query.
For example, imagine that you wanted to return the NYB1 data for each VP, with the VPs defining the columns (using column filters). If the horizontal query is returning VP names, then the formula that creates the field definition row for the second query would need to be something like the following:
=IF(E5="","","NYB1;dept.vp='"&E5&"'")
For columns where the horizontal query returns a VP name, this formula will create a field definition entry for the second query like so:
NYB1;dept.VP='Jones'
The following screenshot shows an example with a horizontal Axiom query populating the VP names for the field definition row of a second, vertical Axiom query. Row 6 is the field definition row for the second query.
You could take this further and use the horizontal query to set up multiple-column formatting:
AX2070
