Manually Enable Jet Reports on a Terminal Server

Jet ReportsI’ve done some work with a client recently who has been a long time user of Jet Reports; they’ve recently merged with another company and needed some of the Jet Reports amended and deployed for the new companies which were added to Dynamics GP. The infrastructure and implementation decisions used by Jet Reports were made well before my involvement and, in some cases, they go against the recommendations of the vendor.

There are two key issues which we encountered rolling Jet Reports out to the new companies. The client does not use the Jet Service Tier which allows you to roll out settings to users from a central service and they have Jet Reports available to users as a published app rather than shared desktop.

The former item means a setup of connections on each client and the latter means this is not a supported configuration ( as mentioned in this article on the Jet Reports knowledge base Jet Reports can only be supported on a shared desktop).

This is something which needs to be discussed with the client, as they should really be operating using a supported method. However, the short term issue is how to get Jet reports available to users.

Usually you’d do this by using the Enable Jet Add-in app on the Windows start menu or launching Jet using the Jet Reports app. In this case, as a published app, the end-user does not have access to the Enable Jet Add-in app and the IT department was resistant to publishing the Jet Reports app.

Instead we were able to get Jet Reports enabled in Excel by adding the add-in manually to Excel.

We did this by launching Excel from the Start menu and selecting (File » options). In the Options window select the Add-ins tab and click the GoManage Excel Add-ins:

Excel Options window

Continue reading “Manually Enable Jet Reports on a Terminal Server”

Error Logging into Jet Hub : “IDX20803: Unable to obtain configuration from: ‘System.String’.”

Jet HubI did an install of Jet Reports recently on new demo VM and found that I had a problem logging into the Jet Hub. When I tried to log in, I was always getting this error:

Error message when trying to log into Jet Hub

Error

IDX20803: Unable to obtain configuration from: 'System.String'.

I did some debugging myself and got nowhere in working out the cause of the problem as everything looked fine. An online search found an article on the
Jet Support Knowledge Base which discussed a similar error.

Stepping through the recommended troubleshooting steps I found that running IISRESET in an administrator command prompt resolved the problem. Running this command is part of the installation steps for jet Hub, so it looks like I overlooked running it during the install.

One to make sure I remember to do on the next install of Jet Repots that I do.

Error With Microsoft Dynamics GP Historical Company Name in Jet Reports

Jet ReportsI’ve worked with Jet Reports against Microsoft Dynamics GP with a few clients over the last couple of years (as well as writing a book on using it with Microsoft Dynamics GP). One recent client I was working with developed some reports against the live companies, but we had a problem when trying to include some data from historic companies (after a merger, new companies were created and the old ones marked as historic).

I was able to repeat the problem on my demo environment. When oa test company was selected the Options tab showed a #VALUE! error:

Error when selected a test company

Continue reading “Error With Microsoft Dynamics GP Historical Company Name in Jet Reports”

Recent ISC Software Webinar: Business Intelligence with Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Business Intelligence with Microsoft Dynamics GP. In this webinar, we covered how business intelligence can be used with Microsoft Dynamics GP. If you want to catch up on this, or any other, webinar, you can do so here.

  1. Introduction
  2. What is Business Intelligence?
  3. What is Jet Analytics?
  4. Reporting options with Jet Analytics
  5. What is Power BI?
  6. Reporting options with Power BI
  7. Conclusion

Introduction ^

I think this webinar was the first one in which we didn’t even open Microsoft Dynamics GP. This because this webinar focused on how business intelligence can be used with Microsoft Dynamics GP. We focused on two business intelligence products which can be used to quickly build required dashboards and reporting for business intelligence.

The two products we looked at are complimentary and can be used both together or independently. They are Jet Analytics and PowerBI

Continue reading “Recent ISC Software Webinar: Business Intelligence with Microsoft Dynamics GP”

Remember to Reactivate Jet Reports When User Made Designer

Jet ReportsI’ve been doing a lot of work with a client on Jet Reports recently. While we were doing a training session, we created some new users and changed some from being ordinary users to being designers via the Jet Licensing portal. When the first user launched Excel, they found they were still an ordinary user without access to design mode.

They logged out and back in without the license changing at all.

The key to resolving this, was for that user to reactivate Jet Reports using the Activate from Jet Service Tier option on the Jet ribbon to to pick up the changed license.

