Drilldown

Created by Damien Zwillinger, Modified on Thu, 22 Feb 2024 at 07:45 PM by Harry Lewis

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

Info: Sage Intacct and Acumatica (or MYOB Advanced, Cegid XP Flex) have a slighly different implementation of Drilldown. Please review the appropriate ERP Drilldown article. 

Drilldown for Sage Intacct

TABLE OF CONTENTS

Example
The Drilldown worksheet
Removing the Drilldown worksheets
Tips & Notes
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 NX 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:

Note: If the data shown in the summary information does not include information for the subaccount segments - such as the Product Group and Department in the example shown above - those columns will not be included in the results:

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.
The Drilldown worksheet for Acumatica, CEGID, Haufe x360, JAMIS Prime, and MYOB Advanced also displays the GL Account and Description, Subaccount and name, subaccount segment(s) and names(s), the related financial periods and dates, branch, and the beginning balance, debits, credits, net turnover, and ending balance for each summary transaction used to calculate the balance for which Drilldown was selected.

Removing the Drilldown worksheets

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 & Notes

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

When drilling down, Velixo NX will automatically Refresh our results. In this way, both the report and the Drilldown worksheet will always contain the latest information from the ERP.
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

The Drilldown feature cannot be used with Excel's SUMIF, VLOOKUP, HLOOKUP, MATCH, or the INDEX functions. This is because the feature 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 Drilldown feature cannot display the complicated results.
Complex math operations cannot be represented, since a Drilldown merely presents a summary of all the relevant accounts.

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.