Dynamics Community Forum Thread Details

How to report on Dynamcis GP growth & usage - transaction counts?

I'm a DBA relatively new to GP 10 (SP2) support. I've inherited from a previous DBA a monthly report (Excel) on the general usage and growth of our GP installation. The audience of the report is IT management and executive management/Board of Directors The report includes database size statistics which of course is useful to track growth from an IT capacity perspective. The report also shows AP and GL transaction counts. I don't get why executive management would care about transaction accounts as long as the accounting department provides them the appropriate financial reports. But I've been asked to update this existing report. However, I can't find the queries that the previous DBA used to derive transaction counts. Can anyone help me identify the AP and GL tables/fields to use to accurately count transactions? Once I identify the source tables, should I just count the total number of records for the month, or should I filter out certain records or transaction types to avoid redundancy or duplication (like reversing transactions)? Are transaction counts the best metric to report on GP usage and growth? Any feedback would be most appreciated.

I can see how the number of transactions would be a useful metric. Often when we talk to prospects about upgrading to Dynamics GP or to existing customers about customizations or add-ons, volume of transactions is a good first indicator of whether certain options or directions make sense from an ROI perspective. Also, if your AP and GL transaction count was pretty steady for 2 years and all of a sudden it doubles, and you are not expecting it to, that may be an indication that something is going wrong in the accounting department.

However, without knowing how the data you currently have is being gathered, it may be very difficult to recreate it exactly. I can think of a lot of different ways to get this data, and they will all come out with different results. For example, GL count could be the number of unique GL entires (each of which could have any number of lines) or it could be the total number of GL entry lines. Recurring transactions will have the same journal entry number, but different dates, so do you count them as one transaction or one per unique date? Also to consider - do you take voided transactions into account? Just because a transaction is voided, doesn't mean it didn't take someone time to enter... What about year end closing entries? If you're counting GL lines, then the year end closing entry will have a line for every GL account number used, but it will be only one transaction. It's also entirely possible that in January of 2011 GL entries are posted for December of 2010, as well as the rest of 2010, if adjustments are needed. So if you're looking at transaction dates, you may see your numbers keep changing for prior months...unless you look at the date the transaction was actually posted or entered.

I think you see where I am going with this, there are enough questions where it would be difficult to just say - count the unique journal entries in tables GL10000, GL20000, and GL30000 - which may be the answer. So...if you absolutely cannot find the code that was previously used (or if you are being asked to change it), I would sit down with the people that look at this report and are asking for changes and get a new set of requirements from them.

All of that said, if a pretty 'simple' count is ok, here are some queries you can use:

General Ledger:

select count(*) Unique_GL_Trx from
(select distinct JRNENTRY, TRXDATE from GL10000
union
select distinct JRNENTRY, TRXDATE from GL20000
union
select distinct JRNENTRY, TRXDATE from GL30000) a

Accounts Payable:

select count(*) AP_Trx from PM00400

Depending on the exact requirements you may need different tables, these pages can help find them:

GL Tables: http://victoriayudin.com/gp-tables/gl-tables/

AP (PM) Tables: http://victoriayudin.com/gp-tables/pm-tables/

Quick Links

Leaderboard