You, Me and Dynamics GP
I’ve got some SQL code that generates metrics about Dynamics GP transactions. I don’t think that I’ve done a good job of highlighting these in the past so I’m going to sprinkle them in to some of the Weekly Dynamics. This week it’s AP metrics.
This code came from a situation where we changed AP entry personnel. After a couple of months the new individual was complaining that they couldn’t keep up. We were growing but we weren’t growing that much so I wrote some SQL code to show how many AP transactions were being processed per week, since we cut checks weekly. We wanted to see:
- How many AP vouchers were being entered per week
- How many checks were cut per week
- How many AP vouchers were entered EXCLUDING P.O. based invoices that were handled by someone else.
We cut checks on Thursdays so for us, Friday was the start of a week for payables.The code adjusts for that. Simply change “Friday” to you week beginning day and adjust the other days accordingly. A generic version of the code is included below and works in Fabrikam. It also works with all of the data already in Dynamics GP. This version uses DocDate which worked for us. It could also be changed to use posting date. The goal is not absolute data but change over time. A little imprecision due to the occasional old DocDate doesn’t change the trend enough to matter.
The code already in the SQL Scripts area and can be downloaded directly from this link.
Oh that employee, she wasn’t keeping up. AP volume had been steady. The amounts had been going up but the actual transaction volume was the same. The size of an AP voucher is generally irrelevant to how long it takes to enter after it has been approved. We also found (as expected) that the last and first weeks of each month had the heaviest AP entry volume.
-----------------------------------------------------
--The core is a view to collect all of the data
--AP Voucher Count per Week
--Create View to count AP Vouchers by Week
--View is needed to include data from both open and history tables with union
--Case statement used to select end of week
--Assumes end of week is Friday. Renaming days in order is fastest way to change this.
--To make Sunday the end of the week, put Sunday in the first line, then Monday, etc.
Create View Metrics_APVoucherCountWeek as
--Get Open AP
Select
--Get end of week
Case
when datename(weekday,docdate) = 'Friday' then Docdate
when datename(weekday,docdate) = 'Saturday' then dateadd(Day,6,docdate)
when datename(weekday,docdate) = 'Sunday' then dateadd(Day,5,docdate)
when datename(weekday,docdate) = 'Monday' then dateadd(Day,4,docdate)
when datename(weekday,docdate) = 'Tuesday' then dateadd(Day,3,docdate)
when datename(weekday,docdate) = 'Wednesday' then dateadd(Day,2,docdate)
when datename(weekday,docdate) = 'Thursday' then dateadd(Day,1,docdate)
else '01/01/1900' end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc
from pm20000
--Get History AP
Union
Select
Case
when datename(weekday,docdate) = 'Friday' then Docdate
when datename(weekday,docdate) = 'Saturday' then dateadd(Day,6,docdate)
when datename(weekday,docdate) = 'Sunday' then dateadd(Day,5,docdate)
when datename(weekday,docdate) = 'Monday' then dateadd(Day,4,docdate)
when datename(weekday,docdate) = 'Tuesday' then dateadd(Day,3,docdate)
when datename(weekday,docdate) = 'Wednesday' then dateadd(Day,2,docdate)
when datename(weekday,docdate) = 'Thursday' then dateadd(Day,1,docdate)
else '01/01/1900' end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc
from pm30200
Go
------------------------------------------------------------------------------------------
The first query then counts all Dynamics GP Vouchers per week
------------------------------------------------------------------------------------------
--SQL Query for APVoucherCount
--Use Weekenddate in where clause to limit range
--This is ALL AP Voucher transactions.
--Uses Metrics_APVoucherCountWeek View
Select WeekendDate, Count(VCHRNMBR) as AllVoucherCount
from Metrics_APVoucherCountWeek
--Exclude Payments
Where Doctype<>6
group by WeekendDate
-----------------------------------------------------------------------------------------
--The second query counts checks cut.
-----------------------------------------------------------------------------------------
--AP Check Count per Week
--Uses Metrics_APVoucherCountWeek View
--Use WeekEndDate to limit range
--This is checks cut during a particular week
Select WeekendDate, Count(VCHRNMBR) as ChecksCut
from Metrics_APVoucherCountWeek
--Include Only payments
Where Doctype=6
group by WeekendDate
-----------------------------------------------------------------------------------------
--The third query counts vouchers and excludes PO/Inventory match created transactions.
-----------------------------------------------------------------------------------------
--AP voucher count per Week Excluding PO Transactions
--Uses Metrics_APVoucherCountWeek View
--This is AP Voucher transactions but it excludes PO based transactions since that work is usually done
--by a different department and its entry is transparent to the AP group.
Select WeekendDate, Count(VCHRNMBR) as VoucherCountxPO
from Metrics_APVoucherCountWeek
--Include Only payments
Where Doctype<>6 and bchsourc<>'rcvg Trx Ivc'
group by WeekendDate
