Drilldown (Classic)

Created by Damien Zwillinger, Modified on Wed, 21 Feb 2024 at 09:47 AM by Harry Lewis

Applies to:

Velixo Classic
Acumatica, MYOB, Cegid

The Velixo drilldown feature presents a summary of underlying data for a value returned by a Velixo function.

TABLE OF CONTENTS

Example
The Drilldown worksheet
Removing the Drilldown worksheet
Tips
Limitations
Drilldown to a separate workbook

Example

A simple example will demonstrate how easy it is to create a drilldown.

Let's consider a Trial Balance report. We can locate the Inventory Purchase Accrual account and click on the Ending Balance cell (see the figure below).

To get a drill-down for this number, we would click the Drilldown button on the Velixo ribbon and then select Drilldown.

The Drilldown option is also available at the bottom of the Excel menu when right clicking a Velixo cell.

This opens a new worksheet tab which displays summary information for the data on which the Ending Balance value is based:

The Drilldown worksheet

In a Drilldown worksheet, we can quickly navigate to the reference data:

We can click any of the links in the Source Cell column to return to the original Velixo function.
We can click any of the links in the Transactions column to open the corresponding results in the Account Details page within Acumatica.

Removing the Drilldown worksheet

The Drilldown worksheet is not dynamic (meaning that it does not update if the data in the original cell updates).

Once you are done with your analysis, you can remove the Drilldown worksheet by selecting Remove Drilldown Worksheets from the Drilldown button on the Velixo NX ribbon:

Tips

These tips will help us get more out of the drill-down feature:

Before drilling down, it is recommended that we Refresh our results. Even when a cell may display an outdated value from a prior refresh, the drill-down will always contain the latest information from Acumatica.
Drilling down can also be performed on certain Excel summarizing functions (e.g., SUM & AVERAGE).
To improve performance, we'll want to minimize the number of cells that we include in our drill-down.

Limitations

If we are using SUMIF, VLOOKUP, HLOOKUP, MATCH, or the INDEX function, the drill-down won't work. This is because it cannot identify all of the cells that were included. Since Excel applies formula auditing, it includes the entire source range in such functions, and the drill-down feature cannot display the complicated results.
Complex math operations cannot be represented, since a drill-down merely presents a summary of all the relevant accounts.
The total for the Beg Balance column might not always reflect the sum of all the individual beginning balance values displayed in the drilldown sheet. That might happen when (for a combination of Ledger/Account/Subaccount/Branch) there are transactions in financial periods that come before the financial period being drilled into, but there were no transactions for the exact same financial period we are drilling into in an ACCOUNTBEGINNINGBALANCE function.

Drilldown to a separate workbook

You can configure Velixo NX to create the Drilldown worksheet in either the current workbook or in a separate (new) workbook:

Please note that when using the New workbook setting, you will be informed that a new workbook is about to be created:

and the Source Cell links displayed on the Drilldown worksheet cannot be used to return back to the original report using Velixo NX. Rather, you will be presented with a message giving you the option to open the drilldown workbook in the Windows Desktop version of Velixo and Excel:

If both the original report and the drilldown workbook are then available in the Desktop version of Velixo and Excel, you will be able to use the Source Cell links.