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 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). */
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

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).

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).

Upgrading to Jet 2019: Upgrade Jet Reports

Jet ReportsThis post is part of the series on Upgrading to Jet 2019.

Earlier in this series, I did an upgrade of Jet Analytics; in this post, I am going to upgrade Jet Reports (formerly known as Jet Professional, and before that Jet Essentials).

To upgrade Jet Reports, download Jet Reports 2019:

Download Jet Global Software

Continue reading “Upgrading to Jet 2019: Upgrade Jet Reports”

Upgrading to Jet 2019: What’s New In Jet Reports 2019

Jet ReportsThis post is part of the series on Upgrading to Jet 2019.

As with Jet Analytics, there have been a number of improvements to Jet Reports:

  • Improved Performance with XLL Multi-threaded Add-in – The Jet Excel add-in is now based on the Excel’s XLL format. This provides several benefits – including a performance boost, with some reports running up to 5 times faster.
  • Business Central Data Source Connection – Dynamics 365 Business Central users can connect Jet Reports directly to their Business Central data base. Each user can use their Office 365 credentials to make their connection.
  • Simulate Jet Reports Financials within your Jet Reports Excel add-in – For those Business Central customers and partners needing to support both Jet Reports and Jet Reports Financials, a new simulation mode has been added to Jet Reports to allow you to use the full Excel add-in as through it were an installation of Jet Reports Financials.
  • Updated Report Wizard – Continuing with efforts to bring all the Jet add-in features in line with a consistent look as well as providing intuitive and easy to use interactions, the interface for the Report Wizard tool has been updated.
  • Update Notification when a new version of Jet Reports is available – A new notification button has been added to the Jet ribbon to let users know when a new version of the Excel add-in is available.
  • +VALUES automatically added to reports – When running a new Jet report for the first time, in addition to Auto+Hide, +Values will now be added to cell A1. This has been a popular request in our ideas portal.
  • The Jet Services Database is Backed Up during Upgrade – The Jet Reports installation program now includes the option to backup the existing Jet Service Tier database prior to updating or replacing it as part of the installation process.
  • The Jet Hub has received a new look and new features – The Jet Hub features a new look (along with a new name) and the new App Switcher allows you to quickly navigate between the applications in the Jet Hub.
  • Updates Emails when Adding Users to the Jet Hub – When adding new users in the Jet Hub, you can now provide an email address for each user. There is also an option to edit the email address for existing users. This information is used for sending invites to log into the Jet Hub and for notifications in the Jet Budgets application
  • Assign Data Source Permissions to User and Groups within the Jet Hub – After defining data sources within the Jet Administration Console, you can now give users and groups permissions to those data sources from the Admin section of the Jet Hub
  • Support for Excel’s WORKDAY.INTL function – Excel’s WORKDAY.INTL function is now supported in the Jet reports that you run within the Jet Hub.

The upgrade of Jet Reports is much simpler than the upgrade of Jet Analytics and I will cover it in the next post.

Upgrading to Jet 2019: Start Jet Analytics Service

Jet AnalyticsThis post is part of the series on Upgrading to Jet 2019.

After completing the configuration of Jet Analytics 2019, I discovered that the Jet Data Manager Scheduler was not running:

Services

Continue reading “Upgrading to Jet 2019: Start Jet Analytics Service”

Upgrading to Jet 2019: Upgrade Jet Analytics

Jet AnalyticsThis post is part of the series on Upgrading to Jet 2019.

With the new 2019 version of Jet Analytics installed, the second step is to configure it.

This is done by launching the Jet Data Manager Server. When it launches an activation box will be displayed; select your activation method and click Next:

Jet Data Manager Server - Select Activation Method

Continue reading “Upgrading to Jet 2019: Upgrade Jet Analytics”

Upgrading to Jet 2019: Install Jet Analytics

Jet AnalyticsThis post is part of the series on Upgrading to Jet 2019.

Before installing the new version of Jet Analytics, it is worth noting that Jet recommend leaving the old version installed while installing the new. There is no reason given for this, but I presume it is tio allow for easy rollback in the event of problems.

To install Jet Analytics, download the Jet Analytics software:

Jet Downloads

Continue reading “Upgrading to Jet 2019: Install Jet Analytics”