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:
Tip
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:
Tip
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:
- Open a blank workbook in Excel; from the menu bar, choose DATA. In the Get External Data portion of the ribbon, choose From Other Sources. Then, choose From SQL Server as the source.
- Enter your Server name and network or AD login credentials. Then, click on Next.
- Select your database from the drop-down list. Select the new view we just created, VIEW_BI_EmployeeDependents, and then click on Finish:
- On the Import Data window, select PivotTable Report. Make sure that you are putting the data in the Existing worksheet option in the =Sheet!$A$1 cell. We are not leaving a blank row now, but we can always insert a blank row later. Then click on OK:
- The PivotTable canvas will appear. In the PivotTable Fields list on the right-hand side, drag the Customer, Doc_Number, and Salesperson fields down to the ROWS area:
- In the PivotTable Fields list on the right-hand side, drag the Commission_Amount field down to the VALUES area:
- Click anywhere on the PivotTable and new menu options will appear called PIVOTTABLE TOOLS. On the DESIGN tab under PIVOTTABLE TOOLS, choose Report Layout in the Layout section of the ribbon. Then, select Show in Tabular Form:
- Click anywhere on the PivotTable and new menu options will appear called PIVOTTABLE TOOLS. On the DESIGN tab under PIVOTTABLE TOOLS, choose Subtotals in the Layout section of the ribbon. Then, select Do Not Show Subtotals:
- On the PivotTable Field list, click on the drop-down list for the Sum of Commission_Amount value, and then select Value Field Settings:
- The Value Field Settings window will open. Select Number Format, and then set the format for this value. By setting the format here, the formatting will remain even if the PivotTable is refreshed. Then, click on OK on each window to close:
Let's add slicers and timeline, so we can easily see various versions of the data with just a couple of mouse clicks:
- Click anywhere on the PivotTable and new menu options will appear called PIVOTTABLE TOOLS. On the ANALYZE tab under PIVOTTABLE TOOLS, choose Insert Slicer in the Filter section of the ribbon:
- In the Insert Slicers window, select the check boxes for Status and Transferred. Then, click on OK:
Status indicates whether the document is considered Open or History. Transferred indicates whether this document has been included on the Transfer Commission sales routine.
- Click anywhere on the PivotTable and new menu options will appear called PIVOTTABLE TOOLS. On the ANALYZE tab under PIVOTTABLE TOOLS, choose Insert Timeline in the Filter section of the ribbon:
- In the Insert Timelines window, select the check box for Doc_Date. Then click on OK:
- Move the Timeline and Slicers (drag-and-drop) around, so your report is readable and functional:
Other ways to view this report:
- This PivotTable allows for so many options. Simply dragging Salesperson in the PivotTable Field list from below Doc_Number to above Customer changes the report entirely. Before this step, we were looking at Customer and the commission for each document:
Now, we have a report that shows which Salesperson, Customer, and Doc_Numbers have commission:
- Using the Slicers, we can view commissions for only those documents with a status of Open. If you run the Paid Transaction Removal sales routine on a daily basis, you will see only those invoices with an open balance:
- Collapsing an entire field or column will take the report from detail to summary (right-click on a specific salesperson and you'll be able to collapse the column):
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:
