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

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: Series Index

Jet Global Data TechnologiesBack in April I did a series of posts on implementing the various products from Jet Global Data technologies (or Jet Reports as they then were before their re-branding).

Jet Global have recently launched new versions of their products and I thought a short series on upgrading might be useful. This post is the series index, and will automatically update as the posts go live (if you’re reading this on a syndicated post, make sure you check back to the original as only that will update).

In this series I will be upgrading both Jet Analytics (formerly Jet Enterprise) and Jet Reports (formerly Jet Professional).

Upgrading to Jet 2019
What's New In Jet Analytics 2019
Install Jet Analytics
Upgrade Jet Analytics
Start Jet Analytics Service
What's New In Jet Reports 2019
Upgrade Jet Reports

Implementing Jet Professional: Deploying Jet Views

Jet ReportsThis post is part of a series of posts on implementing Jet Reports and is part of the Jet Professional sub-series. I’m a little late in posting this one, as I forgot to include it with the original series when I posted it.

Jet Professional provides a number of database views which can be used to simplify reporting, rather than using the Microsoft Dynamics GP tables (the structure of which can be confusing to many users of Dynamics GP.

However, the views are not deployed automatically, but need to be deployed after the first client has been installed. To deploy the views, log into Microsoft Dynamics GP and then start the Jet GP Updater which is in the Jet Reports installation folder:

Windows Explorer

Continue reading “Implementing Jet Professional: Deploying Jet Views”

Implementing Jet Reports: Management Reporter vs. Jet Reports

Jet ReportsThe vast majority of my clients are users of Management Reporter, when implementing Jet Reports, one of the key areas, which needs to be discussed, is how Management Reporter compares to Jet Reports.

Fortunately some work has already been done in this area by MVP Belina Allen, the GP CSI. She has done a series of videos on Management Reporter versus Jet Reports.

Below are links to the playlist of all videos as well as links to the individual videos:

  1. Playlist containing all of the below videos
  2. Excel Add-On: An Introduction – Management Reporter vs. Jet Reports
  3. Building Rows – Management Reporter vs. Jet Reports
  4. Building Columns – Management Reporter vs. Jet Reports
  5. Building Trees – Management Reporter vs. Jet Reports
  6. Report Definition – Management Reporter vs. Jet Reports

If you’re a Management Reporter user who is implementing Jet Reports, I’d encourage you to take the time to watch Belinda’s videos.

Jet Reports is Now Jet Global Data Technologies

Jet Global Data TechnologiesAs of 1st Ovtober 2018, Jet Reports have re-branded to Jet Global Data Technologies (or Jet Global for short).

Since Jet Reports was created, the company has gone through a number of changes and no longer just does reporting. This was true enough as was, but with the recent release of Jet Budgets it is even more accurate. The name change is meant to represent this expanded focus.

As part of the company re-brand, the products have also been re-branded:

Old Product Names Now Product Names
Jet Express for Excel Jet Basics
Jet Professional Jet Reports
Jet Enterprise Jet Analytics
Jet Web Portal Jet Hub
  Jet Budgets

As you can see, the Jet Reports name itself is not going away; instead Jet Professional has been re-branded to Jet Reports. My initial thought was that I didn’t like Jet Professional being re-branded to Jet Reports as this could cause confusion for clients, but I think the reality is that most clients already refer to Jet Professional as Jet Reports.

Jet Global also promise that existing users and partners “will experience the same level of service and even more features and functionality now than ever before, and there will be no significant changes to the way Jet Global conducts business with its global partners and customers”.

Jet Reports 2018 R2 New Features

Jet ReportsI wrote a blog post on Implementing Jet Reports earlier this year. While I was mid-series a new version of Jet Reports was released, Jet Reports 2018 R2.

Jet Reports published a blog post on the new features and there are some quite nice ones included.

  • Run Reports directly from your Windows Desktop – Run your reports without even starting Excel; just right-click and the new task tray app will let you know when your report is ready.
  • Run Reports in the Background within Excel – No need to wait for your report before you can start using Excel as you can run your report in the background and continue working with your other Excel files.
  • Updated Jet Ribbon and Windows within Excel – The Jet ribbon has been updated and the entire user interface has been refreshed to be more consistent.

    Several items have been rearranged to be easier to find and other related items have been grouped together for a cleaner experience; all configuration settings have been combined under a single button and the run options have also been consolidated.

    In an effort to better explain what they do, the Refresh and Report buttons have been renamed to Run and Run from Cache, respectively.

Details on all of the changes are available on the Jet Reports blog.

Implementing Jet Enterprise: Implementing Jet Professional

Jet ReportsThis post is part of a series of posts on implementing Jet Reports and is part of the Jet Enterprise sub-series.

No, the title is not a mistake. When you purchase Jet Enterprise, you also get a licence for Jet Professional. This ensures you get the full reporting capability available from Jet Reports. It also means that as well as following my series on Implementing Jet Enterprise, you should also follow the series on Implementing Jet Professional.

In the last post, on the Jet Enterprise sample reports, I said you could use the reports for both Jet Professional and Jet Enterprise, but forgot to say how. The how is because you have access to Jet Professional if you install it.