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
The Excel addin supplies some additional formulas to Excel, 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
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.
A chip extends the pinboard concept and is both more flexible and often faster. A "chip" is an area that is capable of reporting information. Excel asks the named "chip" for the details you request. we can rewrite the pinboard examples above to use chips
=chip("products","description",,"pid", 22) Return product description for product number 22 =chip("products","description",,"pid", B16) Return the current retail price for the product number stored in cell B16
Chips are often capable of searching in ways that pinboards cannot. Pinboards can only search on the values they have been designed to use, while chips are often much more flexible. eg
=chip("customers","name",0,"phone", "9921234") Return first customer found who has the phone number 9921234 =chip("customers","name",1,"phone", "9921234") Return second customer found who has the phone number 9921234
Conceptually, we find it easiest to consider chips like experts you are asking for information. Much like you would ask finance for some details or manufacturing for other details, with Chip() you first identify who you want to ask, and then specify what you want in a standard way across all chips/experts
There are also functions ChipCache() and ChipArray(). These are similar to the Chip() function. ChipCache() offers performance benefits for large spreadsheets. ChipArray() is used to retrieve bulk list style output and is useful if you wish to populate cells in bulk.
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.
- Start Excel Now.
- If you do not have Fieldpine Global Data addin already installed you need to add this first.
- Type into a cell the following formula and allow Excel to calculate it (or download the example spreadsheet)
=Chip("weather", "",, "Wellington, New Zealand")
- If all goes well, it should report a recent temperature in Wellington.
What we just did was ask Excel to invoke the Chip "weather" for "Wellington". The "weather chip" is like an electronic expert that knows about weather and only weather, so we are directing Excel to ask that expert for information. The "Wellington, New Zealand" tells the weather chip where we are interested in, and it responds with the current temperature. It gave us the temperature because we didn't explicitly say what we wanted to know, so it took a good guess.
How did the weather chip do this? Obviously it doesn't know the weather conditions for the whole world in your computer. The weather chip inside Excel has just enough knowledge to ask a server on the internet for information, so it sent a request to the internet and asked for the details, which it displayed
The ability for a chip to request data from the internet is powerful, it allows you to retrieve and use openly available data easily, lets try some more examples
=Chip("country", "GDP",, "USA") Return the current Gross Domestic Product for the USA. =Chip("NameAnalyser", "Sex",, "Matthew Smith") Ask a name analysis service to see if it can guess at the sex of a person named Matthew Smith =Chip("InternetProductDb", "Name",, "barcode=041143025123") Lookup up the product name for barcode 041143025123
Requesting lots of data from the internet can be slow and failure prone. Each chip is therefore designed to optomise performance if possible. When the weather chip asked for data, the server responded with information about how long that value can be used before it might change, and it also was sent some other data points you might ask for. If you asked for Wellington, you might ask for Auckland next
Fetching Product Information
=Chip("products", ... )
Returns the strikerate for a single department. A strikerate is the number of sales that contain at least one item from the department as a percentage of all sales. More
Returns the total current stock on hand for a single product/deparment/group/etc. There are a number of pinboards in this group which allow you to retrieve stock levels for groups of products as well as single items. More
Returns the supply importance for a single product. A numeric value used to sort products into relative order when creating purchase orders More
Returns the supply priority for a single product. A coded value used to indicate how important this item is when creating purchase orders More
Returns the strikerate for a single product. A strikerate is the number of sales that contain the product as a percentage of all sales. More
Returns the total current stock on hand or value for a complete store More
Details about product writeoffs for a product, store, period, reason code. More