Using Drilldown

Overview

Drilldown shows you a detailed view of the underlying data behind any value returned by a supported Velixo NX function. Instead of manually cross-referencing your ERP to verify a number, you can inspect it directly from Excel in one click.

This article covers:

• How to start a Drilldown

• The structure of the Drilldown sheet

• Removing Drilldown worksheets

• Supported functions

• Tips and limitations

Supported functions

Financial functions

• ACCOUNTBEGINNINGBALANCE

• ACCOUNTENDINGBALANCE

• ACCOUNTTOTALCREDITS

• ACCOUNTTOTALDEBITS

• ACCOUNTTURNOVER

Project module functions — budget balance

• PROJECTBUDGETAMOUNT

• PROJECTBUDGETQUANTITY

• PROJECTCHANGEORDERAMOUNT

• PROJECTCHANGEORDERQUANTITY

• PROJECTCOMMITTEDAMOUNT

• PROJECTCOMMITTEDCHANGEORDERAMOUNT

• PROJECTCOMMITTEDCHANGEORDERQUANTITY

• PROJECTCOMMITTEDINVOICEDAMOUNT

• PROJECTCOMMITTEDINVOICEDQUANTITY

• PROJECTCOMMITTEDOPENAMOUNT

• PROJECTCOMMITTEDOPENQUANTITY

• PROJECTCOMMITTEDQUANTITY

• PROJECTCOMMITTEDRECEIVEDQUANTITY

• PROJECTCOSTATCOMPLETION

• PROJECTCOSTTOCOMPLETE

• PROJECTPENDINGINVOICEAMOUNT

• PROJECTREVISEDBUDGETAMOUNT

• PROJECTREVISEDBUDGETQUANTITY

• PROJECTREVISEDCOMMITTEDAMOUNT

• PROJECTREVISEDCOMMITTEDQUANTITY

Project module functions — forecast balance

• PROJECTFORECASTAMOUNT

• PROJECTFORECASTQUANTITY

• PROJECTFORECASTREVISEDAMOUNT

• PROJECTFORECASTREVISEDQUANTITY

Project module functions — history balance

• PROJECTBEGINNINGAMOUNT

• PROJECTBEGINNINGQUANTITY

• PROJECTENDINGAMOUNT

• PROJECTENDINGQUANTITY

• PROJECTTURNOVERAMOUNT

• PROJECTTURNOVERQUANTITY

Starting a Drilldown

1. In your report, select the cell whose value you want to investigate.

   

2. In the Velixo NX Excel menu, click Drilldown → Drilldown.

   

   
   

   Alternatively, right-click the cell and select Velixo → Drilldown.

   

Velixo refreshes the workbook and opens a new worksheet tab containing the Drilldown results.

The Drilldown worksheet

The Drilldown worksheet is a summary table. Each row represents an entry that contributed to the drilled-down value, and each column is a field relevant to the returned entry set.

The Drilldown worksheet includes GL Account and Description, Subaccount and name, related financial periods and dates, and Branch — as well as a per-row breakdown of beginning balance, debits, credits, net turnover, and ending balance.

You can use the links in the Source Cell column to return to the original report (not available in Excel for the Web).

View transactions in the ERP interface

Click a View transactions link in the Transactions column to view the individual transactions behind a summary row.
Doing so opens the Account Details screen in Acumatica, filtered by the account, date range, and subaccount for that row.

Drilldown with multi-connection formulas

Drilldown works with formulas that reference more than one connection, including:

• A wildcard connection — for example, =TURNOVER("Connection?")

• A formula aggregating results from multiple connections — for example, =SUM(TURNOVER("Connection1"), TURNOVER("Connection2"))

Removing Drilldown worksheets

The Drilldown worksheet is static — it doesn't update when the underlying data changes. Once you've finished your analysis, remove it to keep your workbook tidy.

In the Velixo NX Excel menu, click Drilldown → Delete Drilldown Sheets.

Tips

• Drilldown on aggregated cells: You can drill down on cells that combine supported Velixo functions with Excel functions such as SUM or AVERAGE.

• Performance: Drilling down on a cell that aggregates many Velixo functions (for example, a SUM spanning a large range) will be slower than drilling down on a single function cell. Where possible, drill down at the function level rather than on aggregate totals.

Limitations

• Drilldown is not supported on cells using Excel's SUMIF, VLOOKUP, HLOOKUP, MATCH, or INDEX functions. Because these functions reference entire source ranges, Velixo cannot determine which individual cells contributed to the value.

• Cells involving complex arithmetic on Velixo values cannot be fully represented in a Drilldown. The worksheet summarizes all relevant accounts, not a formula trace.

• The Beginning Balance column total in the Drilldown worksheet may not equal the sum of the individual beginning balance rows. This can happen when transactions exist in financial periods before the period being drilled into, but no transactions occurred in that specific period for a given Ledger/Account/Subaccount/Branch combination.

• For the same reason, the beginning and ending balances in the Drilldown may differ from those shown in the Acumatica Account Details screen. The Drilldown matches the ARM calculation: the ending balance equals the cell value, and the debits/credits shown are from the most recent period with activity.

• If your Velixo function uses the ERP's Master Financial Calendar, the Account Details screen in Acumatica may not automatically check the Use Master Calendar checkbox. In Acumatica versions before 2024R1, check it manually.