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.
16 responses to “Doing the “Impossible””
Excellent blog. Have you read the new best seller 25 Laws for Doing the Impossible? You may enjoy the book.
No – I’ll have to check that out. I do enjoy a challenge!
The Receivables Detailed HATB in SRS has a shared connection called dsGP10RM. Can you please provide more info for this connection? Do we simply need to acces the company DB ? Thank you.
You should find that connection under the pre-built data connections in the SRS report library, but you can use any company connection as well.
Thank you, I was able to make the connection. Now I am getting syntax errors from the semantic queries. I am not too familiar with this type of query, do I need to do a configuration or turn a switch on. The message seems to come from SQL Server. Thank you again. Denis
Please disregard my previous reply. Everything works well now. My users want to export historical detailed statements to Excel. I will be able to build a detailed report based on this one. This will work perfectly. Thank you again!
when I download the rdl it comes over as an xml and I doing something incorrectly?
I don’t suppose there is a multicurrency version of this available?
I have not run across a multicurrency version. If you construct one, please share!
Wow, I am impressed with the RDL summary report. Kudos for getting something like this done. I have looked everywhere on the internet for SQL code but no one seems to be able to get it done, including me.
I do have one question: when I run a historical report, say 6 months into the past, I can’t get the aging buckets to show correctly. Although the balances for each customer are correct, the aging buckets are not correct. What could I be doing wrong?
Have you run the aging routine? Under tools>>routines>>sales>>aging. This needs to be run in order to drop the amounts into the proper buckets.
Yes, I even backed dated that process to the end of month I am interested in. The confusing part is that my historical aging under reports works fine, but the SQL reporting method does not work. I have since found out that both your modified report (summary) and the standard detailed report show the buckets the same (which are not correct)
I assume this is because the bucket information comes from the customer summary table, which only stores the current bucket information based on the aging. In order to get a true aging by bucket, you would probably have to build the code to calculate the bucket summary based on the selected records at runtime.
OK, so when you use the same report, and look back into history, your buckets are not correct either? I just wanted to confirm……..
Also, I thought the SQL reports were based on the same logic as the reports built into Dynamics GP under the reports area. Those reports get the buckets correct back through time even w/o running the routines for the aging.
ya, back dated 🙂
I’m not sure. I would have to dig into the reports and examine the bucket amounts.