Implementing Jet Reports: Series Index

Jet ReportsBack in May 2016, Microsoft and Jet Reports announced a new Microsoft Excel-based reporting product called Jet Express. I was aware of Jet Reports before then, but the announcement brought them back to my attention as well as highlighting that the future of Management Reporter is now somewhat limited.

I’ve spent a bit of time over the last few months taking a look at the different versions of Jet Reports and figured it was time to do a series of blog posts. Over the coming posts, I am going to take a look at the implementation of each of the three versions of Jet Reports; I’ll also show the sample reports available from Jet Reports themselves, but will not cover report design in this series of posts. I’ll probably do some posts on this in the future.

I’ve created a separate series for each of three versions of Jet Reports; the indexes below will populate as each post goes live.

Continue reading “Implementing Jet Reports: Series Index”

OneDrive Feature: Files On-Demand

OneDriveI’ve only recently realised this feature had been introduced to OneDrive; I’m not sure if I’m the only one and everyone else knew, but figured I’d share.

Back in the early days of SkyDrive you could have files stored online which didn’t take up space on the hard disk; unfortunately, this feature was removed and any files in synced folders would take up space on the disk.

From some research (Windows Insider anouncement) it appears that as of the Windows 10 Creators Update files on demand is back. You can tell at a glance if files are available locally or only when you’re online by looking at the status column:

OneDrive files

The cloud icon is used when files are available only when online and the green mark when the files are always available; the latter means that the file is on the hard disk.

Any file which is cloud only, will become available when it is opened as it is downloaded; any files created on the device will remain available.

Continue reading “OneDrive Feature: Files On-Demand”

Web Client & Account Level Security

Microsoft Dynamics GPThe web client was a god addition to Microsoft Dynamics GP and I do like the HTML 5 version introduced in Microsoft Dynamics GP 2016 RTM. While we have a few clients using it, and some only use the web client, we’ve not had any clients using both the web client and Account Level Security.

MVP Jen Kuntz is different; she’s currently looking at the web client for the company she works with and has come across an interesting problem.

The short version is printing the standard report version of the Trial Balance doesn’t work in the web client when Account Level Security is enabled:

Trial Balance

Related to this problem, is another problem whereby user’s don’t have access to any accounts and so can’t do anything around transaction entry, enquiries or reports.

For full details of both of these issues, read Jen’s blog.

As Jen notes, both of these issues have written up as outstanding quality issues:

  • Bug #1 is Trial Balance report does not work when signing into Web Client with Windows Authentication if Account Security is enabled.
  • Bug #2 is Web Client users not able to post if Account Security is turned on. The user receives the error “Access denied/Account Missing”

The bad news is there is no timeline for fixing them, and from the sounds of it, it’ because there aren’t a lot of customers reporting this issue back to Microsoft. A “product suggestion” has been added to the new Ideas site for this which Jen encourages you to vote for:

Bug #1 aka product suggestion #1 link is here.

Bug #2 aka product suggestion #2 link is here.

POWERUSER Removed From ‘sa’; No Other User With Security Access

