AX1064

Limit the data in an Axiom query based on another query

You can limit the data in an Axiom query based upon another "pre-query". The limit query runs first to determine the overall set of data available to the Axiom query. The Axiom query then runs within the context of the available data.

NOTE: This is an advanced feature that should only be used by report designers who have a strong knowledge of your system's data structures. A good understanding of SQL query syntax is also helpful.

For example, you may want to create an Axiom query that only brings in items that are also used in another table. Or, you may want to only bring in items that are not used in another table, such as to create a pick list of unused items.

If you want to apply a limit query to an Axiom query, this is defined on the Control Sheet using the setting Limit query data based on another table.

Within this field, you enter special syntax to define the parameters of the limit query. In this example, the Axiom query is querying data from GL2017. We want to limit the data in the Axiom query to only those departments that are also used in the GL2016 table. The syntax used in the limit query setting identifies the following:

The column to limit within the Axiom query
The limitation behavior, to include or exclude matched records
The table and column to "pre-query" to limit the data
The "sum by" level for the limit query
An optional filter to apply to the limit query

Limit query syntax

The parameters of the limit query are defined using the following syntax:

Limit=ColumninAQ; LimitType=Include/Exclude; Select=Table.Column; Where=Criteria; Having=Criteria; Sumby=Columns

Parameter	Description
Limit	The column to limit within the Axiom query. NOTE: The legacy syntax `LimitColumn` can also be used for this parameter instead of `Limit`. You may see this used in older files.
LimitType	Optional. The type of limitation you want to perform: Include: Include all items that are matched by the limit query. This is the default limit behavior if this parameter is omitted. Exclude: Exclude all items that are matched by the limit query. For example, if you want the Axiom query to include all departments that are also found in another table, you would use the Include limit behavior. If you want the Axiom query to exclude all departments that are also found in another table, you would use the Exclude behavior.
Select	The Table.Column that you want to compare the limit column against. This defines the primary table and column for the limit query. This column must contain the same type of data as the limit column so that the contents of both can be compared against each other. NOTE: The legacy syntax `Column` can also be used for this parameter instead of `Select`. You may see this used in older files.
Where	Optional. A criteria statement to filter the limit query data. You can use any filter criteria statement that would be valid against the table specified in the Select parameter. This filter has the same behavior as the Data Filter for Axiom queries. NOTE: The legacy syntax `Filter` can also be used for this parameter instead of `Where`. You may see this used in older files.
Having	Optional. A criteria statement to filter the limit query data after it has been grouped. For more information, see Using the Having parameter in a limit query.
Sumby	Optional. The "sum by" level (grouping level) for the limit query. If omitted, the same default sum by behavior applies as for Axiom queries. In most cases, the sum by level should match (or at least end with) the column specified in the Select parameter.

The order of the parameters does not matter. If optional parameters are omitted, you do not need to delimit the omitted parameters with "empty" semicolons.

Using multiple limit queries

If needed, you can use multiple limit query statements to limit the data in your Axiom query. This might be necessary if you want to limit the data in the query using the data from more than one table, or using more than one limit column.

When using more than one limit query statement, you must place each statement in curly brackets, and connect them using AND or OR. For example:

{Limit=EncounterID; Select=EncPhys.EncounterID; Where=EncPhys.Physician.Name in ('Jones','Smith')} AND {Limit=EncounterID; Select=EncStats.EncounterID; Where=EncStat.Value = 123}

In this example, the data in the main query will be limited to the encounter IDs in the EncPhys table that are associated with the listed physicians in the filter, and where those encounter IDs are also in the EncStats table associated with the code 123.

The curly brackets are only necessary when using multiple limit query statements, to designate the beginning and end of each statement. However, single statements with curly brackets are still valid.

Using the Having parameter in a limit query

Using the Having parameter, you can optionally filter the data in the limit query after it has been grouped. The Having parameter can be used alone or with the Where parameter to filter the data in the limit query.

Technically speaking, the difference between the two parameters is as follows:

The Where parameter is applied as a WHERE clause to the resulting SQL query for the limit query. This is the same behavior as when using the Data Filter for an Axiom query. The WHERE clause is applied to determine the individual data rows to be included in the query, and then these rows are grouped according to the "sum by" level for the query (either as specified in the query parameters or assumed).
The Having parameter is applied as a HAVING clause to the resulting SQL query for the limit query. It allows you to filter the data after it has been grouped, instead of before the grouping.

The Having parameter uses the following syntax:

AggregationType(ColumnName)=Value

Where AggregationType is the desired type of aggregation, such as sum or count. Any valid aggregation type and operator type supported for the HAVING clause in the SQL database query language can be used here.

For example, imagine the sum by for the query is by region, and you want to only include regions that include 10 or more stores. You could use a limit query statement with a Having parameter, such as:

Limit=Region; Select=Dept.Region; Where=Dept.Type='Store'; Having=Count(Dept.Dept)>=10; SumBy=Dept.Region

Creating a limit query statement

If you want users to be able to create limit query statements and apply them to an Axiom query, there are several ways you can do this. The following features all support showing a wizard that allows users to create a limit query statement:

AdvancedFilter refresh variable (for use in Axiom forms or spreadsheet Axiom files)
ShowFilterWizardDialog function (for use in spreadsheet Axiom files)
Filter Wizard command (for use in Axiom forms)

When the user's selections in the wizard are complete, the wizard generates a limit query statement using the syntax described above. You can then reference this statement in the Limit query data based on another table option. The limit query statement includes the following:

The Limit column specified in the feature configuration. This determines the table columns available for selection in the wizard.
The Select column selected by the user in the wizard.
The Where filter created by the user in the wizard.

The wizard does not support user selections for any of the other parameters in the limit query statement. Since the LimitType is omitted, it is assumed to be Include. If the user is familiar with limit query syntax, they can manually edit the limit query statement before applying it to the file.