Top Categories:
Dynamics GP
ClassicPress

Jet Reports with Microsoft Dynamics GP book available

Microsoft Dynamics GPI have a new book available now called Jet Reports with Microsoft Dynamics GP; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics GP for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools and Jet Hub.

Jet Reports with Microsoft Dynamics GP by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site or from Amazon (for $5 more):

If you buy the ebook from me directly, the coupon code JetWithGPLaunch will get you 20% off the price of this book (offer available until the end of July 2020).

Microsoft Dynamics GP running at an appalling slow speed

Microsoft Dynamics GPOn a recent project we encountered an issue with the test ssytem running remarkably slowly in many areas. While logging in took longer than it should have, even opening the SmartList window was taking 45 seconds; when a SmartList favourite was selected, each column header and field was drawn slowly enough that it could be read before the next one appeared.

Posting a Payables batch with 50 invoices would take more than 50 minutes (I’m not sure how long it took as that was when I disconnected my session and let it run to completion by itself at the end of the day).

We installed a client directly onto the SQL Server for testing purposes and found that the speed was what we would expect to see, rather than the snails pace the users were seeing.

One of the network team noticed that the traffic speed going over the network was very ;pw compared to what should have been possible and lower than we’d expect to see. The entire environment was a virtualised one and all machines were configured with virtual 10Gbit network cards; the solution was to remove the virtual 10Gbit ethernet cards and replace with 1Gbit.

After this change was made on both the SQL Server and terminal servers, users saw the performance of Microsoft Dynamics GP increase across the board. SmartList would open in three seconds, a favourite would return rows of data far faster than could be read and batch posting was rapid.

I’m not sure if there was a peculiarity elsewhere in the system contributing to the slowness, but the only change made at that time was the replacement of the virtual ethernet cards.

Switch off SOP/Invoicing warning in Microsoft Dynamics GP

Microsoft Dynamics GPI often end up creating test, demo or upgrade requirements and have a number of scripts I run against them to make changes. The script below can be used to change the settings for the message displayed when both SOP and Invoicing are both installed.

The script is set up to leave the message enabled for Invoicing, but switch it off for Sales Order Processing (the comments include the setting for switching the other way).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
UPDATE IVC40100 -- Invoicing Setup (IVC40100) SET IVCDSTCK = 0x01000040 -- set to 0x00000040 to switch off warning GO UPDATE SOP40100 -- Sales Setup (SOP40100) SET IVCDSTCK = 0x00000000 -- set to 0x01000000 to switch on warning GO

How to Set the Tab Sequence Order in Microsoft Dynamics GP Modifier

Microsoft Dynamics GPWhile created a new version of a customised window for a client (the old customised version was causing problems for submission to Workflow) I needed to change the tab order of the fields on the window; I’d removed quite a few fields and changed the positions of the remaining.

I couldn’t remember how to do this (I last remember doing this in Visual Studio where you can select a field and change the tab order directly) so a quick online search found me a blog from David Musgrave, originally on the old Developing for Dynamics GP blog and now on his Winthrop DC blog.

The short version is to click Layout » Set Tab Sequence and then tab through the fields, double-clicking a field to change the order:

Set Modifier tab order

When doing this make sure you tab through fields, even after double-clicking to change the order, or odd tab ordering will result.

David’s blog post has a lot more detail available on preparatory steps, steps to accomplish the reordering as well as cautions on what to be careful.

Run Task Manager as Administrator

WindowsI was deploying a new version of a Microsoft Dynamics GP Dexterity modification for a client recently and ran into a problem caused by a user who had disconnected their RDP session to the server instead of logging off; this had left a lock on the dictionary file as they had also not closed Microsoft Dynamics GP.

The server logon requires individual logons with MFA enabled, but my user is not an administrator on the server. As I was doing the deployment out of hours, I was not able to call the user and ask them to log off, but instead needed to kill their session and close the client. With my user account being an ordinary one, I didn’t have permissions to kill the session, but I did have access to a domain admin account which did have th permissions.

The following command will let you specify the user account with which to start Task Manager allowing you to run it under an admin account when yours isn’t:

runas /user:{username} taskmgr

After entering the command, you need to enter the password for the admin account you used.

New book on Jet Reports with Microsoft Dynamics GP available

Microsoft Dynamics GPI have a new book available now called Jet Reports with Microsoft Dynamics GP; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics GP for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools and Jet Hub.

Jet Reports with Microsoft Dynamics GP by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site or from Amazon (for $5 more):