Recent ISC Software Webinar: Automation in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Automation in Microsoft Dynamics GP. In this webinar, we covered how automation can be used in Microsoft Dynamics GP to improve efficiencies and accuracy of data. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and cover the highlights of how automation can be used in Microsoft Dynamics GP to improve efficiencies and improve data accuracy:

  1. Introduction
  2. Integration
  3. Scanning
  4. Approval
  5. Posting
  6. Reporting
  7. Conclusion

Introduction ^

Where possible in this webinar I highlighted standard, or Microsoft supplied, features or additional products where they are available. However, in many cases the standard functionality does not allow for full automation. This is an intentional design choice made when Microsoft Dynamics GP was first created back in the md-90s. The company who created Great Plains, the original name of Dynamics GP, was intended from the very beginning to be extensible with the intention that there be a thriving third-party marketplace for add-ons.

This is the current situation; the core Dynamics GP system has strong core financials and distribution modules, but wider functionality is provided by third party (Independent Software Vendors (ISVs) who have a variety of add-ons and complimentary products which provide the functionality required or automating processes. In each of the areas, there are usually a number of products available from several vendors, but I have selected one in each area. usually an add-on which I have used with several clients across a number of years and which has received positive reviews.

Before implementing one of the solutions, I’d recommend reviewing the functionality it includes, the functionality of competing products and making your own decision about which will best fit your requirements.

Continue reading “Recent ISC Software Webinar: Automation in Microsoft Dynamics GP”

Jet Service Tier Windows Service Won’t Start

Jet ReportsI’ve been doing some work with a client recently to implement Jet Reports. We installed the Jet Service Tier to allow the management of data sources in a central repository. However, after installation we were unable to start the service and received an error each time:

Error 1067: The process terminated unexpectedly.

After doing some investigation, we found that what wasn’t being reported to us on the server was that there was part of the installation (which was executing some PowerShell) caught by a malware monitoring application, called Apex One, and blocked. We had an exception added for powershell.exe and were then able to install the Jet Service Tier again, after which the service was successfully started automatically.

Jet Hub’s Supported Excel Functions

Jet HubI’ve been doing an increasing amount of work with Jet Reports and have come across two Excel functions which do not work with Jet Hub; they work fine in a Jet Report in Excel, but are apparently not supported in the Jet Hub.

The two I’ve encountered problems with are:

  • CONCAT isn’t supported, but the older CONCATENATE function is supported.
  • RATE; this was actually used by a client in a formula and we then discovered it wasn’t supported when uploading the report to jet Hub when I was showing him how to use it. We’ll need to find a way to work around this unsupported function.

There is a list of supported Excel functions in Jet Hub available from insight Software; I need to start referring to this list more often when creating reports and make sure clients do the same.

Jet Reports Snippet: Calculate last day of last month

Jet ReportsI’ve been doing some work with a client on Jet Reports recently and one of the items which we discussed, was running a report for the previous month without the user needing to set the date each time; this would allow them to use Jet Hub and schedule the report.

A few days ago, I posted an article on getting the last day of the month using an Excel formula. To get the date for use in Jet Reports, we can use the same formula, but can wrap it in an NP function to evaluate the formula:

=NP("Eval","=EOMONTH(Today(),-1)")

As you can see, the entire formula, which would usually be in the cell, has been included in the NP function wrapped in double quotes. Wrapping it in the NP function will ensure that the formula is calculated and the date available before other Jet functions run.

SQL Stored Procedure to remove Jet Reports’ Report Run History

Jet ReportsIf you’re using Jet Hub with Jet Reports, then when you run a report, the report run is stored in the Jet Services database;through time, this report run history can grow quite large if you either have large reports or are generating lots of reports; combine these together and the history can grow to potentially massive sizes.

Jet Hub does not, unfortunately, include an automated clear-down routine for the report run history, but, fortunately, the history is only stored in a single table.

The below stored procedure can be created against the Jet Services database and scheduled to run with SQL Server Agent; the highlighted parameter at the top can be changed to alter the number of months for which history should be kept:

IF OBJECT_ID (N'usp_AZRCRV_DeleteJetReportsReportRuns', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteJetReportsReportRuns
GO

CREATE PROCEDURE dbo.usp_AZRCRV_DeleteJetReportsReportRuns
	@iAge INTEGER = 12
AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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). */
DELETE FROM ReportRuns WHERE Runtime < DATEADD(month, -@iAge, GETDATE()) GO

As always, test the script before using against a live system and ensure you have a good backup before