getSalesReport

Get a sales report.

API returns a link to CSV file that contains the report. Field separator in the CSV is region- and configuration-specific — check getConfParameters, field "csv_field_separator".

If you want to display sales totals in graph format, and would like to retrieve a simpler data set, see the following API calls:



Purchases and Inventory Transfers are also available in report format. See getPurchaseReport and getInventoryTransferReport.

Input parameters

Parameter name Description Possible value Required
reportType  Report type. Possible values are "SALES_BY_DATE", "SALES_BY_CUSTOMER", "SALES_BY_CUSTOMER_ACCOUNT_MANAGER", "SALES_BY_CUSTOMER_GROUP", "SALES_BY_BUSINESS_AREA", "SALES_BY_WAREHOUSE", "SALES_BY_POINT_OF_SALE", "SALES_BY_PRODUCT", "SALES_BY_PRODUCT_GROUP", "SALES_BY_PRODUCT_GROUP_WITH_DRILL_DOWN", "SALES_BY_DEPARTMENT", "SALES_BY_DEPARTMENT_WITH_DRILL_DOWN", "SALES_BY_SUPPLIER", "SALES_BY_SUPPLIER_WITH_DRILL_DOWN", "SALES_BY_CATEGORY", "SALES_BY_CATEGORY_WITH_DRILL_DOWN", "SALES_BY_BRAND", "SALES_BY_ALL_PRODUCTS", "SALES_BY_CASHIER", "SALES_BY_INVOICE", "SALES_BY_INVOICE_ROWS", "SALES_BY_WEEKDAY", "SALES_BY_HOUR", "SALES_BY_MATRIX", "SALES_BY_MATRIX_WITH_DRILL_DOWN".  String  yes  
localNumberFormatting  If set to 1, sales report displays local-formatted numbers. If set to 0 or unset, sales report displays numbers in standard decimal format.  Integer    
comparisonType  Comparison type. Possible values are "WAREHOUSES-NET_SALES", "WAREHOUSES-SALES_COST", "WAREHOUSES-PROFIT", "WAREHOUSES-UNITS_SOLD", "WAREHOUSES-UNITS_SOLD_AND_NET_SALES", "WAREHOUSES-SALES_AND_STOCK_IN_RETAIL_PRICES", "WAREHOUSES-SALES_AND_STOCK_IN_COST", "PERIODS", "EMPLOYEES-NET_SALES", "EMPLOYEES-UNITS_SOLD", "EMPLOYEES-UNITS_SOLD_AND_NET_SALES".  String    
comparisonDateStart    ISO date (yyyy-mm-dd)    
comparisonDateEnd    ISO date (yyyy-mm-dd)    
getCOGS 

Setting this parameter to 1 returns a Cost of Goods Sold Report, which, in addition to total revenue, shows the cost of sold items and calculates your margins.

Leaving this parameter unset returns a Sales Report, which, in addition to total revenue, shows taxes (or VAT).

Please note that a Sales Report and a Cost of Goods Sold Report, for the same period, do not return equivalent data. The costs, revenue and taxes, if placed all side-by-side, will likely not match. Please read the additional explanations below.

 
Integer    
dateStart 

Report starting date.

If you are fetching a Sales Report, filters "dateStart" and "dateEnd" will give you all confirmed, revenue-generating sales documents (invoice-waybills, invoices, receipts, and credit invoices) which have a date in the specified date range.

If you are fetching a Cost of Goods Sold Report ("getCOGS" = 1), filters "dateStart" and "dateEnd" will give you all confirmed, inventory-affecting sales documents (invoice-waybills, waybills, receipts, and credit invoices) which have been confirmed in the specified date range — or, in other words, for which the inventory transaction (subtraction of the quantities from inventory) took place during the specified dates.

Thus, a Sales Report and a Cost Of Goods Sold Report are going to differ if:

  1. A document is confirmed later (eg. the date of an invoice-waybill is May 30, but it gets confirmed on June 2);
  2. Goods are shipped to customer with a waybill, and they get their invoice later (at the end of the month).

This approach is useful if you want to track the total value of your warehouse and how it has changed over time. For example, the total value of inventory on April 30, plus all purchases and inventory registrations confirmed in May, minus all sales and inventory write-offs confirmed in May, will equal the total value of inventory on May 31.

If, however, you need to put sales and cost side-by-side, then you can alternatively fetch the Cost of Goods Sold Report using input parameters "documentDateStart" and "documentDateEnd". This will return documents filtered by their document date, not by inventory transaction date.

 
ISO date (yyyy-mm-dd)  yes  
dateEnd 