The book is structured into these chapters:

  • Chapter 1, Introduction to Jet Reports, introduces Jet Reports and explains what type of reporting for which it can be used.
  • Chapter 2, Implementing Jet Reports, covers the installation of Jet Reports including the Jet Service Tier and Jet Hub.
  • Chapter 3, Configuring Jet Reports, shows how to configure Jet Reports once it has been installed.
  • Chapter 4, Introducing the Excel Add-in, introduces the Excel Add-in and the features available.
  • Chapter 5, Report Structure, shows how reports can be structured to make maintaining them easier.
  • Chapter 6, Using the functions in the Excel Add-in, shows how each of the four Jet Functions can be used.
  • Chapter 7, Creating and using an Options page, covers the creation and use of an options page in reports.
  • Chapter 8, Create a Summary Trial Balance, steps through the creation of a Summary Trial Balance report.
  • Chapter 9, Create a Detailed Trial Balance, shows how to make the Summary TB from the previous chapter into a Detailed report.
  • Chapter 10, Create a Balance Sheet, covers the creation of a formatted Balance Sheet.
  • Chapter 11, Create an Income Statement, covers the creation of an Income Statement and how to make it an intercompany/consolidated version.
  • Chapter 12, Create a Summary Payables Aged Trial Balance, shows how to create a Summary Payables Trial Balance report.
  • Chapter 13, Create a Purchase Order by Vendor Report, covers the creation of an operational report on purchase orders.
  • Chapter 14, Report Creation Tools, introduces the tools which can be used to make creating reports easier.
  • Chapter 15, Using Jet Hub, shows how users can access Jet Reports via web access and without a local install of Jet Reports.

If you buy the ebook from me directly, the coupon code JetWithGPLaunch will get you 20% off the price of this book (offer available until the end of July 2020).

Exchange Rates inserted by eConnect integrations

Microsoft Dynamics GPI recently assisted with a support call logged by a client following the introduction of an RM integration created using SmartConnect.

The client enters exchange rates at the start of the month, but following the integration being deployed, extra exchange rates were being inserted into the exchange rate table. This caused problems as the rate from the external system varied more often than the rates entered, and required, in Microsoft Dynamics GP.

The investigation started from the integration to see if there were any settings in it which could be causing this issue, but none seemed relevant. The only currency fields being set was the Currency ID and the Exchange Rate.

After this I started taking a look into the eConnect stored procedures on top of which SmartConnect runs. After digging down a couple of levels I discovered that taMCExchangeRate has a section which inserts the exchange rate passed in if it is not in the Exchange Rate table; this is regardless of the setting in Multicurrency Setup (Financial area page » Setup » Multicurrency) which were set to allow rates to be used without being added to the exchange rate table.

The client was running Microsoft Dynamics GP 2013 SP2 and my investigation was confirmed on a 2019 October Release environment so this is a long standing issue which is not yet resolved; I have confirmed with Microsoft that they are aware of this issue and it is logged with the development team for resolution in a future version.

In the meantime, I have deployed customised versions of two stored procedures to the client so that they do not have the exchange rates being inserted into the exchange rate table.

Microsoft Dynamics GP payment batch no longer visible in Process Remittance

Microsoft Dynamics GPI’ve been working on a project to convert some bank accounts to making payments via EFT through Microsoft Dynamics GP using the payment run process for a client. On one of these accounts, the user ran into problems with the remittance and could not longer see the batch in the Process Remittance window [gpmnu]Purchasing area page » Transactions » Process Remittance(); the batch did show up in the lookup on the Payables Batches window showing a status of Remittance Printing, so it should have been visible.

I did some exploring of the data and reset the Marked to Post column from 1 to 0 in Batch Activity (SY00800) and changed the Batch Status from 140 (Remittance Printing) to 130 (Remittance Report) in PA Report Posting Definitions Master (SY00500).

After this the batch was available for selection in the Process Remittance window and the payment run could be completed.

Rollback Microsoft Office to a previous version

Microsoft OfficeIn the last post, I mentioned uninstalling and reinstalling an old version of Microsoft Office to stop Microsoft Dynamics GP crashing when sending emails using MAPI. However, you don;t need to uninstall Office to roll back the version.

There is a command you can run to roll Office back to a previous version. You can find out the version numbers for Office here and then run this command at an escalated Adminstrator prompt to roll back the version of Office (change the highlighted section to the version number to which you want to roll back):

"C:\Program Files\Common Files\Microsoft Shared\ClickToRun\OfficeC2RClient.exe" /update user updatetoversion=16.0.12730.20236

Once the roll back of Office is complete, launch one of the products and select File » Office Account and then click Update Options » Disable Updates to stop Office being automatically updated:

Stop Microsoft Office automatically updating

With this done, Microsoft Office is rolled back to an old version and will not automatically update.

here are other approaches discussed online, but I have tested the above and it worked correctly for me where the others didn’t.

Microsoft Dynamics GP crashes sending emails using MAPI

Microsoft Dynamics GPOn my Microsoft Dynamics GP demo environment I have Office Professional Plus 2019 installed and Dynamics GP configured for MAPI integration for the sending of emails (I sometimes have my demo environment used when I don’t have an Internet connection so have a hMaiLServer installed for emails instead of relying on the company Exchange). I got caught doing a demo last week when sending an email, Microsoft Dynamics GP crashed whenever I did anything involving integration with email.

I first encountered the crashes when trying to email a creditor remittance, but soon found that even trying to open the Email Settings window did the same thing; in fact any window which tries to connect with Outlook, caused Dynamics GP to crash. I did some investigation and repairs of Office, as well as rolling back a version or two to see if that resolved the problem. Unfortunately it didn’t resolve the problem so I opted to remove and reinstall Microsoft Office from the original media which I know had worked. And it worked again; the rollbacks should have worked, but I may ot have rolled back far enough.

As this old version worked fine, I have disabled automatic updates of Office to make sure I don’t have problems again.