AP Metrics

By in AP, Purchasing, SQL on Wednesday, September 24th, 2008

It’s often helpful to have an idea of the volume of transactions in a department. For example, it’s nice to know how many AP Vouchers and Checks get processed each week to understand the load on the AP department. This also provides a pretty objective benchmark for AP employees. To make this happen, we start with a SQL View that agregates the Open and Historical AP transactions by week.

This code uses a Friday to Thursday week with the expectation that checks are cut on Friday so the typical AP entry cutoff is end of day Thursday. If your week doesn’t work like that, the code is easy to change. After that we can query the view using a Smartlist, Excel, SQL Reporting Services or Crystal Reports. My preference has always been Excel because this data really lends itself to a graph.

The code to build the view looks like this:

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, Mdfusrid
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, Mdfusrid
from pm30200

You only have to run the view code once to build the view named Metrics_APVoucherCountWeek.

From there, a query on the count of AP Vouchers by user looks like this:

–SQL Query for APVoucherCount
–Use Weekenddate in where clause to limit range
–This is ALL AP Voucher transactions.
–Uses Metrics_APVoucherCountWeek View

Select WeekendDate, Mdfusrid as APUser, Count(VCHRNMBR) as VoucherCount
from Metrics_APVoucherCountWeek
–Exclude Payments
Where Doctype<>6
group by WeekendDate, mdfusrid

And the output looks like this:

AP Metrics Ouput Example

AP Metrics Ouput Example

You can see that it would be pretty easy to graph the voucher count per user per week. I have prebuilt AP queries available for AP transactions, AP payments and AP Transactions excluding PO related items. We’ve also got PO and Fixed Asset metrics available, all you have to do is ask.

This is my shameless inducement to get you to leave a comment. If you want more queries for the metrics, leave a comment on the blog with an email and I’ll get them to you. No, we’re not going to spam you to death if you ask for the metrics. We just want to know that people are paying attention.

2 Responses to “AP Metrics”

  1. Chad says:

    This is really great…Well done!

    Could you please emil me
    AP queries available for AP transactions, AP payments,AP Transactions excluding PO related items ,PO and Fixed Asset metrics

    Thanks

  2. Christian says:

    Thanks for this great AP metric, this is quite useful.

Leave a Reply

*
*