Report end date.

 
ISO date (yyyy-mm-dd)  yes  
documentDateStart 

Use if you need to filter COGS report by invoice date as explained above.

To use this filter, your account needs to have Classic back office version 4.12 or newer.

 
ISO date (yyyy-mm-dd)    
documentDateEnd 

Use it together with "documentDateStart".

To use this filter, your account needs to have Classic back office version 4.12 or newer.

 
ISO date (yyyy-mm-dd)    
currencyCode  Currency code: EUR, USD. Currency must be defined in Erply. If omitted, or an unknown currency code is provided, API uses your default currency instead.  String (3)    
giftCardsSales  Possible values are "EXCLUDE" - exclude sales of gift cards, "ONLY_GIFT_CARDS" - sales of gift cards only, "BOTH_REGULAR_AND_GIFT_CARD" - both regular and gift card sales. By default "EXCLUDE"  String    
customerID  Customer ID.  Integer    
customerGroupID  Customer group ID.  Integer    
customerAccountManagerID  Customer account manager ID.  Integer    
businessAreaID  Customer's business area.  Integer    
salesManagerID  If set, report will contain only the transactions of a specific cashier / associate / salesperson / sales manager.  Integer    
warehouseID  Warehouse ID.  Integer    
pointOfSaleID  Point of sale ID.  Integer    
region  Region.  String    
storeGroup  Store group.  String    
productID  Product ID.  Integer    
productIDs  Filter the report by multiple products. The input parameter must contain a comma-separate list of product IDs, for example: "1,2,3,4,5".  Integer    
productGroupID  Product group ID.  Integer    
categoryID  Product category ID.  Integer    
priorityGroupID  Priority group ID.  Integer    
supplierID  Supplier ID.  Integer    
serviceID  Service ID.  Integer    
userGroupID  User group ID.  Integer    
showProductsAndServices  Possible values "ALL" - show all sales, "PRODUCTS" - show product revenue only, "SERVICES" - show service and non-stock product revenue only. By default "ALL".  String    
brandID  Brand ID.  Integer    
campaignID  Promotion ID.  Integer    
transactionType  Type of transaction. Possible values are "ALL" - all transactions (sales and returns), "SALES" - sales only, "RETURNS" - returns only. By default "ALL".  String    
displayBundles  If set to 1, sales report displays sold bundles as bundles. If set to 0 or unset, sales report displays sold bundles by components separately.  Integer    

Response

Field name Type Description
reportLink  String  Link to report file.

Note: if you try calling API getSalesReport and the output does not look as described below, your Erply account may need updating. Please contact helpdesk.

The report is a CSV file in "latin1" encoding. Fields are separated by semicolons and quoted with double quotes. The file has a header line (with standard column headers, for identifying which field contains which data) and a footer line (with totals). Here is a sample:

""​;"PRODUCT_ID"​;"SERVICE_ID"​;"CODE"​;"EAN_CODE"​;"NAME"​;"SOLD_QUANTITY"​;"UNIT"​;"NET_SALES_TOTAL"​;"SALES_WITH_VAT_TOTAL"​;"DISCOUNT_TOTAL"​;
"1"​;"239"​;"0"​;"1409575"​;"###"​;"Large​Coffee"​;"98"​;""​;"1862.00"​;"2234.40"​;"-1045.66"​;
"2"​;"243"​;"0"​;"1409579"​;"###"​;"Croissant"​;"17"​;""​;"240.55"​;"288.66"​;"-98.94"​;
"3"​;"1"​;"0"​;"001"​;"3711234567890"​;"Donut"​;"21"​;"tk"​;"210.00"​;"252.00"​;"0.00"​;
"4"​;"242"​;"0"​;"1409578"​;"###"​;"Small​Coffee"​;"14"​;""​;"116.62"​;"139.94"​;"0.00"​;
"5"​;"241"​;"0"​;"1409577"​;"###"​;"Cheese​Sandwich"​;"65"​;""​;"53.95"​;"64.74"​;"0.00"​;
"TOTAL"​;​;​;​;​;​;"215.00"​;​;"2483.12"​;"2979.74"​;"-1144.60"​;


The escape character for literal quote characters is ", like in Microsoft Excel: "This ""word"" is quoted".

First line is a header line. Each column has a specific header identifier and you can use the headers to parse data out of the file. The last line in file is a total line, identified by the word "TOTAL" in line number column.

There are two basic report types: Sales revenue reports and COGS reports. Revenue reports show revenue and taxes. COGS reports show cost of sold goods and profit. By default, API returns a Sales Report. To get COGS report, use getCOGS = 1.

