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 use these common parameters:

the data parameter is the value or cell reference that you want to retrieve in the query data. This value will be compared against the value of the given key column, and only matching rows will be retrieved.

If you specify an empty data value (""), no filter will be applied and all rows will be retrieved. This is the same behaviour as user selections within the application: "no selections" = all data. If you want to retrieve only those rows that have an empty key value, you should use "@empty" as the data parameter.

As an alternative to using an empty value, you can also use "@all" as the data parameter to retrieve all rows. This returns the same result, but provides clearer intent.
the query parameter is the ID of a 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.
the column and key specify the data column to retrieve and the key column to match on. These parameters are optional: by default, all query functions assume that column 2 in the query data contains the values that you want to retrieve, and column 1 contains the key value to match on. Both parameters are integers starting at 1.

Alternatively, you can also specify the column names. This can be useful for readability, or when the columns are dynamic (e.g. a query with a variable number of periods).

If you use column numbers, you may need to amend those if you change the report layout of a query.
If you use column names, you need to amend those when you change the field names in the report.

Examples:
=DSUM("Revenue", 1, 3, 1) retrieves the sum of values in column 3 from query #1, where column 1 contains "Revenue".
=DSUM("Revenue", 1, "Amount", "Category") retrieves the sum of values in column "Amount" from query #1, where column "Category" contains "Revenue".

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

DAVERAGE

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

DAVERAGE(data, query, column, key)

Example:
DAVERAGE("2019", 1) = 5

DCOLUMNS ❖ XLReporting

Inserts dynamic values from a column in the given 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 query, where the key column matches the data parameter.
If you just want to know the total number of rows in a query, you can also use DSIZE(query)

DCOUNT(data, query, key)

Example:
DCOUNT("2019", 1) = 10

DCOUNTA

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

DCOUNTA(data, query, column, key)

Example:
DCOUNTA("2019", 1) = 6

DGET ❖ XLReporting

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

DGET(data, query, column, key)

Example:
DGET("2019", 1) = "4000"

DINDEX ❖ XLReporting

Returns a single value from a column on the given row number in the given 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) = 50

DMAX

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

DMAX(data, query, column, key)

Example:
DMAX("2019", 1) = 200

DMIN

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

DMIN(data, query, column, key)

Example:
DMIN("2019", 1) = 100

DRANGE ❖ XLReporting

Returns the data from either a single column or multiple columns, in the given query, for all rows where the key column matches the data parameter. 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 the column number or name to retrieve a single column (e.g. 3 for the 3rd column), or 0 to retrieve all columns, or -1 to retrieve all columns after the key column.

DRANGE(data, query, column, key)

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

DROWS ❖ XLReporting

Inserts dynamic values from a column in the given 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)

DSIZE ❖ XLReporting

Returns the total number of rows in the given query.

DSIZE(query)

Example:
DSIZE(1) = 10

DSUM ❖ XLReporting

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

DSUM(data, query, column, key)

Example:
DSUM("2019", 1) = 200

QUERY ❖ XLReporting

Returns the query name and type of a given 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