Functions for queries (models only)

These are special XLReporting functions that you can use in models to pull reasltime data into cells.

✭ Tips:

Most query functions support the same common parameters:

"data" is the value or cell reference that you want to lookup in the query data. This value will be compared against the value of the given "key" column, and only matching rows will be used. If you specify an empty value (e.g. ""), all query data will be used.
"query" is the ID of a report or data query. You can define queries within your model, and once defined the ID of a query is visible in the dropdown list in the query preview, which you can then reference in your functions.
"column" and "key" are optional: by default, all functions assume that column 2 contains the values that you want to retrieve, and column 1 in the query data contains the key value to compare against. Both parameters are integers starting at 1. You only need to provide the column and key as a parameter when they differ from these defaults.

For further information on models and queries, please refer to Define models.

DAVERAGE

Returns the average value from a column in the given data query, for all rows where the key column matches the "data" value.

DAVERAGE(data, query, column, key)

Example:
DAVERAGE("2017", 1)

DCOLUMNS ❖ XLReporting

Inserts dynamic values from a column in the given data query into a range of subsequent columns in a model, starting at the current cell. The "type" parameter determines whether a new column will be inserted for every value.

This only takes effect when the model is being generated in Run model. In Define model, the cell that contains this function only shows information.

DCOLUMNS is similar to DROWS, each populating the query data into a different direction in the model. If you want to insert cell ranges or results from other functions into columns (as opposed to query data), you should use the TRANSPOSE function.

Please note that you can not combine this function with other functions in the same cell.

DCOLUMNS(query, column, type)

Type:
0 - overwrite cells in existing columns (default)
1 - insert new column for every value

Example:
DCOLUMNS(1, 1)

DCOUNT

Returns the number of rows in the given data query, where the key column matches the "data" value.

DCOUNT(data, query, column, key)

Example:
DCOUNT("2017", 1)

DCOUNTA

Returns the number of non-empty and non-zero values in a column in the given data query, for all rows where the key column matches the "data" value.

DCOUNT(data, query, column, key)

Example:
DCOUNT("2017", 1)

DGET ❖ XLReporting

Returns a single value from a column in the given data query, from the first row where the key column matches the "data" value.

DGET(data, query, column, key)

Example:
DGET("2017", 1)

DINDEX ❖ XLReporting

Returns a single value from a column on the given row number in the given data query. The "row" parameter must be an integer between 1 and the number of rows in the query data.

DINDEX(query, row, column)

Example:
DINDEX(2, 1)

DMAX

Returns the highest value from a column in the given data query, for all rows where the key column matches the "data" value.

DMAX(data, query, column, key)

Example:
DMAX("2017", 1)

DMIN

Returns the lowest value from a column in the given data query, for all rows where the key column matches the "data" value.

DMIN(data, query, column, key)

Example:
DMIN("2017", 1)

DRANGE ❖ XLReporting

Returns the data either from a single column or from all columns, in the given data query, for all rows where the key column matches the "data" value. The returned data has the same structure as a cell range or data array, which enables you to pass query data directly into other functions that expect a cell range or data array (for example: math functions, aggregate functions, or sparkline functions).

The "column" parameter must be an integer, either representing a single column starting at 1 (e.g. 3 for the 3rd column), or 0 for all columns.

DRANGE(data, query, column, key)

Examples:
MEDIAN(DRANGE("2017", 1))
COMPARE(A2:C2, DRANGE("2017", 0))

DROWS ❖ XLReporting

Inserts dynamic values from a column in the given data query into a range of subsequent rows in a model, starting at the current cell. The "type" parameter determines whether a new row will be inserted for every value.

This only takes effect when the model is being generated in Run model. In Define model, the cell that contains this function only shows information.

DROWS is similar to DCOLUMNS, each populating the query data into a different direction in the model. If you want to insert cell ranges or results from other functions into rows (as opposed to query data), you should use the TRANSPOSE function.

Please note that you can not combine this function with other functions in the same cell.

DROWS(query, column, type)

Type:
0 - overwrite cells in existing rows (default)
1 - insert new row for every value

Example:
DROWS(1, 1)

DSUM ❖ XLReporting

Returns the sum of all values from a column in the given data query, for all rows where the key column matches the "data" value.

DSUM(data, query, column, key)

Example:
DSUM("2017", 1)

QUERY ❖ XLReporting

Returns the query name and type of a data query. The "range" parameter is the cell range into which the query data will be inserted.

QUERY(query, range)

Example:
QUERY(1, A2:D5)

Recommended reading:
Back to top | Models | Reports