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.

Advertisement

Dynamics GP Add-Ons Your Company Should Consider

Third party add-ons for Dynamics GP can be used to increase the efficacy of the program. There are a variety of add-ons most companies should consider that will improve the efficiency of searching, analysis, report making, and accessing SQL. I typically recommend the following add-ons: SmartList Builder, SmartFill, and SQL Reporting. Over the next few months, I’ll talk about these add-ons in more detail. Today, I want to focus on SmartListBuilder.

SmartList Builder is an add-on that makes the standard SmartList in GP work better for many companies. With SmartList Builder you are able to custom-build a SmartList with external or GP data. If you have a special report you need to create, such as sales history, or sales transactions, or purchasing, that is not a standard GP SmartList, then SmartList Builder will let you create it and then dump data out to an Excel spreadsheet where you can manipulate it, analyze it, or create additional reports.

The advantage, of course, of being able to dump data into Excel is the ability to use Excel to run hypothetical scenarios, create graphs that can be imported to PowerPoint for presentations, and otherwise use the data from Dynamics GP for decision making and planning. SmartList Builder also allows you to export data to Word or print a report directly from the add-on.

The biggest advantage of SmartList Builder is that it saves time and it makes repetitive tasks easy to do. The report writer that comes with GP is time-consuming and not at all intuitive. For example, let’s say you need to generate a list in Excel every month of all the items you sold for a certain period of time. The basic list could be generated by Dynamics GP, but if you also need to see any special fields, like line item comments for each item sold, then SmartList Builder lets you build a more robust report, and do it very quickly. SmartList Builder also allows users to generate reports using a combination of Dynamics data and other SQL data.

The biggest drawback of SmartList Builder is that you cannot dump out an AP Aging or an AR Aging report from SmartLists without a lot of extra manipulation to get it into Excel. There is an AP/AR Aging report in GP that dumps to a GP Report file, but it’s not compatible with Excel. However, SmartList Builder will let you build a SQL view in the database with all information calculated for you and then, using SmartList Builder, you can pull SQL view data.

SmartList Builder makes monthly reporting faster, better, and easier, and it makes Dynamics GP more versatile by allowing users to have access to information they may not otherwise have access to. It provides a user-friendly interface for making that data usable by people outside of IT and accounting.

Dynamics GP as a Single Source

More and more companies are integrating their various business information systems in an attempt to streamline IT and accounting processes.  One of my customers recently decided to use Dynamics GP as their primary database for storing Login information for all their disparate systems.

Before I outline our approach to meeting their requirements, let me explain how they were doing this.  Their recruiting and hiring process is fast-paced, to say the least. This company is opening new retail stores in new markets all the time, so their recruiters are on the road collecting applications daily and setting up new hire meetings every couple of weeks.  In order to set up a new employee, the recruiter would fill out all the basic information in a spreadsheet, which was then passed on to the payroll department to create an employee record in Dynamics GP.  The payroll department would then send a spreadsheet to the IT department with the new hires’ information.  IT would set up the necessary logins and passwords in each system based on another spreadsheet summarizing the different systems required by each job title, then communicate these back (again via a spreadsheet) to the recruiting department.  The final step for recruiting was to prepare (manually, using Word) a login sheet for each new hire to be distributed at their new hire orientation.  In addition to this “workflow”, there were additional data elements that were often not captured or only partially captured, resulting in inaccurate information or worse: erroneous payroll transactions.

Overall, a cumbersome process relying on several people to remember to fill out all the necessary information and send a notification by e-mail that the shared spreadsheet was ready or attach and e-mail a new spreadsheet to someone in another department. Additionally there was frequently a need for cross-communication regarding missing pieces of information for some new hires.  The primary pain point to be addressed was setting the appropriate commission flag in their point of sale system to ensure that employees received commissions when applicable.

The first step to address in developing their new process was removing the back-and-forth nature of creating employee information in their various systems. In order to handle the initial setup, we utilized Extender to create two new windows in Dynamics GP.  The first window is attached to the Payroll Position Setup window and lists all of the user logins required by position code along with a commissionable flag.  The second window is attached to the Employee Maintenance window and captures each employee’s logins for the various systems.  Once this was complete, we published each of these windows as SQL Views using Extender views, then used Smartlist Builder to attach the two views and create a smartlist that IT could use to generate a spreadsheet of new hires along with their required logins defined by position code. Finally, we created a second smartlist that recruiting can use to create a mail merge in Microsoft Word for their new hire login sheets.

We changed the business process to remove the payroll department from the initial setup of new hires.  The recruiting department now enters the employee ID and SSN in the employee maintenance window, along with the department and position code.  The IT department merely checks the smartlist on a daily basis to determine if new hires have been entered by the recruiting department (if the logins fields are blank, this indicates a new employee record). If records are found, they export the list to Excel, and as the various logins are defined IT completes this spreadsheet and uses an Extender import to populate the logins window in Dynamics GP.  Recruiting uses their login sheet mail merge smartlist to quickly generate the forms they need for orientations. The Payroll department completes an employee record after recruiting confirms the employee has attended an orientation and filled out all the necessary paperwork (this is also tracked using  Extender windows on the employee maintenance window).

Now that I’ve written all of that, it looks very complicated, but it really simplifies and streamlines the process of adding new hires and their various logins into the numerous systems. In addition, the commissionable flag is now determined at the position code level instead of on an employee-by-employee basis, so there is much less room for error in setting up the point of sale system. In addition, because all of the logins are now stored in a single location, the IT department is able to use the SQL views to push login information into various systems automatically instead of having to set up each one individually. A similar process (utilizing the same Extender windows and SQL views) is used by the IT and Payroll departments to communicate changes in status (which often result in a change to the commissionable flag and login requirements) and terminations.