Output to Excel Pivot Table
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
Setting a Report for Output
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.
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
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.
Templates, by default, are held/created in the working folder (usually DFWIN) unless a path is specified.
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.
As mentioned above any Excel template will be, by default, created within the working folder. The actual Pivot Table spreadsheet will be created within the MSWORD location in the same manner as output to PDF where have sub-folder for each application, within this a REPORT sub-folder, and within this a folder for each user-id.
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’.
- Release ID: 6.1