AX1768

Running an individual Axiom query block on demand

You can set up a sheet so that users can run individual Axiom query blocks on demand, using the RunAxiomQueryBlock function. Users can double-click the cell with the function to run the query and populate the block. By default, users can double-click the cell again to zero the query and delete the rows in the block.

The intent of the function is to allow an Axiom query block to be run as needed by the user, instead of pre-populating the worksheet with data that may not be needed. Deferring the query until it is needed can improve performance, in cases where the worksheet does not always require the data to be present.

When using the default "collapse" behavior, the function can be used to simulate expanding and collapsing a set of rows within the worksheet, where the rows are built out via Axiom query. Instead of bringing all rows into the worksheet and then selectively hiding or showing certain rows (which can be performance-intensive), you can dynamically populate and clear blocks of rows as needed. This approach can be used to dynamically show detail rows in a section, or to perform in-sheet drilling. Alternatively you can opt to suppress the collapse behavior, so that double-clicking the function on a populated block has no effect. This option is intended to support rows with user inputs, so that the inputs are not inadvertently deleted before the user has a chance to save.

An Axiom query "block" means a set of [AQ] and [Stop] tags (data range tags). Each [AQ] tag can have a filter that limits the rows for that particular block. For example, you could have multiple Axiom query blocks in a sheet, where each block is populated with rows relating to a particular department, account, entity, job code, or any other dimension or grouping that you want to show in the sheet. You can associate a RunAxiomQueryBlock function with each block, so that users can double-click the function to populate blocks as needed.

Setting up RunAxiomQueryBlock

To enable running an Axiom query block by double-clicking a cell in the sheet, use the RunAxiomQueryBlock function. This function uses the following syntax:

The RunAxiomQueryBlock function must be placed in the same row as the [AQ] tag for the block that you want to run, or above it. When a user double-clicks the cell with the RunAxiomQueryBlock function, Axiom looks in the insert control column for the specified Axiom query, and runs the first [AQ] block that it finds at the current row or lower.

In the following example, an [AQ] tag for the Axiom query named Dept Detail is located in row 29. The RunAxiomQueryBlock function has been placed in the row above it in order to populate this block. The function could also be placed on the same row as the tag.

When you double-click on the cell containing the function, the query is run and the target block is populated. Notice that the other [AQ] blocks were not populated, even though they belong to the same Axiom query.

Because this function uses the default collapse behavior, you can double-click the cell again to zero the query and remove the rows. The sheet would then look exactly the same as it did in the first screenshot, with no rows in the Asia section. If instead the fourth parameter was set to False, then double-clicking the cell again would have no effect.

Running nested Axiom queries using RunAxiomQueryBlock

A nested Axiom query is when one "parent" query builds out the [AQ] tags of a "child" query. If the primary Axiom query for the RunAxiomQueryBlock function builds out the tags for one or more nested queries, you can also run those nested queries. Additionally, if a nested query builds out the tags for another nested query, that query can also be run.

In this example, the primary Axiom query (AQ2 - Insert Depts) brings in the departments, and then a nested Axiom query (AQ3 - Acct Detail) brings in accounts relating to each department. To run both queries, the nested query is listed in the third parameter of the function.

When the function is used, the block for the parent Axiom query is first populated. For each nested query listed (in the order listed), the following occurs:

• Axiom looks for [AQ] tags for the nested query within the rows of the primary block. The primary block starts at the [AQ] tag and ends at the [Stop] tag.

• If tags are found, the nested query block is run. If tags are not found, the process moves on to the next nested query in the list and does not error.

• Because each nested query may add rows to the original block, the size of the block is re-evaluated after each nested query is run. This means that one nested query can build out the tags for a subsequent nested query.

Setting up Axiom queries for use with RunAxiomQueryBlock

Axiom queries listed in the RunAxiomQueryBlock function must meet the following setup requirements:

• Queries must be defined on the same sheet as the function. The function cannot be used to run a query on another sheet.

• Queries must be standard vertical queries. Horizontal queries cannot be run using this function.

• The primary query must use the Rebuild refresh type. Nested queries can use any refresh type.

The queries are not required to be active, and do not require any particular refresh behavior settings. You can make the queries inactive if the only time they should be run is by using RunAxiomQueryBlock.

When the function is used on an empty [AQ] block, the specified Axiom queries are run (primary query plus any listed nested queries) and the block is populated. By default, double-clicking the function again causes the queries to be zeroed—meaning, the rows in the block are deleted, and the block is once again empty. In this case, the block can be toggled between "expanded" and "collapsed" as many times as needed. When the optional "suppress collapse" parameter is enabled, then no action occurs if the block is already populated.

IMPORTANT: If the rows in the Axiom query block contain user input cells, the default "collapse behavior" should not be used. If a user expands a block and enters data into input cells, then collapses the block without saving first, the user's inputs will be lost when the rows in the block are deleted.

When an Axiom query is run using RunAxiomQueryBlock, the database query is specially targeted so that it only brings back data for the block to be populated. Any filter on the [AQ] tag is added to the database query, to improve query performance and eliminate any unmatched data. This is different from normal Axiom query behavior, where data range filters are not added to the database query and only impact the placement of data in the sheet.

Note the following regarding Axiom query behavior when using RunAxiomQueryBlock:

• Views are reset after running Axiom queries using RunAxiomQueryBlock. This means that if some rows populated by the query are tagged to be hidden by the currently applied view, those rows will be hidden.

• The Axiom query order and any batch settings are ignored when running Axiom queries using RunAxiomQueryBlock. Nested Axiom queries are run in the order they are listed.

• The selective running of the Axiom query block is not considered to be a full refresh. Refresh-related features are not processed, such as refresh variables and data lookups.

• In plan files, users do not need to have the Run Axiom Queries permission in order to use RunAxiomQueryBlock. Queries run using this function are exempt from this security requirement.

• If you need to dynamically enable or disable this feature based on some condition, the RunAxiomQueryBlock function should be wrapped in an IF function. Disabling the Axiom queries will not disable the feature, since the queries are not required to be active.