You’re offline. This is a read only version of the page.
I am trying to pull the data for the commission report into Business Central. I did this through the Customer Ledger Entries table, but the profit doesn't match to the commissions report. Looking into this, it appears that it is due to the commission report being based on the Invoice Statistics Adjusted amount, but the Profit amount in the Customer Ledger Entries is different. How can I determine the adjusted profit amount? I know the invoice statistics page 397 can be set as a web service, but is there a table that has the cost, or profit percentage or something to let me calculate the profit, or can I link the customer Ledger entry table to this page web service since the 397 doesn't give much information except the stats of the invoice.
For example, I have invoice IN1000095 in the customer ledger table for sale of $99,867.32 and a profit of $28,257.45. When I run the salesperson commission report, this invoice shows for $27,931.33. I can find that on the invoice statistics report or web service I created for page 397, but not anywhere else. Any guidance/ideas?
Thanks,
Kevin
The best table to use for reporting Cost and profit when it comes to item sales will in my opinion be the Item Ledger Entry table.
There you will have a link to the corresponding sales invoice.
Thanks Daniele, what I am not sure is how to create the new data item in the web service that pulls from the invoice statistics where that is not a table, but a page. When I try and do a data item link, I don't see anything available for invoice statistics.
Thanks,
Kevin
hi
It sounds like you are on the right track in using the Invoice Statistics Adjusted amount to calculate the commission report instead of the profit amount in the Customer Ledger Entries table. The Invoice Statistics page (397) is a good source for this information, but as you mentioned, it does not provide the cost or profit percentage.
To determine the adjusted profit amount, you can use the following formula:
Adjusted Profit = (Invoice Statistics Adjusted Amount) - (Direct Cost + Commission Amount)
Here, the Direct Cost is the cost of goods sold, and the Commission Amount is the commission paid to the salesperson. You can find the Direct Cost in the Item Ledger Entries table or the Value Entries table, depending on your setup.
To link the Customer Ledger Entries table to the Invoice Statistics web service, you can create a new data item in the web service that includes the relevant fields from both tables. Then, you can use a codeunit or report to pull data from the web service and calculate the commission report using the above formula.
It's important to note that the commission calculation may also depend on your specific commission structure and rules. You may need to adjust the formula or include additional factors to accurately calculate the commission. It's recommended to work with a Microsoft partner or consultant who has experience with commission calculations in Business Central to ensure that you are following best practices and getting the most out of your commission report.
DAniele
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,151
Super User 2024 Season 2
#2
Martin Dráb
229,963
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (