Output to Excel Pivot Table

One of the more useful tools within Excel is the Pivot Table utility. The pivot table is a data summarization tool and among other functions, they can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data.

When reporting within Datafile the output is typically listed on a ‘flat’ basis listing the transactions or records required. Whilst you can sort, group and total many of these reports, where this is a lot of data being output it can be difficult to gather the information you require quickly.

Outputting the report as a pivot table can help you quickly summarise the data and get the information you require.Pivot table options are numerous and indeed vary between different versions of Excel but one of the first things to consider when outputting the data to pivot table is ‘what are you looking for?’

As an example your stock may be grouped into various product ranges and you may want to analyse which product ranges customers are buying. If you also analyse customers according to types you may want to identify sales opportunities by seeing that customer A buys a lot of product type X and Y, but customer B in the same industry only buys product X – customer B needs a call!

In Datafile you could report the stock transactions showing the product and sales groups – you can also total by group to give values but interpreting the data can be difficult. Outputting to an Excel pivot table could display the information in a clearer manner. The table below shows that for customer type ‘101’ both BAL001 and BAL002 buy products in ranges ‘1’ and ‘2’ but only BAL001 buys in product range ‘4

By default ‘Pivot Table’ is not enabled as an option on the print dialog – if you wish to output a report as a pivot table then you need to select the OPTIONS button and tick the parameter ‘Allow Excel Pivot Table’ – if want to do this on one-time-only basis then select the Accept button, to allow output on future basis select SAVE (the SAVE option is only available for authority ‘0’ users, or those with ‘Installer Desktop’ privileges).

Once set then when print a report the print dialog offers ‘Pivot Table’ as an output device.

The output to pivot table is available for Application and Report Generator reports although, depending on the report, the output may not be suitable for pivot table generation. As a guide listing reports would be suitable but ‘summary’ reports such as the aged debtors listing or ledger control summary are not.

Output to Pivot Table – Basic

On first output of a report a ‘basic’ options list is displayed. This asks for the item you want to analyse down the spreadsheet (account code, analysis table, stock code etc), and the value items you want to total. Options available are restricted to the items printed on the report.

Report Filter – by default the Excel table would summarise all records. This option allows you to ‘filter’ the values shown.Say, for example you are summarising sales issues by stock code, if have several thousand stock lines this could be a big list. If you set the report filter as the stock group then, in the Excel table, you can change the groups listed as required on the fly to change the data reported.

Totals – select the data values to total. All numeric items set for print on the report are listed and can tick the items required.

These options allow you to create a basic pivot table and are remembered for subsequent prints. To enable a bit more flexibility on the output select the ADVANCED button.

Output to Pivot Table – Advanced

The advanced options allow some more flexibility on the pivot table output.



The Advanced Tab allows you to select more than one item from the report to be output as rows / report filter plus allows setting of column labels to analyse across the spreadsheet also.

Report Filter – as above select the items you wish to allow run-time filters on.

Row Labels – select the items you want to summarise down the spreadsheet. More than one item can be selected and, if select more than one, a row is created for each combination of items within the data.

Values – select the items you want to total per ‘row’. Again more than one item can be selected; if you select a non-numeric item then the table gives a ‘count’ of the number of records.

Sum – applicable for ‘row’ items this creates a subtotal for the selected row/data item.

Column Labels – this option is similar to row label but summaries across the spreadsheet rather than down the spreadsheet.

Apart from the ‘Sum’ option, which works in conjunction with ‘Row Labels’, a report item can only be included as one of the options (i.e. can’t set same item as row label and value), if tick another option will unset the original selection.

Selected options are remembered for next print; the system also remembers whether you last used the basic or advanced options per report.

Output to Pivot Table – Template

The template option is an even further advanced option which allows formatting options for the pivot table. Within the OPTIONS setting a parameter asks for an ‘Excel Template’ – here you would reference a XLS file that will be created as a template on next print to a pivot table. Once created you can modify the template with Excel to add formatting, logo’s and ‘key’ labels as required.

Note

On first run after setting the template pathname (or if template not found) you will be prompted for the basic/advanced settings but all other times the Excel pivot table will be created automatically. Two template files are created on first run – the XLS and an XLS.INI – and a pivot table is created based on standard processes. Once created the template XLS can be modified with normal Excel tools.

The Excel template has two pages – shown at foot of screen – ‘Pivot’ and ‘Sheet1’.‘Pivot’ has basic sheet with where you can imbed logo or key labels but make sure these won’t overlap the pivot table itself. The second page has the commands for creating the pivot table.

We would suggest not changing any text on this screen but you can set formatting as required – make the AMOUNT column a currency value etc., put the row labels in bold and so on.

 File Locations

No installation changes in Datafile are required for this feature other than the enabling of the Pivot Table on report output. Note that the pivot table output uses four new files held within the Datafile programs folder – DBXSQLITE3.DLL, MIDAS.DLL, SQLITE3.DLL and SQLITE3.EXE.

However, you do need to ensure that Microsoft Excel, on all workstations applicable, is set to allow trust access to Microsoft Visual Basic Projects. In Excel 2007 this is achieved by selecting ‘Excel Options’ off the main menu, then Trust Centre followed by Trust Centre Settings and ‘Macro Settings’, within here tick ‘Trust access to the VBA project object model’. In Excel 2003 select Tools, Macro, Security and then the Trusted Publishers tab – within here tick ‘Trust Access to Visual Basic Project’.