Poor man’s dashboard?

A client asked me where they could find out some metric-type information from the last couple of years’ processing in Dynamics GP, such as the number of A/P invoices processed, number of cheques processed etc.; and the same for A/R if possible.

Simple question, not so simple answer. I admit it never occurred to me, this information isn’t particularly easy to get to in GP in aggregate form, or I’m drawing a complete blank on where to get it. There are many inquiry windows but most with this type of thing are at a customer level, not company level.

So, I wrote a couple of quick smartlists – one for A/P, one for A/R, and I thought I’d write it out as this client likely isn’t the first one to ask this question! I’ll call it a poor man’s dashboard… it’s simple, and for those who haven’t delved into dashboards yet or graphs or KPIs, this may suffice for some simple information about what you’re processing in Dynamics GP.

Overview

First, I’ll be describing the A/P Metrics smartlist but the same concept will apply for A/R or other modules. I also chose to exclude voided documents altogether using a restriction, but I could have easily handled that within the calculated fields or had a column on its own if # of voids was important. And, lastly, I ignore WORK tables and focused only on OPEN and HIST tables to get posted transaction counts.

I chose to use Smartlist Builder from eOne to build these using their Summary smartlist feature, as it didn’t require a SQL view or anything special to get it done. I don’t actually have any active clients who use Smartlist Designer, so I can’t tell you if this exact approach can be done via that out-of-the-box tool or not.

The specific approach was to pull data from the transaction tables themselves, and summarize by year and by type or grouped by type of documents. The result would be one record (row) of data per year, with columns for the count of invoices, credit notes, payments etc.  This can easily be customized further, to the limit of your imagination, as long as you can build a calculated field for what you want to capture.

There are some summary views already in the database but they aren’t summarized in the exact same way my client requested, so I didn’t use those.

I’m not walking through the literal how-to-create-a-smartlist steps, but hitting the high level key points on this one.

Tables to use

I started with the PM Transaction OPEN File table first. After selecting that table, go back into “edit” the table to select the Matched Tables feature. Smartlist Builder’s (SLB) matched tables feature allows you to effectively combine related tables like work-open-history versions of transaction tables, without having to create multiple smartlists or create SQL views just to combine the data.

In this case, I wanted the PM Paid Transaction History File, to pull in all paid/historical A/P documents. I named it “PM Trx History” and clicked Save.

Metrics Smartlist Example SLB 1A

Notice that on that table, I deselected every field that was set to Display. By default, every table you add, SLB assumes you want every field displayed but not defaulting on the “*” smartlist default favourite. In this case, I’m using the data to summarize by calculated fields I’m going to create next, so I don’t actually want to display *any* fields from the transaction tables.

Metrics Smartlist Example SLB 1

Calculated Fields

I created 4 calculated fields in my case, but you can do more or less, depending on how granular you want to break out the counted data or group the data. In my screen shot above, you see the “Calculated Fields” like it’s a table. That appears as soon as you have one or more calculated fields. I took the screen shots after the smartlist was built, hence some screen shots appear out of order in how I am describing this!

Grouping field for Year

In my case, the client simply wanted metrics by calendar (same as their fiscal) year. I wanted to create a field to group by year, and specifically the Year of the GL Posting Date. In the A/P transaction tables, this field is called Posting Date. In the A/R transaction table, it’s more clearly labelled GL Posting Date. I created a “String” field for this value.

While I’m on the subject of dates, a brief tangent. If you are confused by the various date fields in Dynamics GP, take a look at Steve Endow’s post from last month, referencing a post on Victoria Yudin’s site on what dates mean what. Be aware: “posted date” and “posting date” are not the same thing and can’t be used interchangeably!

I’m using a simple CASE statement – fancy for “if this, then that”. The basic format is:

CASE

WHEN <fieldname equals something> THEN <use this value>

ELSE <use this value>

END

In my case, this is a highly simplified version of a CASE statement. You can have many “when” statements and you are not limited to “equals” as you’ll see in a couple of my screen shots in the next calculated fields, that’s just the simple example above. It uses the SQL syntax so google it and you will see what your options are to see how fancy you can get!

Here is my formula:

Metrics Smartlist Example SLB 3

Now, in my screenshot, you’ll notice an odd piece in my formula to catch scenarios where the “Posting Date” field in my client’s data happened to be “1900-01-01” for some transactions when they first converted to GP. I don’t know the history of why, and didn’t want to figure it out right at that moment, so I trapped that particular situation in my formula. You may find you don’t need that! In a perfect world, I could simply just make this formula “YEAR({PM Transaction OPEN File:Posting Date})” and that would do the trick, without a CASE statement.

My formula says “if the Year of the Posting Date is empty (GP empty dates = 1/1/1900), then pull the Year from the Document Date; otherwise, use the Year from the Posting Date”. For the handful of outliers in the client data, using the year from the Document Date was fine.

Count fields – Invoices, Credit Notes, and Payments

This is the Invoices formula, one of 3 different calculated fields.  I’m using it as an example where you don’t have to have your CASE statement be limited to something equal to one thing. For the count of invoices processed, I made the following formula based on the Document Type.

My formula says “if the Document Type is in the following list (“invoice”, “finance charge”, “miscellaneous charge”), then count it (1); otherwise don’t count it (0)”. The “in” is an alternative option to equals, where you specify a list. Since these three document types happen to be sequential numbers, I could also have used < 4 or something like that.

My client didn’t want columns for every one of these things, I’m counting any “positive” charge on the vendor as an invoice. This will count all 3 document types as an invoice.

Metrics Smartlist Example SLB 4

My Credit Notes field is Document Type “in (5,6)” to capture both returns and credit notes. My Payments formula is Document Type = 6. That covers the 6 A/P document types.

Fields to Display & Default

Once the calculated fields are all created, I want to mark them all to be Displayed and Default.

Metrics Smartlist Example SLB 2

Create your Summary

On the Options menu, click on Summary to create a summary smartlist. Tick the box “Summary Smartlist”, then click on the Calculated Fields and change the Summary Type using my screen shot as an example.

I want to “group by” Year, which means show one row/record per year. I want to “sum” each of the other fields, as I used 1’s and 0’s to easily “count”. I could also use count but I tend to prefer sum.

At this point if you wanted to group by other fields, such as Vendor Class or something, you would need to make that field set to Display and Default, and then change its Summary Type to Group By as well.

Metrics Smartlist Example SLB 5

Final Result

Here’s an example of what the final smartlist should look like if all goes well.

I don’t have a screen shot but I did add a Restriction to only count non-voided transactions.

In order to validate the data, before I made this available to the client, I put a temporary restriction in to only summarize a specific vendor that has lots of transactions over several years. I used the Vendor Yearly inquiry window to cross check some of the totals to make sure the data was making sense, then removed that restriction so it pulled all vendors again. It seamlessly combined open and historical transactions using the matched tables feature which made it really quick and simple to produce this!

Blog - Metrics Smartlist Example

Hope this helps someone!

(originally posted on www.kuntzconsulting.ca, and migrated to this site in October 2017)

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top