A sales report starts as one table and turns into a queue of requests: the same numbers for another year, only the best sellers, the share instead of the amount, the invoices behind that one figure. Each request is a small change, and each one waits for a developer.
This post builds that report once, so that the person reading it can make those changes themselves. The data is real: the 1,067,371 invoice lines of the Online Retail II dataset, a UK online gift shop's sales from December 2009 to December 2011, with invoice number, product, quantity, unit price, customer and country on every line. The table is our free PivotGrid for React, and everything below is in its Community edition.
Here is the finished report: the eight countries with the most revenue in 2011, inside each one its five best-selling products, with units sold, the average price paid and each line's share of the year.
It is live at /demos/pivot-report. The sections below take it apart in the order it was built: the fields, the best sellers, two numbers the raw data gets wrong, the share column, saved views, and the invoice lines behind a cell.
A pivot table is configured as a list of fields. Each one names a column of your data, an area (rows, columns, values or the filter area) and how to show it:
Everything set here is also on screen. The year filter is a chip in the filter area with a filter icon on it; the sort is an arrow on the Country and Product chips. A user who wants 2010 opens the Year filter and ticks it, without anyone editing the field list.
sortBySummaryField: 'revenue' orders countries by their revenue instead of alphabetically, and topN: 8 keeps the first eight. The same two options on Product apply inside each country, so every country gets its own top five, not the shop's overall top five.
The cut changes what is listed, not what is counted. The grand total at the bottom is still the whole year, every country included: £9,032,994. The United Kingdom alone is 84.2% of it.
The first version of this report put DOTCOM POSTAGE at the top of the United Kingdom, ahead of every product. Germany had POSTAGE and Manual in its top five, Ireland Manual and CARRIAGE.
The dataset mixes products with everything else that goes through an invoice: postage, carriage, marketplace fees, bad-debt write-offs, bank charges, manual adjustments. Ten descriptions in all. A best-seller report should not count them at all, so they are removed with a prefilter, a condition applied to the source rows before anything is grouped or summed:
Because it runs before aggregation, it changes the totals too: the year's grand total is product revenue, not product revenue plus postage minus fees.
With postage gone, the next surprise was PAPER CRAFT, LITTLE BIRDIE: £168,470 and 80,995 units, the UK's best seller of 2011. One customer ordered 80,995 paper birds at 06:15 on 9 December 2011 and cancelled the order twelve minutes later.
Whether it shows up depends on one filter, the one on the Kind chip. The demo gives every line a Kind, read from its invoice number: a sale, a cancellation (numbers starting with C) or an adjustment (starting with A). Filter Kind to Sale and you are adding up orders, including the ones that were taken back; the paper birds win. Leave Kind unfiltered and cancellations subtract from the products they cancel; the paper birds net to £0 and drop out, and Regency Cakestand leads with £109,044.
The report uses net revenue. The Kind chip stays in the filter area, so anyone who needs gross sales can switch.
The average price column is the one that is easiest to get wrong. Put the unit price in the values with summaryType: 'avg' and you get the average of the line prices: for Regency Cakestand in the UK, £14.07. But a line for one cake stand and a line for 192 count the same in that average. What customers actually paid per unit is revenue divided by units, £12.83, because the large orders were the cheaper ones.
That ratio has to be computed per cell, after revenue and units are summed. A summary-level calculated field does exactly that:
The formula language is deliberately small, field references, numbers and arithmetic, and it is compiled without eval, so a formula can come from a saved report without running code. Computed row by row instead, the same formula would divide each line and then add the quotients, which is a number with no meaning.
Users can make the same change without code: right-click a value chip, open Field Settings, and pick % of total, of row or of column, a running total, or the difference or % difference from the previous value.