In this article:
All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

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:

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.

This function is useful for calculating averages like revenue per customer or cost per unit across filtered data sets in real-time models.

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.

This function is useful for displaying period-based or multi-category values horizontally in models, which is ideal for trend analysis or dynamic forecasting layouts.

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)

This function is useful for checking data volumes such as active contracts, number of transactions, or open items based on filters.

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.

This function is useful for completeness checks and performance metrics such as filled entries or active contributions.

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.

This function is useful for extracting specific values like department names, account codes, or KPIs from reference data.

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.

This function is useful for fixed position lookups or stepping through ordered data in simulations or benchmarks.

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.

This function is useful for identifying peak metrics like maximum revenue, top sales month, or highest expense.

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.

This function is useful for locating bottom performers, minimum cash positions, or earliest events.

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.

This function is useful for passing live data into aggregations, charts, or custom calculations like forecasts or trends.

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.

This function is useful for showing item-level detail or transaction breakdowns vertically in reports and models.

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.

This function is useful for checking query scope, controlling model size, or validating incoming data sets.

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.

This function is useful for calculating key totals such as sales, costs, headcount, or hours worked for specific segments.

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.

This function is useful for documentation or diagnostics, showing query sources directly within the model structure.

QUERY(query, range)

Example:
QUERY(1, A2:D5)

Recommended reading:
Back to top | Models | Reports

We value your privacy

We use cookies to enhance your browsing experience and analyze our traffic.
By accepting, you consent to our use of cookies.

Accept Reject Cookie Policy