Sales By Product Details Report

The report shows quantities, profit and additional financial summary for sales by product in a given reporting period. It shows product quantity, amount invoiced, COGS, journal amount, tax and profit. The output can be customised using product details. Use this report to view product sales by customer location (state, country, etc.).

Users need the Sales Reporting permission to access this report.

Prerequisites

Managing Cin7 Core Reports

Data from this report can be exported in Excel, PDF or RTF format.

Input:

Reporting Period	Allows the user to select the reporting period from a list of options or set a custom date range. The report is filtered by transaction dates (COGS within range) or invoice date (all COGS). (See NOTE below.)
Show COGS	Choose whether COGS are calculated from COGS within the date range or all COGS on the record.(See NOTE below.)
Layout	Set to the Default layout. Allows selection of saved layouts, if any.
Save Layout As	Allows saving a layout for potential future use.
Reset Layout	Resets the layout to the default layout or selected saved layout.

Select Apply Filter to set filter fields for the report and Update to refresh the output with any new changes. See Managing Cin7 Core Reports - Filtering Reports for more detailed information about using filters.

NOTE: Sales profit summary pulls entries based on transactions dates (if COGS within date range is selected) or invoice dates (if all COGS is selected.)

Transaction dates include invoice authorization, ship tab authorization (COGS) and payments. For dropshipped products, COGS transactions are triggered when dropship purchase invoice is authorized. If you need COGS to have same date as the invoice, you need to change delivery date same as the invoice date (you can undo the ship tab to do this.)

If only COGS within date range is selected, a) you may see invoice dates from before the reporting period (with COGS transactions within the reporting period) and b) may see some 0 COGS values (if the ship date is out of the reporting period).

If All COGS is selected, the report will pull entries by invoice date, but COGS will be shown regardless of when the ship tab was authorized. If the ship tab has NOT yet been authorized, COGS will be 0.

Gift Card sales do not have COGS so will not show a margin or profit, and therefore do not appear in the report. When a gift card is redeemed, it will be used as payment, so it will be reflected as a payment amount.

Output:

The following output fields are available for this report. You may have to open the Configure Layout menu to add some of them to your report.

Column Name	Description
SKU	Product SKU
Product	Product name
Order #	Sale Order number
Order Date	Sale Order date
Document #	Invoice number
Invoice Date	Invoice issue date
Invoice Status	Status of issued invoice (e.g. Invoiced, Partially Invoiced, Invoiced/Credited)
Credit Note Date	Credit note issue date
Customer	Customer name
Customer Tags	Comma-separated customer tags
Sales Representative	Name of the Sales Representative associated with the sale
Brand	Product Brand
Category	Product Category
Family	Product Family name
Product Tags	Comma-separated product tags
Unit	Product unit of measure
Customer Additional Attributes 1-10	Values of customer additional attributes
Product Additional Attributes 1-10	Values of product additional attributes
Customer	Customer name
Sales Representative	Name of the Sales Representative associated with the sale
Year	Year of the sale issue
Quarter	Quarter of the sale issue
Month	Month of the sale issue
Weight	Weight of the sold quantity
Weight unit	Weight unit of the product
Shipment Date	Date of the shipment
Shipment Status	Status of the shipment (Shipped or Not Shipped)
Shipping Address 1	Shipment address line 1
City	Shipment address city
State	Shipment address state
Country	Shipment address country
Postcode	Shipment address postcode
Data Headers
Quantity	Quantity sold (Invoice - credit note restock)
Invoice	Invoice Line Total - Credit Note Line Total, including tax in base currency. Invoice Line Total is only included when the Invoice Date is within the specified From - To date range.
Sale	Invoice Line Total - Credit Note Line Total, excluding tax in base currency. Invoice Line Total is only included when the Invoice Date is within the specified From - To date range.
Tax	Invoice Line Tax - Credit Note Tax in base currency. Invoice Line Tax is only included when the Invoice Date is within the specified From - To date range.
COGS	Cost of Goods Sold in base currency = Invoice COGS - Credit Note COGS. Invoice COGS is only included when the Max Ship date under the Ship tab is within the specified From - To date range. Credit Note COGS is only included when the Credit Note date is within the specified From - To date range.
Journals	Total of all Additional Transactions associated with the sale in base currency distributed by invoice lines using Total amount
Profit	Profit = Sale - COGS - Tax (in base currency)

Note: If you have added an Additional Attribute set to your sale process, the attributes will appear as additional report fields.

FAQs

