AX1761

RunAxiomQueryBlock function

Runs a designated Axiom query block as follows:

  • The name of an Axiom query is specified in the function parameters.

  • The function is placed in the same sheet as the Axiom query, on the same row that contains the [AQ] tag for the block to be executed, or above it.

  • When a user double-clicks on the cell containing the function, Axiom populates the first [AQ] block that it finds in the insert control column for the query, starting at the current row or lower.

  • By default, double-clicking the cell again causes the populated Axiom query block to be zeroed (deleting the populated rows). If "suppress collapse" is optionally enabled, then double-clicking the cell again has no effect.

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.

The function can also 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.

Syntax

RunAxiomQueryBlock("DisplayText", "AxiomQueryName", "NestedQueryNames", SuppressCollapse)

Parameter Description

DisplayText

The display text for the cell containing the function. For example, the text could be something like "Double-click to view detail rows".

You may want to format the cell so that the text displays like a hyperlink, or use some other special formatting to draw attention to the cell, so that users understand that the cell is interactive.

AxiomQueryName

The name of the Axiom query to be run by the function. For more information, see Axiom query requirements and behavior.

NestedQueryNames

Optional. The names of one or more nested Axiom queries that have [AQ] blocks within the primary [AQ] block to be run by the function. Separate multiple query names with semi-colons.

For example, the function may be used to run a block for the Axiom Query named Populate Departments. This query may build out [AQ] tags for another query named Populate Actuals Data. When you use the function to run a block of the Populate Departments query, you also want to run the nested block of the Populate Actuals Data query.

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.

SuppressCollapse

Specifies whether populated Axiom query blocks are zeroed on double-click.

  • If FALSE (default), then double-clicking the cell when the target Axiom query block is already populated causes the query to be zeroed, meaning the rows are deleted. Once the rows are deleted, the user can double-click again to populate the rows, and so on. This is intended for use when the Axiom query block contains display-only data (no user inputs), so that the user can "expand" and "collapse" the data block as needed.

  • If TRUE, then double-clicking the cell when the target Axiom query block is already populated does nothing. This is intended for use when the rows in the Axiom query block allow user inputs to be saved. In this case, you do not want to delete the rows because the user may not have saved yet.

All non-numeric entries must be placed in double quotation marks, unless you are using cell references to reference the text held in another cell.

Remarks

  • The Axiom Double-Click setting does not need to be enabled for the sheet in order to use RunAxiomQueryBlock.

  • RunAxiomQueryBlock is a non-volatile function.

Axiom query requirements and behavior

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.

Examples

RunAxiomQueryBlock("Double-click to see detail", "Populate Detail Rows")

This function runs an [AQ] block for the Populate Detail Rows query.

RunAxiomQueryBlock("Double-click to see detail", "Populate Departments", "Populate Detail Rows;Populate Data")

This function runs an [AQ] block for the Populate Departments query, as well as two nested queries within that block.

RunAxiomQueryBlock("Double-click to see detail", "Populate Departments", "Populate Detail Rows;Populate Data", True)

This example is the same as the previous example, except that now the collapse behavior is suppressed. The Axiom query block will not be zeroed if the function is used again on a populated block.

If no nested queries are listed, and you want to suppress the collapse behavior, then the omitted parameter must be indicated with an empty comma. For example: RunAxiomQueryBlock("Double-click to see detail", "Populate Detail Rows", , True).

See also