Doing the “Impossible”

I’ve recently encountered several issues in which my new client was told by another partner that “it couldn’t be done”.  I would be the first to admit that there are many things that cannot be done in GP, but for the most part if it involves reporting or fixing data, it can indeed be done.  I thought I would share a couple of examples of these issues and how I resolved them with the help of other bloggers and GP experts around the globe.

Bank Reconciliations gone wrong

I’ve run into this one on a few occasions.  Somehow the cash account no longer ties to the checkbook balance. Usually this can be corrected by adjusting the checkbook balance with a deposit.  Negative or positive deposits without receipts can be entered in Dynamics GP.  A deposit only affects the checkbook balance, so the GL is not affected when the deposit is posted.  Of course, at times the solution is not this simple.  Perhaps the cash account is wrong, the checkbook’s current balance is not correct, or someone just reconciled to the wrong date.  In this case, it is necessary to “unwind” the reconciliation information and re-reconcile the checkbook.

Fortunately, there is a consultant out there who has already solved this problem.  Doug Pitcher was kind enough to show us the necessary scripts to undo a bad bank rec.  By identifying the checkbook, the reconcile audit number, and knowing the last good reconcile date and amount, it’s relatively simple to release all the reconciled transactions for a given period and allow the user to redo the work.  Now this isn’t typically something that an end user would want to do alone, but the information is available for those of us unafraid to attempt the “impossible”.  When I used Doug’s steps, everything went flawlessly, but after running through them I also had to correct the checkbook’s current balance field in the CM00100 table before the bank reconciliation would tie to the cash account.

Receivables Historical Aged Summary Trial Balance in Excel

This is one of those “duh” issues that a consultant encounters. It seems like something that should just be available in GP, but because of the intricacies of Report Writer, such things are inevitably a challenge.  The aging process for historical receivables occurs during the process of printing this report, so the data results are actually stored in a temp table that is unavailable anywhere else.  Essentially, the only way to get the results into Excel is to format the report in such a way that it can be printed to a tab- or comma-delimited file.

I have found some attempts to build SQL views for Trial Balance reports, most notably by Victoria Yudin, but these are typically for open A/R and not historical. Ideally you would build a SQL view, then present it to the customer using Smartlist Builder or Excel Reports or SQL Reporting Services. In fact, the Historical Aged Trial Balance is available in the Dynamics GP 2010  SRS Reports library.  Unfortunately, in this case it was not an option, so I moved ahead with the report modification.  During my search, I did locate Steve Reese  who had already built this report in detail and summary.  He published the detail version but not the summary, so I requested that from him. Of course, I had already made the necessary modifications for my customer by the time I received the package file from Steve, but I loaded his format in my test environment and works great. Steve retained the headings and parameters in his version, whereas my customer just wanted the raw data. My version is available here for download. Just right-click here and choose save link as.  Also, to be complete in my development efforts, I did modify the Receivables Detailed HATB in SRS to create a summary version.  This RDL is also available for download by right-clicking here and choosing save link as.  Please note, this is a SQL 2008R2 format.

******

Just a quick update: Ian was kind enough to share his multicurrency version of the GP RM HATB Detail report.  This report was built for originating currency only.  If you would like a copy of this, it is available for download by right-clicking here and choosing save link as.  Please test this prior to deployment, as your multicurrency reporting needs may differ from Ian’s.