We are exporting a Sales by product details Report. In the report we see that some of the invoices on the report does not have a invoice date in the field. What is the reason for this?

This is the case when sales are under advanced sale module. This field (date) won't show on advanced sale since there may be multiple invoices associated with the Sale order. At the moment Cin7 Core shows multiple invoice numbers as comma delimited list on the Invoice # field, but we can't do that with the date, because it is not text. You can use the Sale Order Details and Sales Overview Report to see all invoice dates for Advanced Sales. We are aware of this but have as of yet been unable to suitably resolve it.

I ran the attached report for the month of July and found some discrepancies. A SO with an invoice date in June was picked up, but the sales value was zero even though the quantity was non-zero. My question is: how can I run a report for each month and have the quantity for each SKU and $ sales value accurately reported for that month?

There is a transaction entry for the month of July with regards to COGS. This can happen where the invoice of the PO that brought in the goods was only authorized after the shipment was authorized in the related sale order. Cin7 Core will then take the PO invoice authorized date as the COGS date as Cin7 Core uses actual costs. If you select 'All COGS' from the Show COGS list, you can see the sale with the quantity, invoice amount (incl GST) & sale amount (excl sale tax).

I'm trying to run a report showing the units sold per store for a particular customer. The different stores are currently sitting in the shipping address, therefore i have so far been unable to run a report based on the location. (only city, but sometimes we have four stores in one city) Is there anyway i can separate the stores from the particular customer?

The Sales by Product Details report have Shipping Address field you can add to the report table to separate the customer stores.

Can you please tell me why a sale order is missing from this report?

Usually, this is an issue with the date filters. This report pulls sales according to their invoice date. Double check that the reporting period covers the invoice date of the sale order you are looking for. Sales that are not invoiced will not appear.
Back-ordered sales also will not appear in the sales by product details report - because this report only shows sales that have been completed.
Missing entries can also be caused by cache problems. Try clearing your cache and refreshing to see if the issue persists.

Can you tell me how to add the margin to a sale by product details?

The margin only can be seen in the Sale Profit Summary report. In the Sale by product details report you will be able to add the below-mentioned columns to check the margin.

Sale total on the Sales by product details report and Profit and Loss report are different. Is there any reason for this?

The reports are not suppose to be exactly the same. There might be financial transactions involving revenue account that are not related to sales, like money tasks or some manual journals. You will need to drill-down to specific amounts using Trial balance to see what contributes to this amount in Profit and Loss report.

In sales by product details, the source is an invoice, and data is filtered by the invoice date. In Profit and loss by product, the source is stock movements, and date of sale proceeds will match Shipment date if it is greater than the invoice date. This can also cause records not to match.

Why there is a mismatch in total sales figure of two different sales reports (Sales Overview & Sales By Product Details) for the same month?

These two reports use different basis for their data. Sales Overview report contains all the transactions for a specific order. The date range in the header filters the sale order date. Sales By Product Details report is transaction based. It contains all the transactions within a specified period of time. The invoice totals might be different if the credit note date falls out of the date range specified since the credit note total is included in the invoice total on the reports.

For example, for a May 16 report, example SO27500 has a credit note dated 30/06/2016 so it will be included in the Sales Overview report but will be excluded from Sales by Product Details report since you are looking at May. SO31800 and SO32400 are present in Sales Overview report since the order date is in May but they are missing from Sales By Product Details report since the invoice is dated June.

Why aren't additional costs captured on the Sales Order Details and Sales By Product Details reports?

Sales Order Details and Sales by Product Details Report does not show additional charges because report focuses on product lines and order details found on document header. Try using instead the Profit by Sales Rep report, this includes additional charges because it is needed to know the profit total and currently this is how the system is designed for the reports.

Some sales are being excluded when I filter by invoice date. What is the cause of this?

This is the case when sales are under advanced sale module. This field (date) won't show on advanced sale since there may be multiple invoices associated with the Sale order. For simple sales, it will work with invoice date filter which you have added, but for advanced sales since there are multiple invoices, invoice date is empty. That's why it doesn't work and some invoices are not shown in that report. Unfortunately, there is no way of fixing this. You can try filtering by invoice month instead.

Can you please teach me how to add "location" within our sales by product details report?

The location field is not available for the Sale by Product Details report. You may want to run Customer Order by Product instead, go to More Field and drag the location field from there to the report table if it's not currently added.

Still no answer to your question? Please do not hesitate to get in touch with Cin7 Core support.

Sales By Product Details Report

Prerequisites

Input:

Output:

FAQs

Was this article helpful?