Datafile RTD Excel Add-In

Datafile Software Solutions

Datafile RTD Excel Add-In

The Datafile system as standard allows reports to be sent to Microsoft Excel as part of the print process. Once the report is in Microsoft Excel however the data is static so when balances or transaction values change these values are not automatically updated without re-outputting the report.

The Datafile RTD Excel Addition allows Microsoft Excel to access the mirrored SQL data for the Datafile system at the click of a mouse, so you don’t have to copy and paste, re-key or work out formulas from scratch. Any cell in an Excel worksheet can obtain information from any of the mirrored Datafile tables using the Datafile Formulae builder and security can be set in Microsoft SQL Server to ensure users only have access to their relevant tables and data items.

Installation

To install the Add-In Windows 7, Windows 8 and Windows Vista users may need to run Datafile with Administrator Rights – right click on the Datafile icon and select ‘Run as Administrator’ – if prompted for a password discuss with your network administrator. Once running sign on as the SYSTEM (or if Premier any user with Installer rights) and select the Install pull-down menu on the main toolbar and select Microsoft Office.

Before installing select Excel Parameters to enter the linking details to the Microsoft SQL Server.

Datafile DLL Folder – enter the location of the Datafile programs for your system.

Server Name – enter the name of the server that Microsoft SQL Server resides.

Database Name – enter the name of the SQL Database to link to that contains the mirrored Datafile SQL tables.

Click OK to update and then select Install Excel Add-Ins button to add the Excel add-in. Note that you will need to repeat this process for each terminal that you wish to use this facility on.

Creating a Worksheet

Once the Add-In has been installed an additional tab Datafile RTD will have been added to the ribbon within MS Excel.

The Tab contains four options, although initially only one option is active.

Show (Hide) Interface – this option toggles on/off the Datafile panel that allows options to log on to the Microsoft SQL

In order to access Datafile tables and fields then you need to login to your Microsoft SQL Database. Enter the requested username and password – remember this is the SQL login detail and not your Datafile login - these credentials would normally be available from your system manager.

Other options on the Ribbon include:

 Refresh Data – selecting this option manually refreshes the data in any Datafile formulae in the worksheet.

 Edit Cell – when highlighting a cell containing a Datafile formula this transfers the formula into the Datafile panel for editing.

 Clear Cell – when highlighting a cell containing a Datafile formula this blanks out the cell and the Datafile panel.

There are two additional options on the Datafile panel – Insert Data Item and Insert Calculation.

Insert Data Item

The Insert Data Item tool allows for the insertion of data from a Datafile table

Step 1 – Select Datafile Table – from the drop-down list select the appropriate Datafile table containing the data item you need to access. The table list is read from a mirrored Datafile table SQLTBL.DFD

Step 2 – Select Data Item for Insert – select the data item you wish to display within the worksheet.

Step 3 – Select Data Item for Lookup – select the data item on the Datafile table that contains the prime-index – i.e. account code, stock code, order reference, etc.

Step 4 – Select Cell Containing Lookup Item – select the cell on the spreadsheet that contains the lookup value (i.e. account code, stock code, order reference, etc.).

If double-click the cell so it is highlighted you can use the mouse to select the cell and return the cell reference to the formula builder.

Setting the ‘fixed’ parameter means that if the formula is copied to another cell the cell reference is not updated (Relative vs. Absolute Cell References)

Step 5 - Insert Data Item – selecting this option inserts the formulae into the current cell.

Insert Calculation

The Insert Calculation tool allows you to calculate and update values from the Datafile tables.

Step 1 – Select Datafile Table – from the drop-down list select the appropriate Datafile table containing the data item you need to access. The table list is read from a mirrored Datafile table SQLTBL.DFD

Step 2 – Select Data Item Value / Function – select the data item you wish to include within the worksheet and select the function you wish to apply. Available functions are SUM, AVG, COUNT, MAX and MIN.

Step 3 – Select Lookup Item – select the data item on the Datafile table that contains the selection item or prime-index – typically you would be selecting an item that contains an account code, stock code, order reference, etc. After selecting the item select the cell on the spreadsheet that contains the lookup value (i.e. account code, stock code, order reference, etc.). If double-click the cell so is highlighted you can use the mouse to select the cell and return the cell reference to the formula builder. Setting the ‘fixed’ parameter means that if the formula is copied to another cell the cell reference is not updated.

Step 4 – Selection Criteria – enter any search criteria to be applied to the accumulated values. Up to six criteria statements can be defined and each line of criteria asks for the data item to select, the selection type (equal, greater than, less than, etc.) and match item. The match item can refer to a cell or a set value but any set value should be entered in "quotes”. Setting the ‘fixed’ parameter means that if the formula is copied to another cell the cell reference is not updated.

Step 5 - Insert Calculation – selecting this option inserts the formulae into the current cell.