Article Index

Introduction to the DrillDown Plugin

Want to get to the hart of your data ? Want to be able to create custom reports in Microsoft Excel or OpenOffice Calc fast?

Then the DrillDown will help you. It has the ability to group, sort and filter all the transactions in a Set of Books with only a view mouse clicks is very powerful. If you have a problem with your data and you have entered some wrong entries you need to now what amount on what account needs to be corrected.

The DrillDown will help you hunt this entry and because of its sophisticated filter and order features does this quicker than any other way. Also will the DrillDown show you the current sales in three screens with each there different approach of displaying data.

Customers → Invoices → Stock items

Here you can locate your customer and see his invoices. The invoices show the items the items that where both. It has a good a quick navigation interface the helps you hunt down any invoice. The key element here is that you now what client has this invoice.

Stock item → Invoice

See what invoices where used to sell this stock item. Giving you the ability to find an invoice if you now the stock item. Just find that item and if it was sold you will find the invoice with that.

Invoices → Stock item

Here you can find all you invoices and the stock items that were sold on the invoice. It is a fast way of just finding that invoice if the invoice number is what you got.

Chart

And as a last feature we have made a Chart view of your sales documents, including -

Total sales count : See your total sales in a pie chart with the top of your products as separate pie pieces and the others as one total and find your most selling stock item.
Total sales amount : See your total sales amount in a pie chart with the top of your products as separate pie pieces and the others as one total and find your biggest sale stock item in amounts.
Total sales count per day: See your total sales per day in a bar chart And see how certain days affect your sales quantities.
Total sales amount per day : See your total sales amount per day in a bar chart And see how certain days affect your sales amount.

All these views (Except the chart) can be exported to XML HTML EXCEL or TEXTFILE so you can take this even a step higher if you want to.

License

This program is licensed under a commercial license. An error screen will be displayed when this Plugin is launched and will pop up with regular intervals.

This Plugin is already included in the TurboCASH releases.

Commercial: Once-off payment

Order: here.

Using the DrillDown

To use this option, you need to open a Set of Books. When the DrillDown is launched, it will by default list all the transactions in the database of the active (opened) Set of Books.

To Launch the Drilldown:

Click on the Setup → Tools → Plugins → Financial Tools → DrillDown menu.
The DrillDown (Transactions) screen is displayed:
You may use various options to sort and / or filter your data.
For example, if you need to check your sales for a specific date, select the date in the date column, and the sales in the Group1 column. Only sales will be listed for the selected date as well as a total of the sales for the day.

DrillDown (Transactions)

The columns (if all is selected), is as follows:

Account Type – There are five (5) account types (i.e. Bank, Creditor, Debtor, General Ledger and Tax).
Transaction No. – The number of the Transaction in the Transaction table of the database.
Batch No. - This is the number of the batch (journal) as allocated by TurboCASH. The last column (i.e. “Journaal”) displays a more friendly version of the Batch No.
Date – The date of the transaction.
Period – The accounting period (e.g. Month) of the transaction.
Account No. – The account code for Bank, Creditor, Debtor, General Ledger and Tax accounts.
Account – The description or account name.
Group 1 – Account group 1 for Bank, General Ledger and Tax accounts. Creditor Group 1 for Creditor accounts and Debtor Group 1 for Debtor accounts.
Group 2 – Account group 1 for Bank, General Ledger and Tax accounts. Creditor Group 1 for Creditor accounts and Debtor Group 1 for Debtor accounts.
Reference – The reference as entered in the Reference column of batches and in the case of sales documents (i.e. Invoices, Point-of-Sales Invoices and Credit Notes) and purchase documents (i.e. Purchases and Supplier Returns), the document number will be displayed.
Description – The description as entered in the Description column of batches. In the case of documents, the description is as follows:

Sales account – Stock Item's description.
Cost of Sales and Stock Control account – COST OF SALES/Document Number.
Creditor account – Document Type/Document Number.
Debtor account – Document Type/Document Number.
Tax (Input and Output Tax) – Document Type/Document Number.

Debit – Amounts entered or generated as balancing entries in the Debit column of batches. In the case of documents, the description is as follows:

Debit Tax – Amounts entered or generated as balancing entries in the Debit column of batches. In the case of documents, the description is as follows:
Debit Outstanding – Outstanding amounts in the Debit column. This is usually the same, as the Debit amount, unless a credit transaction have been linked to the debit transaction to an Open Item account.

Credit – Amounts entered or generated as balancing entries in the Credit column of batches. In the case of documents, the description is as follows:

Credit Tax – Amounts entered or generated as balancing entries in the Debit column of batches. In the case of documents, the description is as follows:
Credit Outstanding – Outstanding amounts in the Credit column. This is usually the same, as the Credit amount, unless a debit transaction have been linked to the credit transaction to an Open Item account.

Journaal - This is the Alias (Batch name). If the Change the Alias option on batches were used before posting batches, the Alias will be displayed. Should the Aliases of batches not be used, more than one batch will be listed. You may then need to use the Batch No. to filter for a specific batch.

Customise Data

Sort Transactions Sequence

All the data is by default displayed ascending; from the smallest to the highest value (e.g. a-z or 0-9) according to the Period (e.g. April, February, March, etc.).

To change the sort order from ascending (e.g. a-z or 0-9) to descending (e.g. z-a or 9-0) select a column and click on it. If you click on the same column again, it will change back to ascending sequence.