All Sales Revenue reports have the following columns. (Columns are not necessarily in this specific order - here we have adjusted the order for clarity.)
  • LINE_NUMBER
  • SOLD_QUANTITY
  • NET_SALES_TOTAL
  • SALES_WITH_VAT_TOTAL
  • DISCOUNT_TOTAL
  • DISCOUNT_PERCENTAGE

The following Sales Reports will also show total VAT (total sales tax) for each tax rate:

SALES_BY_SUPPLIER, SALES_BY_CATEGORY, SALES_BY_BRAND, SALES_BY_CUSTOMER, SALES_BY_CUSTOMER_ACCOUNT_MANAGER, SALES_BY_CUSTOMER_GROUP, SALES_BY_BUSINESS_AREA, SALES_BY_DATE, SALES_BY_WAREHOUSE, SALES_BY_POINT_OF_SALE, SALES_BY_CASHIER

A tax column header looks like this:
VAT_TOTAL - ID:2 - 9%

It has three parts, separated by " - ". The first part is keyword "VAT_TOTAL". Second part contains the string "ID:", followed by tax rate ID. (use API call getVatRates to get a list of all tax rates and their IDs.). Third part is the name of the tax rate.

Since there is a separate column for each tax rate, the number of columns will vary.

All COGS reports have the following columns:
  • LINE_NUMBER
  • SOLD_QUANTITY
  • PURCHASE_VALUE
  • WAREHOUSE_VALUE
  • NET_SALES_TOTAL
  • DISCOUNT_TOTAL
  • DISCOUNT_PERCENTAGE
  • SALES_PROFIT
  • PROFIT_PERCENT
  • MARKUP_PERCENT


The rest of the columns depend on selected report type (ie. how data is grouped). In SALES_BY_PRODUCT, each report line corresponds to one product. IN SALES_BY_EMPLOYEE, each report line corresponds to one employee.

Reports SALES_BY_PRODUCT, SALES_BY_PRODUCT_GROUP_WITH_DRILL_DOWN, SALES_BY_DEPARTMENT_WITH_DRILL_DOWN, SALES_BY_SUPPLIER_WITH_DRILL_DOWN, SALES_BY_CATEGORY_WITH_DRILL_DOWN, SALES_BY_MATRIX, SALES_BY_MATRIX_WITH_DRILL_DOWN have the following columns:
  • PRODUCT_ID
  • SERVICE_ID
  • CODE
  • EAN_CODE
  • NAME
  • UNIT

Reports SALES_BY_PRODUCT_GROUP, SALES_BY_DEPARTMENT, SALES_BY_SUPPLIER, SALES_BY_CATEGORY, SALES_BY_BRAND, SALES_BY_CUSTOMER, SALES_BY_CUSTOMER_ACCOUNT_MANAGER, SALES_BY_CUSTOMER_GROUP, SALES_BY_BUSINESS_AREA, the next columns are:
  • GROUP_ID, or SUPPLIER_ID, or CATEGORY_ID, or BRAND_ID, or CUSTOMER_ID, or EMPLOYEE_ID, or BUSINESS_AREA_ID
  • NAME

Report SALES_BY_ALL_PRODUCTS has the following columns:
  • PRODUCT_ID
  • SERVICE_ID
  • CODE
  • EAN_CODE
  • NAME
  • UNIT

Report SALES_BY_DATE has the following columns:
  • DATE
  • AVERAGE_UNITS_PER_TRANSACTION
  • AVERAGE_VALUE_SOLD
  • NUMBER_OF_SALES

Report SALES_BY_WAREHOUSE has the following columns:
  • LOCATION_ID
  • LOCATION

Report SALES_BY_POINT_OF_SALE has the following columns:
  • REGISTER_ID
  • REGISTER

Report SALES_BY_CASHIER has the following columns:
  • EMPLOYEE_ID
  • NAME
  • AVERAGE_UNITS_PER_TRANSACTION
  • AVERAGE_VALUE_SOLD
  • NUMBER_OF_SALES

Report SALES_BY_INVOICE has the following columns:
  • SALES_DOCUMENT_ID
  • DATE
  • SALES_DOCUMENT
  • CUSTOMER
  • CUSTOMER_ID

Report SALES_BY_INVOICE_ROWS has the following columns:
  • SALES_DOCUMENT_ID
  • DATE
  • SALES_DOCUMENT
  • CODE
  • NAME
  • PRODUCT_ID
  • SERVICE_ID
  • CUSTOMER
  • CUSTOMER_ID

Report SALES_BY_WEEKDAY has the following columns:
  • DAY_OF_WEEK
  • DAY_NUMBER

Report SALES_BY_HOUR has the following column:
  • HOUR