How To Create a Store Report With Erply APIs

January 28, 2021

On this page

A “Store Report” is an overview of store operations. Such a report typically contains multiple tables of data, not just a single dataset. 

The report can cover only one day, or a longer period of time.

The contents vary from company to company (businesses have sometimes defined their own custom KPIs for the store personnel), but common parts of the Store Report are typically:

  • Totals By Tender
  • Sales By Product Group
  • Sales Tax / VAT Collected
  • Transaction Count and Average Transaction Size
  • Gift Cards Issued
  • Total Revenue, Returns and Discounts
  • Store Credit Earned And Spent

Assembling such a report requires pulling multiple datasets from API:

  • Sales transactions
  • Payments
  • Shift data (start and end time, cash over / short amount)
  • Store credit changes

The following sections recommend which API calls to use.

 

Totals By Tender

Erply back office lets you choose between a “Z Report”, which is centered around cashiers' shifts—and “Payments By Date”, which is a simple aggregation by date. 

For this part of the report, we recommend to pull payment totals by shift. This lets you show “Cash Over / Short” amounts, too (as overage/shortage is counted at the end of the shift). However, it is important that every register gets counted and closed every night; if a cashier forgets to do that, the report will not show accurate information.

Use the “Z Report” calls in Reports API. Fetch all the shifts that fall into the date range, and add up the data of all shifts.

API call: GET /v1/POSDay/transaction/total-by-type

unixTimeStart, unixTimeEnd and currencyId are required input parameters. Most likely you will also want to use warehouseIds (a specific store) or pointOfSaleIds (a specific register). Please note that the results are returned in “pages”, so if there are a lot of shifts in the selected period, you need to issue API calls in a loop until all the data has been retrieved.

If the report also needs to include “cash in” and “cash out” activity, use GET /v1/POSDay/transaction/cashflow.

Sales By Product Group

Call “getSalesReport” in Erply API. Use the following input parameters:

reportType=SALES_BY_PRODUCT_GROUP
dateStart=yyyy-mm-dd
dateEnd=yyyy-mm-dd

If you want sales by top-level product groups only, use SALES_BY_DEPARTMENT instead of SALES_BY_PRODUCT_GROUP.

The API call returns a CSV file; inspect the column headers to find the data you need. NAME contains product group name, NET_SALES_TOTAL contains the net total, SALES_WITH_VAT_TOTAL contains the total revenue with tax. 

Sales Tax Collected

Call “getSalesReport” in Erply API. Use reportType=SALES_BY_DATE or reportType=SALES_BY_WAREHOUSE, for example. Other options would work, too, but these probably have the least amount of data to process. 

In addition to NET_SALES_TOTAL and SALES_WITH_VAT_TOTAL, the CSV file will contain one or more columns with names starting with VAT_TOTAL…. Inpect these columns to get the total amounts of tax by tax rate.

Transaction Count and Average Size

The number of transactions within one shift can be retrieved from Reports API, with the call that returns a list of shifts: GET /v1/POSDay.

If you additionally need the average transaction size, you need to fetch a report with individual transactions (reportType=SALES_BY_INVOICE) and calculate the average.

Gift Cards Issued

The sale of an (untaxed!) gift card must be reported as a “prepayment” (at least in the European Union), not as revenue, and therefore API call getSalesReports by default excludes these sales. If this is not the expected behavior, use the flag giftCardsSales=BOTH_REGULAR_AND_GIFT_CARD. (And setting the flag to ONLY_GIFT_CARDS would return only gift card sales)

However, if you are selling gift cards without identifying them as such, these flags might not give the expected results. In that case, you may need to use a product filter (productIDs) or a product group filter (productGroupID) to single out the items you know to be gift cards.

Also, there are two perspectives to gift cards. On one hand, a gift card is just a sold good or service that will show up on a sales report; on the other hand, a gift card is a specific asset with a serial number. If you need a more detailed list of issued gift cards and their serial numbers, see Erply API call getGiftCards.

Total Revenue, Returns and Discounts

Use Erply API call getSalesReport again.

For the total amount of discounts, you can re-use one of the data sets fetched earlier; it is not necessary to make a new call. In the CSV file, there is a column DISCOUNT_TOTAL; it indicates the total amount of discounts applied to transactions. (If all transactions would have taken place at regular price, the revenue would have been larger by that amount).

However, there are different views on what counts as a “discount”! The value returned by getSalesReports generally attempts to strike a balance, but it might not be appropriate for all possible cases.

Fetching the total value of returns requires a separate call: use getSalesReport with input parameter transactionType=RETURNS. 

The total value of returns will be a negative number.

Tip: one transaction can contain both a sale and a return (eg. one row with quantity 1, one row with quantity –1). If you take a different approach than the one recommended here, keep that in mind!

Store Credit Earned and Spent

Use the “Z Report” calls in Reports API:

Timeclock Report

Use Erply API call getClockIns.

A Note About Sales Transaction Data

Please note that for sales transaction data, the API call mentioned most often is “getSalesReport”. This API call has known issues—most importantly, the returned data is in CSV format, with a variable number of columns, and a field separator that depends on account settings. 

Processing the data thus requires looking up the right column header by name.

As an alternative, you may consider a newer call in Reports API: GET /v1/generated-reports/invoice-rows. The returned format here is JSON, and the data is considerably richer.

The only downside right now is that it returns individual sales document rows; the dataset might be huge and you need to do the aggregation yourself. Other aggregated formats are coming soon.