Show / hide Columns

The DrillDown displays all the columns (except the Transaction No., Batch No., Debit Tax and Credit Tax columns) by default. You may select only those columns you need to see.

To configure columns:

Click on the Options → Columns menu. The Visible Columns screen is displayed:
Select the following options, if necessary:

Show / Hide Columns– You may deselect (remove the tick) the columns which you do not wish to view by simply removing the tick from the selected column.

You may click on the All button to select all columns.

To clear all remove all the columns, click on the Clear button. You may then select place a tick only in those columns to be displayed in the DrillDown.

To select (tick) or deselect (remove the tick), you may use the Spacebar and use the Down and or Up keys to select a column.

Sequence – Should you wish to change the sequence in which the columns are displayed, you may click on the or buttons.

Once your selection is finished click on the OK button to apply these changes to the data in the DrillDown.

Move Columns using the Mouse:

You may also click on a column and drag it to the right (or to the left) to change the sequence in which you prefer the columns to be displayed.

Remove Columns using the Mouse:

If you wish to delete or remove a column, simply click on the column and drag it down and leave the mouse button.

Filter Options in Column Headings

While viewing and analysing the data you may sort and filter the data, in each column of the active or loaded table. To do this, select the column and click on the down arrow. A list displaying the data, as well as an All option and a Custom... option in the selected column will be displayed.
For example, the Reference column (i.e. SREFERENCE) of the Transaction table (i.e. TRANSACT) will display as follows:

The filter for this column (as per example) will list all reference numbers as entered in batches (journals) example, the reference for balancing entries (i,e, 8 asterisks ********) Reference column (i.e. SReference) of the Transaction Database table (i.e. TransAct.DB).

Custom Filters

You have the following options to sort or filter the data:

Select a specific entry from the list - only those entries which match the selection in the table, will be listed, e.g. with the same reference number.
All - Select to display all the entries in the selected table.
Custom - This will launch a screen on which you may set further criteria to filter the data.
1. Equal to - list or display all values which is the same as the specified value.
2. Not equal to - list or display all values which is not the same as the specified value.
3. Less than - list or display all values smaller than the specified value.
4. Less than or equal to - list or display all values smaller or equal to the specified value.
5. Greater than - list or display all values greater than the specified value.
6. Greater than or equal to - list or display all values greater or equal to the specified value.
7. Like - list all values in the table similar to the specified value.
8. Not like - list all values in the table not similar to the specified value.
9. Is null - excludes any value entered, will not be listed or displayed.
10. Is not null - is not zero - any value which is not equal to zero will be listed or displayed.

Working with Custom Filters

Once you have selected an option on a list, the your selection will be displayed at the bottom section of the DrillDown screen as follows:

In this example, Group1 Expenses or Group1 Income was selected. You may click on the Customize button to:

Make a filter (add or delete conditions and groups).
Save a filter.
Open a filter.

To make a filter:

Select a column and click on the Filter button (or on the … button) and select one of the following options on the Context menu:

New Condition
New Group

Delete row (If you click on the Filter button, you may delete all rows (conditions and groups).
Select the or option to set a filter value. The following options are available:

New Condition
New Group

Once you have created your conditions or groups, click on the Apply button.
Click on the OK button to close and exit this Make filter screen.

To Save a Custom Filter file:

Once you have sorted or filtered your data with the Make filter utility, click on the Save as... button. The Save active filter as screen will be displayed.
Select a Directory in which you wish to save the custom filter file.
Enter a file name.
Click on the Save button to save the Filter in a (*.flt) Filter File format. You may then at any later stage open the saved *.flt file.

To Open a Saved a Custom Filter file:

Once you have sorted or filtered your data with the Make filter utility, click on the Open... button. The Open saved filter as screen will be displayed.
Select a Directory in which you have saved the custom filter file.
Select a valid filter file.
Click on the Open button. The selected filter file's name will be displayed in the titlebar of the make filter screen.

Sequence of Data in Rows

All the data is by default displayed ascending; from the smallest to the highest value (e.g. a-z or 0-9) according to the record number or record ID (e.g. Accounts.DB table it would be the AccountID, in the TRANSACT table, it would be the TRANSACTIONID, etc.)

To change the sort order from ascending (e.g. a-z or 0-9) to descending (e.g. z-a or 9-0) select a column and click on it. If you click on it again it will change back to ascending sequence.

Export the Data

Once you have sorted and filtered your criteria, you may Export the data in exactly the same sequence as displayed in the TCDrillDown screen.

Once you have sorted or filtered your data, click on the File → Export menu.
The Save as screen will be displayed.
Select a Directory in which you wish to export the selected data.
Enter a file name.
Select one of the following file formats:

XML - Extensible Mark-up Language
HTML - HyperText Mark-up Language
Excel - Microsoft Excel Spreadsheet
Text - Text file

Click on the Save button. Once you have exported the data, you may locate the file and open it in your systems default program for the saved file type. For example, you may use it to build graphs in OpenOffice Calc or Microsoft Excel spreadsheets, and make powerful presentations of your data in OpenOffice Impress or Microsoft PowerPoint.

Spreadsheets

The following is examples of exported data in spreadsheets with charts for expenses exported using the DrillDown.

Create an account

TCDrillDown Plugin