Excel
Support
Documentation
Online POS
More

The Excel addin allows you to insert formulas directly into spreadsheets and extract information from your retail system. You can very rapidly create highly specific reports. The formulas recalculate in real time from your system, so you always have up to date information.

The Excel addin augments the standard Fieldpine reporting by allowing you to easily create a customise reports. Web browser based reports can be saved as excel spreadsheets, basically a copy of the report output at that time, but the Excel addin allows you to create dynamic spreadsheets that can include a myriad of facts

For instructions on installing the Excel Addin, see Excel Addin Installation Instructions.

Further information on individual pinboards: Pinboard Reference Guide

Concepts

The Excel addin supplies some additional formulas to Excel, the main one being the "pinboard" function, you use these formulas to extract data from your live retail system. Excel reporting is more suitable for totals type reports rather than list reports. Excel is great for "report total sales revenue yesterday", but for list style reports ("list all sales yesterday") a web browser report is often more suitable. This is because Excel is not so flexible with a variable number of output rows. Of course it is possible to create list style reports in Excel

Pinboard

A pinboard returns a single value for a named request. Pinboards can have parameters to select individual details. eg.

	=pinboard("product.description", 22)   Return product description for product number 22
	=pinboard("product.unitprice", B16)   Return the current retail price for the product number stored in cell B16
			

Pinboards can conceptually be viewed like a giant noticeboard where individual statistics are pinned to the board on post it notes. How and where the value is calculated is not important, Excel simply reports the value.

Getting Started

The quickest way to learn how to use the Excel reporting is just to try it. There are a couple of key concepts but these can be learnt from one or two examples.

  1. Start Excel Now.
  2. If you do not have Fieldpine Global Data addin already installed you need to add this first. Excel Addin Installation Instructions
  3. Download some of the examples

Examples

End of Day By Store

Download .XLS file

This example shows how a multi store retailer can implement a multi store EOD for individual stores. It uses the pinboards:

Brief Product Sales Analysis

Download .XLS file

This example shows a simple sales analysis for a single product, showing revenue and cost of goods sold. It uses the pinboards:

Internal Debug Information

Download .XLSX file

The Globaldata Addin maintains a number of counters and debug support fields. While these are not needed for normal spreadsheet use they can be helpful for support and analysis.

Department Listing

Download .XLSX file

Shows a department list with names of departments and number of products in each department. It then adds strikerate (percent of baskets that include a product from this department) for this year and last 5 years. Stock on hand quantity and value (using last cost) and also added for stores that maintain accurate stock levels.

Variable Department Listing

Download .XLSX file

An alternative method of listing departments and products within those departments. This example shows how to use "department list" to retrieve a list of department id numbers, and then use the ELEMENT() function to retrieve each value in turn

Totals by Value using Excel SUMIF

Download .XLSX file

Uses the Excel SUMIF function to produce totals on a field that isn't available as a total already. In this case a list of products and revenues are displayed alongside the "u_brandname" field, which is a site specific customisation. The individual product totals are then amalgamated via Brand to display a total for each brand.

Basket Size Information

Download .XLSX file

Reports average customer basket sizes by store and date range. Also includes a section showing the distribution of the different basket sizes observed.