Microsoft Dynamics GPOne of the project managers was doing some testing for a development project recently and accidentally changed the security for the ‘sa’ account; they removed the ‘POWERUSER’ role (which gives global access to Microsoft Dynamics GP. In its place, they assigned the AP CLERK role.

What made this a major issue, was that the development system only had one company and this left the system with no users with access to the security windows.

They made the mistake by selecting the sa account instead of the sam account. Unfortunately, it was only after logging out as sa and back in as sam that they realised what they had done.

Fortunately, this isn’t actually too complext to fix, although it does require some SQL.

The sa account needed to be added back into the Security Assignment User Role (SY10500) table. I used a very simple script for fixing the development system, but have then tidied it up a little to post here.

This script adds POWERUSER back to the sa account for all companies and needs to be run against the system database (typically called DYNAMICS):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
INSERT INTO SY10500
	(USERID,CMPANYID,SECURITYROLEID)
--VALUES
	(
	SELECT
		'sa',CMPANYID,'POWERUSER'
	FROM
		SY01500 AS ['Company Master']
	WHERE
		(
		SELECT
			COUNT(*)
		FROM
			SY10500 AS ['Security Assignment User Role']
		WHERE
			['Security Assignment User Role'].CMPANYID = ['Company Master'].CMPANYID
		AND
			['Security Assignment User Role'].USERID = 'sa'
		) = 0
	)
GO

Before running, please make sure you are happy with what the script will do and have a good backup of your system database.

Expiration Dates on Exchange Rates

Microsoft Dynamics GPI’ve been doing some work with a US-based company as they implement Microsoft Dynamics GP in Europe for one of their subsidiaries (we do a fair bit of this type of work for expertise with VAT and Intrastat). One of the issues they asked for assistance with was a problem they had while importing transactions.

All of the transactions which were in the functional currency, imported without a problem, but all of those in other currencies failed.

The cause of the problem was actually quite a simple one, and one I have seen before with other clients. On the Multicurrency Exchange Rate Maintenance window, there is an Expiration date field:

Multicurrency Exchange Rate Maintenance

Continue reading “Expiration Dates on Exchange Rates”

How to Compare Files in Notepad++

Notepad++This is a little off topic from my usual posts on Microsoft Dynamics GP, although it is related.

I recently did some work for a client developing a new EFT File Format and needed to verify that the file form,at loaded into live was the same as that in test which had passed UAT (a few versions had been floating around).

I figured the easiest way was to export both files and then use Notepad++ to compare the files; I was sure that I’d done this before, but I couldn’t immediately see the option to do so.

I then remembered that it was a plugin which I’d installed which added the ability. To add plugins to Notepad++, launch the plugin Manager (Plugins >> Plugin Manager >> Show Plugin Manager):

Notepad++ Plugin menu

Continue reading “How to Compare Files in Notepad++”

Configuring Workflow to use Gmail for Email Notifications

Microsoft Dynamics GPI’ve done a lot of work around the Microsoft Dynamics GP Workflow module and have configured Email Notifications to work with numerous different SMTP servers. One recent one we configured was Gmail.

This was not actually as straightforward as configuring other SMTP servers as the details Google publish are not accurate in that the port they list for SSL is not the correct one.

The settings required are below:

Workflow Setup

Continue reading “Configuring Workflow to use Gmail for Email Notifications”

Fix Windows 10 Lock Screen Not Changing

WindowsI have my PCs all set to share settings via OneDrive; I like this as it means I have a unifed theme everywhere, but when a problem occurs, it occurs everywhere.

his particular issue, is that I use Windows Spotlight for images on the lock screen and it became stuck on one image a while ago. After recently installing a new PC and having them same problem there as on other machines, I finally decided to fix the issue.

This was a fairly straightforward fix for me, but others report various fixes not working.

I found the fix which worked for me on Driver Easy and am reposting here, so I can easily find it again in future if necessary.

To fix the issue by re-registering Windows Spotlight, perform the following steps:

Use Run As Administrator to launch Command Prompt, click Yes when prompted by UAC (User Account Control) and type the following command:

dism /online /cleanup-image /restorehealth

Press Enter and wait for the command to run; this might take a few minutes to complete:

Command Prompt: dism /online /cleanup-image /restorehealthdism /online /cleanup-image /restorehealth

In the Command Prompt window type the following:

sfc /scannow

Hit enter and wait for the command to complete:

Command Prompt: sfc /scannowsfc /scannow

Either type powershell or launch a PowerShell window and type the following:

Get-AppXPackage -AllUsers |Where-Object {$_.InstallLocation -like “*SystemApps*”} | Foreach {Add-AppxPackage -DisableDevelopmentMode -Register “$($_.InstallLocation)\AppXManifest.xml”}

Hit enter and wait for the PowerShell command to finish (this may take a while) and generate several errors which can be ignored:

PowerShellGet-AppXPackage -AllUsers |Where-Object {$_.InstallLocation -like “*SystemApps*”} | Foreach {Add-AppxPackage -DisableDevelopmentMode -Register “$($_.InstallLocation)\AppXManifest.xml”}

I’ve found I need to wait a few minutes for the fix to take effect, but I don’t need to do anything else before the Windows Spotlight image starts to change.

Error Configuring Web Services for Microsoft Dynamics GP 2018

Microsoft Dynamics GPI’ve seen this error at two different clients now when configuring Web Services for Microsoft Dynamics GP 2018:

A loader exception has occurred.
Loader Errors:
- System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'taRequesterWSDynamicsOnlineConfiguration1' in the database.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Dynamics.InstallData.SqlObjectLoader.DoSqlFromResource(String resourceName)
   at Microsoft.Dynamics.GP.GPSystemWebServiceObjectLoader.PerformActionOnEConnectStoredProcedures(Action action)
   at Microsoft.Dynamics.InstallData.Loader.Process(String[] args)
ClientConnectionId:04c704d2-d12c-40f3-9ccc-1444c4b529d9
Error Number:2714,State:3,Class:16

Both clients have previously had the Web Services implemented so that they could use Email Actions for Workflow approval. The first client got the error while we were doing the initial installation and confguration of the Web Services for Microsoft Dynamics GP 2018 and the second got them when we did a redeploy of the Web Services on the test system after refreshing the databases, but hadn’t received the error during the initial installation and configuration.

Continue reading “Error Configuring Web Services for Microsoft Dynamics GP 2018”

Microsoft Dynamics GP 2013 R2 Out of Mainstream Support

Microsoft Dynamics GPMicrosoft Dynamics GP 2013 came out of mainstream support and into extended support as of 1oth April 2018; end of mainstream support means you will no longer get statutory updates such as the addition of the Invoice Receipt Date in the January 2017 hotfix which applied to the UK or year-end tax updates for the US and Canada.

I always recommend to client to look to upgrade every two to three years; the last few years have seen many improvements and additional new functionality added to Microsoft Dynamics GP (2013, 2013 R2, 2015, 2015 R2, 2016, 2016 R2 and 2018) as well as bug fixes.

By not upgrading regularly, you not only miss out on all of these fixes and new functionality, but you also make your eventual upgrade longer and more complex due to the number of versions which need to be upgraded through.

You can review the product lifecycle on the Microsoft Lifecycle Policy – Microsoft Dynamics GP page.