Sales by Sales Person custom report - By invoice and all details

Posted By mdiah over 8 years ago

I need to create a custom report which is Similar to “Sales by Sales Person” so I can pay my sales reps quarterly. Now the current list doesn’t meet my requirements.

When I select “Sales by Sales Person” I want it to pull up Sales by the Selected Rep for the Specified Period (Normally every 3 months)

Now when the report is generated I need to to pull the up following information in this pattern:

CUSTOMER – INVOICE # - DATE OF ORDER – DATE PAID (If Paid, If not maked UNPAID) – TAXES (G.S.T, H.S.T, ETC… Seperated not together) – Net Total of Sale – Freight Cost – Total of all those fields.

At the bottom it should do a total of each field mentioned above

- Total of Taxes (Separate not grouped)
- Total of Net Sales
- Total of Freight
- Total of Totals

Who can help me achieve this?

0 RandyW over 8 years ago

If I had to do this only once, I would use Excel - autofilter, VLookup, if statements and pivot tables.

But to do it regularly... I've seen a commission report available from a third party vendor - it may have been 'Swagman Systems'

A SQL query is quite 'messy' since there are tables in several modules that have to be worked out to calculate this. The salesperson is stored in the invoice table, but the payments are in the A/R table, there can be multiple payments, so there isn't just one 'paid date', there can be multiple invoices to different customers with the same invoice number,... that sort of thing.

A query could work back from each payment paid in the quarter, to the invoices that were paid, to the details, including the salesperson on each invoice.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 mdiah over 8 years ago in reply to RandyW

I looked for Swagman Systems couldn't find anything
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 RandyW over 8 years ago in reply to mdiah

I found www.swagsys.com - and that was by using Bing.

www.swagsys.com/.../

"Commissions and Sales Utility – calculate commission using custom definable criteria such as Total Sales, Gross Profit Margin, and Net Profit Share."

I am in no way affiliated with Swagman Systems. I spoke to someone there about 8 years ago regarding calculating commissions only on paid invoices, since at the time I couldn't see any way to do it in Sage 50.

So I exported the aged receivables detail with all the history, then stuck in an 'If' statement to calculate whether an invoice had a payment, then copied / pasted as text to 'freeze' the 'paid' column and used AutoFilter to get a list of paid invoices, and to filter a date range.

THEN I could work from that list to figure out who to pay commissions to.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 50 Canada

Sales by Sales Person custom report - By invoice and all details

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING