Dynamics GP 2015 and FRx

Today’s little project was upgrading my internal system to GP 2015, which all went quite smoothly. Unfortunately, I don’t have a domain, so I am not able to install and configure Management Reporter for my financial reporting. I still use FRx. But after upgrading to GP 2015, FRx would no longer connect to my company database. Instead, I received a lovely error referencing a utility dll that I was not able to locate.

After uninstalling FRx, removing all vestiges of it from my workstation, and rebooting, I did a fresh install and encountered the same problem, which — after some searching online for related issues– I traced to the common files folder at this path: C:\Program Files (x86)\Common Files\Microsoft Shared\Dexterity.  I renamed all the dlls in this path, did a fresh installation of FRx and discovered that one dll was created afresh: GPConn.dll.  The FRx one is dated 4/6/06. This is from the FRx674 build for GP10, which was the last full release of FRx. The new GPConn.dll created by the GP2015 installer has a date of 11/14/14.

FRx ran just fine and connected to my company database after recreating the original dll. So far I’ve had no problems running GP 2015 either, so I thought I would share this in case anyone else out there is still using FRx and wants to upgrade to GP 2015.

Leave a comment

Filed under Microsoft Dynamics

Fun with SSRS

It seems an increasing amount of my time is spent building reports in Smartlist Builder and SQL Reporting Services. I thought it might be fun to share a couple of SSRS reports I’ve been working on lately.

The first is a Customer Statement. If you use GP customer statement forms, you are probably aware of the limitations in modifying the standard report format. Even the Word template format is cumbersome to modify, so using SSRS to print your statements is a great alternative. You can make an SSRS format as simple or complicated as you like.

I usually begin the process of any SSRS report by building a SQL view containing all the necessary information. In the case of customer statements, there is an additional level of complexity if you want to add apply detail, and most of us do! In my case, we needed a report showing all the open invoices along with all applied open payments and any payments applied during the selected date range.  My customer wanted to be able to enter a date range and select which of their customers would receive statements.  The final product is shown below.

This is a statement from my own GP environment. I’ve removed customer details to protect the innocent, but when you open the report in designer, you will see the Customer ID field and the address block. 2014-07-06_1300

As you can see, it’s a pretty simple format. Once you open the RDL in SQL report builder, you can change the logo and make any formatting modifications you like.  Just note at the invoice detail is in the main report and the APPLY detail is contained in the sub report. If you add columns in either place, you will need to adjust the formatting so that everything lines up.

The SQL view I used can be downloaded here.  (Remember to right-click and save as!) In order to use the RDL I  have provided here, you will need to deploy this SQL view to your environment.  You will also need the SQL view behind the apply detail sub report here (thank you, Victoria Yudin!) and the associated RDL for the sub report here.

If you are familiar with deploying SSRS reports and SQL views, you shouldn’t have any trouble with these.  You will need to point to your GP datasource and ensure that the appropriate security is set to the SQL views and the newly deployed reports. TEST, TEST, TEST to make sure the report shows accurate data for you!

The real power of this particular statement format is that you can print them for any date range. You can print a customer statement from the beginning of time until the last month’s cutoff, or you can print a partial month. You can print statements for all customers or for a select few. I’ve added a comment field as a parameter to accommodate one of my customers who needed to be able to enter a dynamic comment to print on all statements in a print run.

One challenge I have found in SSRS reports in general is how multiple pages are rendered, so if you have long statements, make sure you test with several different scenarios, as you may need to adjust the format to get it perfect.

 

 


 

The second SSRS report I want to share is a simple sales document.  This one uses the actual GP tables SOP10100, SOP10106 (header comments) SOP10200, and SOP10202 (line item comments). If you require more customer billing details, you could easily link the RM00101 (customer master) or RM00102 (customer address master) as well. 2014-07-06_1428

Once again, I’ve made this pretty simple, but additional fields can be added easily. For end user ease of use, you could also write the necessary code to render SOPTYPE as a text field. For simplicity, I have left it as a number from 1-5 to represent QUOTE, ORDER, INVOICE, RETURN, and BACKORDER.

Also, additional parameters would make this more usable depending on a company’s business processes for printing or generating invoices. For example, you could add parameters for customer class, customer, city, state, priority code, etc.

Finally, for return document amounts and for fields such as markdown and trade discount, you may want to modify the expression to render these as negative numbers. This is very simple to do using the SSRS expression builder.

The RDL files for this basic SOP document are here (main doc) and here (line detail). Again – right-click and save as to download the RDLs. Otherwise, they will open as XML.

Again, deploying these is a matter of uploading them to your SQL Reporting Services environment and making the necessary changes to the datasources.

Once you’ve begun using SSRS for documents such as these, you begin to see the power of using the same format for historical documents. By simply making a change to the dataset, you could pull all historical invoices and define parameters to print by a date range, batch ID, etc. Posting a batch of invoices prior to printing is no longer a problem – simply use SSRS to print any and all based on your customer parameters.

Well, that’s all I have for now.  I hope you will have fun using these templates and share your own developments!

 

 

 

1 Comment

Filed under Microsoft Dynamics

September was a whirlwind month for me, and it was also a steep learning curve month. I love learning new things, but I also hope to spare some of you the frustrations I encountered while delivering these solutions.

First, let me state emphatically that the internet is a wonderful resource. Let me temper that by stating that it can also be very confusing when you are looking for specific information and only find snippets of code and overviews of how “it should work”.  I have a fondness for examples and complete end-to-end documentation, and because I’m not a programmer I often find it difficult to make the leap to a complete solution by piecing together bits of script from various sources.

AA and Integration Manager

One of my customers has begun to use Analytical Accounting for simple project tracking. Of course, there is plenty of documentation on how to set up AA and distribute transaction amounts, etc. The challenges begin when you need to import AA data and prepare reports containing the AA detail information.

So in the interest of time, I am going to share the KB articles that explain in depth how to set up AA integrations.  These were provided to me by Microsoft, and they worked like a charm. Just right-click and choose “save link as” to download the PDFs.

AA GL Transaction

AA Sales Transaction

After reviewing the AA GL Transaction document, I identified the area where I had gone wrong in building my integration originally, and it’s now working beautifully.  If you are having trouble building an AA integration, I highly recommend that you follow this document step by step to understand how it works.  I will also clarify that my customer distributes to multiple dimensions and codes, so I had to tweak my source file to accommodate that requirement.  The AssignID on each AA code distribution has to be unique for the amount being distributed, not for the dimension or the code. Below are the actual examples from my test transaction:

Header

***Now if I can just figure out how to dynamically assign the next journal entry number, this little project will be completed.  I hope one of you will provide me with a working example of the necessary scripts!***

AA and Management Reporter

One of the most critical components of any system is the output.  In fact, a lot of times when I’m discussing a customer’s needs, I will begin with “what do you want to accomplish by doing this?”  Normally in an accounting system, the ultimate goal of any data input is to view the information on a financial report.  Analytical Accounting information  is no different.  So it was with dismay that I realized the obstacles faced when trying to present these simple codes on an existing financial report.

First, my customers was using Dynamics GP10 and Frx.  When attempting to install the AA add-on for Frx, we discovered that it won’t run on a 64-bit server.  So we attempted to run it from a 32-bit workstation, but no luck was had there either.  I finally convinced the customer to move forward with their upgrade to GP2010 and Management Reporter, so we upgraded their databases and migrated their Frx reports to MR 20102.

I’m relatively new to Management Reporter, but I had some experience with the installation, configuration, and FRx migration process.  I now feel that I have mastered these steps,  thanks to some very helpful sessions at the  sold-out GP Technical Airlift in Fargo, which took place early in September.  By the way, if you are a partner or work for a partner, you should attend this event:  three days of technical information provided by the “in-the-know” folks at Microsoft.

With regard to the install and configuration, here is the link to the Microsoft download site.

Once I had Management Reporter configured and tested, it was a simple process to add a report to display the AA information.

Of course, much like the AA functionality in FRx, you have to be connected to the AA company in Management Reporter.  Beyond that, it’s a simple matter of adding the dimension codes in the row format like this:

When you double-click on the Link to Financial Dimensions, you will see the AA Dimensions as columns. Double-click on each one to define filters as needed.  In my example, I combined all expense types for each event on a distinct row, but you can use the dimension window to break out or combine codes as needed.

I’m sure I will be faced with combining the AA information with an existing financial report in the near future, so stay tuned for new adventures!

Leave a comment

September 29, 2012 · 8:19 am

WennSoft Troubleshooting for the Not So Faint of Heart

I have a couple of WennSoft customers in my fold, and they often pose some of the most challenging issues to resolve.  For those of you who are not familiar with WennSoft, you can find out more about their products here.  For those of you who are already – and reluctantly – aware of these add-ons, this post is for you.

GP service packs used to make me nervous, but they are almost effortless when compared to the issues I have encountered in applying WennSoft service packs.  Most recently, I took a customer from WennSoft Classic 2010 SP1 to WennSoft Signature 2010 R2 SP1.  This service pack contained 193 fixes to prior releases of the product and was compatible with GP 2010 build 1914.

I tried to anticipate (based on prior experience, of course) any obstacles that might come my way on a Saturday, when WennSoft support would not be available.  I requested and safely tucked away the new registration keys that I knew would be required; I requested additional information from WennSoft regarding any “gotchas” that might come my way and any additional downloads that might be needed;  I had the customer’s IT department download the appropriate files and place them on their server, and I double-checked the versions; I exported all modified reports for GP and WennSoft to package files; I backed up the GP code folder, etc. We planned a back-up schedule after close of business on Friday and I coordinated with IT to ensure that everything was ready.

After running the GP update Friday evening and verifying that it was error-free, I ran the WennSoft update utility.  As it was already late, I left that process running and went to bed.  Of course, in the morning I was greeted by a not-so-clear error message referencing a table that had updated sucessfully.  So after some research and pondering, I decided to give the update another shot.  It completed without error.  Yippee!

Unfortunately, my joy was short-lived.  I completed the update steps, took backups of the databases, verified that reports still worked, etc.  Then I notified the customer contact that the update was complete, so that she could do some testing before we updated clients.  This is all standard procedure, of course.  I had updated a single client and documented the steps so that the customer could complete the remaining workstations after testing.  I then moved on to deploying refreshable Excel reports, GP SRS reports and WennSoft SRS reports, and setting the necessary security for users to access them.

Monday morning we found out that the testing was not thorough enough. Users encountered errors when opening some windows, errors that indicated that tables had not been updated.  I’m sure you’ve seen this dreaded error message before: “Number of results columns doesnt match table definition.”  The customer contacted WennSoft directly for additional assistance and users continued to do their work in the system. We knew at this point that a rollback of 12 workstations and server would be quite time-consuming and that most of the system was operational, so my customer decided to trudge on.  A few hours later she received some guidance from WennSoft regarding a maintenance utility that would show us which tables did not update.  My response, of course, was to immediately run the maintenance utility, all the while wondering a) why does this not automatically run as a final step of the update process and b) why does it require a password?  Putting my frustration aside, I proceeded with the troubleshooting. There were 14 tables that were not updated. After another few hours of back-and-forth between my customer and WennSoft support, they offered to update the tables manually for a consulting fee. My customer asked if this was my recommended course of action, and I replied that I would do it for them, as I knew exactly what needed to be done to correct this error.

The answer was obviously to copy out the contents of these tables, rebuild the tables and associated stored procedures, then import the data back into the newly updated tables.  Fortunately, only six of the tables contained any records. The other ones were empty and could simply be dropped and recreated. I used the handy SQL maintenance window in GP to take care of this, beginning with the empty tables, then used the WennSoft utility to verify that the tables were at the correct version.  Three hours later, all the tables were rebuilt and the data back in place, and no further errors have been encountered in the system.

Harrowing experience? Yes! Rewarding? Absolutely!

1 Comment

Filed under Microsoft Dynamics

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.

1 Comment

Filed under Smartlist Builder

Getting the Most Out of Dynamics GP

Murphy’s Law states that if something can go wrong, it will. Perhaps the greatest antidote to stave off Murphy (or to lessen the consequences of a problem) is preparation. Q Factor strongly encourages all of its clients to perform regular practices to get the most out of Microsoft Dynamics GP. Dynamics GP takes well to customization, whether in creating accounting reports or allowing changes to the system, and we recommend two habits to help save a lot of headache for every company: making server notes whenever changes are made, and performing regular backups.

Whether your in-house accounting team is one or twenty, it is important that everyone who touches Dynamics GP make note on the server what changes and improvements they have made. The reason to make notations about any system modifications is so that, in case the “improvement” turns out to have unanticipated, or even has unwanted results, you are able to revert to a previous state. Often, you will not realize you made a mistake three steps ago until you’ve moved forward far enough to realize its impact. Without notes to understand what has been tweaked, it becomes a much bigger problem than it has to.

Additionally, if you have a team member leave the company, these notes allow for continuity. Many who are taking over the use of GP may not know the foundation of what comes with the software compared to the changes a team member introduced to accommodate your company’s business processes. Making notes can help your current and new team members understand a colleague’s stopping point. This also makes it far easier to have more members of your accounting team working with Dynamics GP, because they can follow one another’s improvements.

Regularly backing up GP-specific files, your team can be confident about protecting the efforts being made. Backups can also allow you to roll back to the prior state if a recent change interferes with the effectiveness of GP. Typically, the accounting team and the IT team are not working or communicating with each other effectively. If there is no one in the accounting department setting up backups of data, and no one has communicated the importance of such backups to IT, you can assume you’re not getting the backup you need.

Daily backups are critical to protecting your data. As your accounting team works to improve notation making habits, you should also be onboarding IT members to regularly back up the data that sits on the server.

Leave a comment

Filed under Microsoft Dynamics