This report will track commissions for all customer documents in the Sales Series; it will also track whether the document is Open or History. By "Open", we mean the invoice or credit memo/return has been posted, and remains available to be displayed on the customer statement. "History" means the document has been posted, fully applied.
This report is for any company that tracks commissions on invoices in GP (Sales Order Processing, invoicing, and receivables management), particularly those companies that pay commissions when payment is received.
GP does a great job providing the amount of commission to be paid. It does not, however, show the invoices with commission amounts for those that have not been paid in full. Salespeople keep track of their commission, and they want to know that your numbers match their numbers.
We will be using the PivotTable feature of Excel. We will provide the salespeople with a list of all outstanding invoices for which they have outstanding commissions. Providing them with details (which you can do with a PivotTable; they do not always summarize) will allow them to balance their numbers to yours, with the possible side benefit of them stepping in to help collect when necessary.
There are two parts to these steps. The first part is technical, containing steps that involve work in SQL Server. The second part is the actual building of the report. You may choose to obtain assistance with the technical part, especially if you do not have access to SQL Server.
Create the following view in SQL Server, after you make a backup of course:
This first part involves working in SQL Server. If you are not experienced in working in Microsoft SQL Server, request your IT department or Microsoft GP partner to assist you. You may corrupt your data and completely break your system if you are not careful. You should also have the SQL Server system administrator password.
For directions on creating a view, follow the technical matters step for the first report in Chapter 2, Business Intelligence for the General Ledger.
CREATE VIEW view_BI_Commissions AS SELECT CD.DOCNUMBR AS Doc_Number, CASE RK.DCSTATUS WHEN 1 THEN 'Work' WHEN 2 THEN 'Open' ELSE 'History' END AS Status, CD.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer, CD.SLPRSNID AS Salesperson_ID, RTRIM(SM.SLPRSNFN) + ' ' + RTRIM(SM.SPRSNSLN) AS Salesperson, CD.SLSAMNT AS Sales_Amount, CASE CD.COMMPAID WHEN 1 THEN 'Yes' ELSE 'No' END AS Transferred, CD.SALSTERR AS Sales_Territory, CD.COMDLRAM AS Commission_Amount, RK.DOCDATE AS Doc_Date FROM dbo.RM10501 AS CD INNER JOIN dbo.RM00401 AS RK ON CD.DOCNUMBR = RK.DOCNUMBR INNER JOIN dbo.RM00101 AS CM ON CD.CUSTNMBR = CM.CUSTNMBR INNER JOIN dbo.RM00301 AS SM ON CD.SLPRSNID = SM.SLPRSNID INNER JOIN dbo.RM20101 AS RMT ON CD.CUSTNMBR = RMT.CUSTNMBR WHERE (RMT.VOIDSTTS = 0) UNION SELECT CD.DOCNUMBR AS Doc_Number, CASE RK.DCSTATUS WHEN 1 THEN 'Work' WHEN 2 THEN 'Open' ELSE 'History' END AS Status, CD.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer, CD.SLPRSNID AS Salesperson_ID, RTRIM(SM.SLPRSNFN) + ' ' + RTRIM(SM.SPRSNSLN) AS Salesperson, CD.SLSAMNT AS Sales_Amount, CASE CD.COMMPAID WHEN 1 THEN 'Yes' ELSE 'No' END AS Transferred, CD.SALSTERR AS Sales_Territory, CD.COMDLRAM AS Commission_Amount, RK.DOCDATE AS Doc_Date FROM dbo.RM30501 AS CD INNER JOIN dbo.RM00401 AS RK ON CD.DOCNUMBR = RK.DOCNUMBR INNER JOIN dbo.RM00101 AS CM ON CD.CUSTNMBR = CM.CUSTNMBR INNER JOIN dbo.RM00301 AS SM ON CD.SLPRSNID = SM.SLPRSNID INNER JOIN dbo.RM30101 AS RMT ON CD.CUSTNMBR = RMT.CUSTNMBR WHERE (RMT.VOIDSTTS = 0) GO GRANT SELECT ON view_BI_Commissions TO DYNGRP
One last time so you get the whole warning; know what you want. We've already completed this step and have included a screenshot here:
As always, we must know what we want our end result to look like before we start. Draw it on paper or perform a mock-up in Excel. Starting with the end in mind will save you many, many hours of time. We'll say this over and over again because it's that important! The preceding screenshot is what we want our report to look like.
Let's make a connection to the GP data:
Let's add slicers and timeline, so we can easily see various versions of the data with just a couple of mouse clicks:
Status indicates whether the document is considered Open or History. Transferred indicates whether this document has been included on the Transfer Commission sales routine.
Other ways to view this report:
Now, we have a report that shows which Salesperson, Customer, and Doc_Numbers have commission:
From a summary report, you can open or expand one record at a time by clicking on the + sign on the left-hand side of the record. This will allow you to have a hybrid of summary and details, all in the same report: