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.
Table of Contents
Data from this report can be exported in Excel, PDF or RTF format.
|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 DEAR 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 authorisation, ship tab authorisation (COGS) and payments. For dropshipped products, COGS transactions are triggered when dropship purchase invoice is authorised. 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 authorised. If the ship tab has NOT yet been authorised, 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.
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.
|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 Tags||Comma-separated customer tags|
|Sales Representative||Name of the Sales Representative associated with the sale|
|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|
|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|
|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.
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 DEAR 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 authorised after the shipment was authorised in the related sale order. DEAR will then take the PO invoice authorised date as the COGS date as DEAR 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.
- If a product is deprecated or missing in the inventory list it won't show in the Sales By Product report. Deprecated products will show up on all other reports like profit by sales rep, customer payments, profit and loss, sales overview etc.
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. Adding Margin to Sale by Product Detail/Sale by Product Summary can be raised as a feature request on our forum and considered for future development releases.
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. However, You can leave this feature as a suggestion on our forum.
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 DEAR support.