Overview

Using the Queries module, you can create database queries that will be executed during runtime calculations. The Queries module contains an easy to use query builder where you can select columns to be included in the query, then add conditions and constraints to filter the query results.

Creating a Query

Application

Begin by selecting a web application or data map as the data source which will be queried to retrieve data. Once you make a selection, the page will refresh and you will see the Query editor.

Name

The name is simply an identifier name which is used to distinguish between all active queries for the particular web application. This field will not be visible to your end users.

Query Builder

Query are built through the builder panels and controls.

Entities

On the Entities panel, you can view and select table and column information retrieved from the data source. You can select columns and use Add condition(s) or Add column(s) buttons on the bottom of this panel to add columns and conditions to the query as a batch.

Columns

The Columns panel consists of Expression and Title columns, and two buttons for adding or removing aggregate functions. Under the the Expression column, you can choose from columns added through the Entities panel. The Title can be edited to change the column name in the query results. When you click the functions button denoted by f, you can make a selection from a list of functions that can be applied to the selected column. Sum, Count, Average, Maximum, Minimum are some of the aggregate functions that can be selected.

Conditions

You can add a condition or a group of conditions from the Conditions panel. The conditions are filtered based on the type of column selected. For example, a field with numeric values will contain conditions like "is equal to", "is between", and "is less than". On the other hand, a datetime field will contain conditions like "within today", "within this year", and "before". A group of conditions is a composite condition where "All", "Any", "None", or "Not all of" the conditions apply. Some conditions can be bound to named ranges instead of static values. Any condition that contains "named range" in its text will be bound to one or more named ranges. The named range values in the conditions will be calculated and injected in the query during runtime calculations.

Select Top

You can limit the results to include only a certain number of top numbers using the Select Top X Rows control.

Select Distinct

When the Distinct Values is enabled, only distinct values will be retrieved from the database.

Execute Query

Pressing the Execute button will run the query and show the immediate results. This button is available for queries that do not have named range dependency conditions. After the query is executed, the query results can be exported into an Excel or CSV file using the Export to Excel and Export to CSV buttons.

Show/Hide SQL

You can see the actual query that will be executed during calculations by pressing the Show / Hide SQL button. Note that this is a readonly field, and is only displayed for your information.

Named Range

You must select a Named Range to include the query results in runtime calculations. Named ranges are filtered based on the data type inferred from the query result.

Header Behavior

When using a Table-Named Range, you must also select a Header Behavior. There are 3 type of header behaviors:

• None: The query results will be injected into the named range beginning from the first row.
• Skip First Row: The first row will be left blank, and the injection will begin from the second row.
• Inject Headers: The first row where the column names coming from the query will be injected into the first row.

Trigger on Calculate

If this option is checked, query is triggered during application calculation.

Trigger on Load

If this option is checked, query is triggered during application loading.

Skip on Load Record

Skip on Load Record is only enabled when Trigger on Load is active. If this option set to true, query is not going to run during loading record.

Tips

• When using queries to affect a named range that is otherwise an input, the query's value will take precedence over the user's input. This means that the user will not be able to set a value to a cell that is also affected by a query's results.
• A user can still set cells within a target query's named range, so long as the query does not override that particular cell. However, to achieve this, the control needs to be associated with a different named range than the query itself. For example, if you use a query to populate two columns of named range *TableNamedRange* (e.g. *SubsetTableNamedRange*), then you can still set values to the other cells in *TableNamedRange* by binding it to an input grid.

• If you use the same named range for both the query and the input grid, then the user will not be able to override any input cell values, whether or not they are _actually_ updated by the query. However, you can create a second named range that mirrors the named range from the query binding and utilize that for the input grid binding. This will allow users to update any cells that the query does not actually populate.