Trying to issue a custom report to show net sales of one inventory item (filtered by divisions) in a specific timeframe (usually monthly), the problem is whatever the custom report I get, it only shows sales invoices totals for that item, while neglecting credit notes (customers’ returns).
Is there a way to combine both values of sales invoices and credit notes of an item leveraging customs reports & divisions functionalities?
Share a screenshot of the custom report definition.
Here is a screenshot:
There is a condition to filter sales rep name as you can see (for commission purposes), which I think complicated things even more!
This example does not reflect your original request related to net sales of an inventory item by division per month. You also refer to existing custom reports that fail to show sales total and ignore credit notes.
This ia all very confusing, your example does not match your request. Please provide much more exact details and a clear attempt including screenshots of what you attempted and what is missing / failed. Only then can we see if we can help you to improve it yourself.
Where... condition on
Sales Invoice are filtering the transactions to only show Sales Invoices.
If you’re filtering by a custom field, you need to have two reports per salesman, one for sales invoices and another for credit notes.
Alternatively, you can remove both sales invoice filters and instead use:
Group by custom fields of
Group by custom field of
This will combine sales invoices and credit notes but will also combine all sales persons in a single report. That’s a completely different format but it has all information.
my attempts have failed so far, I followed the steps you shared but couldn’t achieve what am looking for.
To summarize my goal from this report, I need to show sales of group of items (all within the same division) minus credit notes (customers’ returns) for the same group of items, and to show these numbers per salesman (Salesman is a custom field in both sales invoices and credit notes).
No problem if the report shows all salesmen one by one, or all divisions one by one
@sigmas55, because “Salesman” is made up of 2 Custom Fields on 2 different forms, you will not be able to Filter (
Group by Salesman. So a report that shows Total Sales minus Returns per Salesman is not possible.
I don’t know if the following report will be any closer to what you want, but you may be able to modify the definition to give you all the data you need, and then finalize the presentation in a spreadsheet:
It may make it easier to manipulate the data in a spreadsheet if the amounts for Sales and Returns are seperated into 2 columns. To do so, in the
Select section replace “AmountMultipliedByNegativeOne” by 2 lines: one for Credit and one for Debit.
It should be possible to get one report for all Divisions. Try removing the Division line from the
Where section and make Division the top line of the
Group by section.
The report can be compacted by modifying the
Order by and
Group by sections as follows:
and then deleting the “Transaction / TransactionName” line and the “Account” line from the
Select section to produce:
Group part work fine for me with a tiny little catch which is that it will display “Empty” in the groups where the custom fields isn’t applicable.
Here’s my grouping definitions:
And here’s an excerpt from the report:
Note the first chunk are Purchase Invoices with Sales Invoice Custom Field set to “Empty”; and the second chunk are Sales Invoice with Purchase Invoice Custom Field set to “Empty”.
Could you please tell me where to find this page to create reports
I should have worded that with more care.
@sigmas55 has Custom Fields on 2 different transactions forms, as you do. Both of his Custom Fields have the same name: “Salesman”. Your Custom Fields have different names.
You have grouped the report by “Sales Invoice / External reference”, and then by “Purchase Invoice / External ref.”
And so too can my report be grouped by “Sales Invoice / Salesman”, and then “Credit Note / Salesman”.
However to get the report that will “
show these numbers per salesman” would require grouping just by “Salesman” (without first choosing Sales Invoice or Credit Note). That can not be done. That is what I meant to convey with:
you will not be able to . . . Group by "Salesman".
Following your example did however produce a nice report. The
Group by section was changed to:
If a person substitutes “Returns” for the word “Empty” the report is useful. Probably not exactly what @sigmas55 wants, but as close as I can get.
Welcome to the forum @mnyassien
You can go to Reports > Custom Reports