Similar to Bank Reconciliation, every company has bills to pay, so this is for all companies.

There are two pain points: the inability to easily see which invoices and/or vendors are on hold and which credit memos/returns or prepayments need to be applied.

Our goal is to create a simple dashboard that easily shows what needs to be applied and what is on hold, all in one report. Monitoring this report each day will allow users to spotlight the documents that need attention first.

For this report, we will use a simple PivotTable report in Microsoft Excel.

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 VIEW view_BI_PM_Open AS
SELECT VM.VNDCLSID AS [Vendor Class], OP.VCHRNMBR AS [Voucher Number], OP.VENDORID AS [Vendor ID], VM.VENDNAME AS [Vendor Name],
CASE OP.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Credit Memo'
WHEN 6 THEN 'Payment'
WHEN 7 THEN 'Schedule'
END AS [Document Type], OP.DOCDATE AS [Document Date], OP.DOCNUMBR AS [Document Number],
CASE OP.DOCTYPE
WHEN 5 THEN - CURTRXAM
WHEN 6 THEN - CURTRXAM
ELSE CURTRXAM
END AS [Current Amount], CASE VM.HOLD WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS [Vendhor Hold],
CASE OP.HOLD
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS [Transaction Hold], OP.DUEDATE AS [Due Date], OP.PORDNMBR AS [PO Number],
OP.TRXDSCRN AS Description
FROM dbo.PM20000 AS OP
INNER JOIN dbo.PM00200 AS VM ON OP.VENDORID = VM.VENDORID
WHERE (OP.VOIDED = 0)
AND (OP.CURTRXAM <> 0)
GO 
GRANT SELECT ON view_BI_PM_Open TO DYNGRP

Non-technical matter (in case you're not a techie!)

Let's build a report!

Let's prepare and connect to the data.

We performed a mock-up of what we wanted in Excel and have included a screenshot here:

Let's make a connection to the GP data:

1. Open a blank workbook in Excel, and 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:
   
2. Enter your Server name and your network or AD login credentials. Then, click on Next.
   
3. Select your database from the drop-down list, select the view, and click on Finish. Please note that the views, and then tables, (in this window of Excel) display in alphabetical order; so a view that starts with view will appear before a table that starts with A. Now, select view_BI_PM_Open:
   
4. Click on Finish.
5. On the Import Data window, select PivotTable Report. Make sure that you are putting the data in the Existing worksheet option in the =$A$1 cell. Then, click on OK.
   

Let's build the first PivotTable report:

1. The PivotTable canvas will appear with the PivotTable Fields list on the right-hand side. Drag the Vendor Name, Document Type, and Document Number fields down to the ROWS area:
   
2. 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. Now, select Show in Tabular Form:
   
3. Once again, 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. Now, select Repeat All Item Labels:
   
4. On the Design tab, under PIVOTTABLE TOOLS, choose Subtotals in the Layout section of the ribbon. Now, select Do Not Show Subtotals:
   
5. In the PivotTable Fields list, drag Current Amount to the VALUES area:
   
6. In the PivotTable Fields list, in the VALUES area, click on the drop-down list for the Sum of Current Amount field. Now, select Value Field Settings:
   
7. In the Value Field Settings window that opens, make sure that Sum is selected as the Summarize value field by option. Click on Number Format and the Format Cells window will open. Change Category to Currency. Select how you would like your payments and credits to display, and then click on OK for each window to close:
   

Let's add slicers:

1. Let's add slicers, so that we can easily see various versions of the data with just a few mouse clicks. On the ANALYZE tab, under PIVOTTABLE TOOLS, choose Insert Slicer in the Filter section of the ribbon:
   
2. In the Insert Slicers window, select the check box for Due Date, Transaction Hold, Vendor Hold, and Vendor Class. Then, click on OK:
   
3. Arrange and resize the slicers according to where and how you want them to appear.

Tip

For unapplied documents that reduce the balance to appear, select the due date of 1/1/1900 (that is, payments, credit memos, and returns).

The finishing touches are as follows:

1. Add a logo, change the PivotTable colors (under the Design tab), and perhaps the report title.
2. To refresh the data from GP, click on Refresh All (under the Data tab).

This Excel report shows all Open documents with a balance. This means ANY invoice, return, credit memo, payment, and so on, with an unapplied balance. This report is also used to reveal only those invoices or vendors that have been placed on hold. Finally, this report reveals credit memos, returns, and/or (pre) payments that have not yet been applied (or fully applied) to an invoice. This will allow the AP staff to fully utilize GP features, keeping the financial data current, and still make sure they are not overpaying or forgetting to pay something.