Implementing SmartList Builder: What is SmartList Builder?

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Before launching into the installation of SmartList Builder, I thought it might be worthwhile to do a post on what it is. All users of Microsoft Dynamics GP will be familiar with SmartList; this is the flexible reporting tool which allows you to generate a report showing a list of master records or transactions. Each series has a set of SmartLists available by default, with predefined favourites where search criteria or columns are supplied already configured.

SmartList favourites can be customised and new ones created quite easily, but new SmartLists themselves cannot be created. To create a new SmareList, you need a tool such as SmartList Builder or, in more recent years, SmartList Designer. In this series, I am focussing on SmartList Builder as this is an additional product available from an ISV for Microsoft Dynamics GP.

Back in 2003 when I started working with Microsoft Dynamics GP, SmartList Builder was available direct from Microsoft. SmartList Builder was originally developed by eOne Solutions and then licensed by Microsoft; in the run up to the release of Microsoft Dynamics GP 2013 SP2, the licensing deal for SmartList Builder was ended and maintenance of the product reverted back to eOne Solutions.

SmartList Builder the product, includes four utilities:

  1. SmartList Builder – SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.
  2. Excel Report Builder – Excel Report Builder generates an Excel Spreadsheet with a live connection back to GP or any other data you include. This means you have live refreshable reports, anytime you need them. You can give all your data from GP to anyone in the organization via Excel – without buying new, full-user licenses of Dynamics GP.
  3. Drill Down Builder – Drill Down Builder completes the functionality of Excel Report Builder by letting you drill from your spreadsheet back into that same record within Dynamics GP. Where Microsoft hard codes a drill down – eOne makes it completely configurable to allow you to drill down to any screen or SmartList in Dynamics GP. This is an essential part of bringing the most common business tools together. Working in GP and Excel all day – it only makes sense to auto-switch between one and the other.
  4. Navigation List Builder – Navigation List Builder allows you to publish your SmartList into the Navigation List user interface.

The standard installation of the SmartList Builder product, gets you all four of the above utilities.

Implementing SmartList Builder: Series Index

eOne SolutionsI’ve posted series on a few different ISV addons for Microsoft Dynamics GP over the last few years, but, somewhat ironically I guess, have not posted all that much about the addin I’ve used the most over the years.

That addin is SmartList Builder from eOne Solutions.

SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.

This series is going to cover the implementation of SmartList Builder; I’ll take a look at report builder in a future series.

Implementing SmartList Builder
What is SmartList Builder?

SQL View to Report on Fixed Allocation Accounts

Microsoft Dynamics GPIf you are using Fixed Allocation Accounts in Microsoft Dynamics GP, there is only a standard report which shows the distribution accounts against one of the accounts. These reports are not very user friendly and can;t be exported to Microsoft Excel in usable way.

Below is a SQL View which can be added to a reporting tool such as SmartList Designer, or a refreshable Excel report, which will allow users to see how Fixed Allocation accounts have been setup.

-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_FixedAllocationAccounts', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_FixedAllocationAccounts
GO
-- create view
CREATE VIEW uv_AZRCRV_FixedAllocationAccounts 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['Account Index Master - Fixed Allocation'].ACTNUMST AS 'Account Number'
	,['Account Master - Fixed Allocation'].ACTDESCR AS 'Account Description'
	,CAST(['Fixed Allocation Master'].PRCNTAGE AS NUMERIC(15,2)) AS 'Distribution Percentage'
	,['Account Index Master - Fixed Allocation Distribution'].ACTNUMST AS 'Distribution Account Number'
	,['Account Master - Fixed Allocation Distribution'].ACTDESCR AS 'Distribution Account Description'
	,CASE WHEN ['Account Master - Fixed Allocation Distribution'].ACTIVE = 1 THEN 'Yes' ELSE 'No' END AS 'Distribution Account Active'
	,['Account Category Master'].ACCATDSC AS 'Distribution Account Category Description'
	,CASE WHEN ['Account Master - Fixed Allocation Distribution'].PSTNGTYP = 1 THEN 'Profit & Loss' ELSE 'Balance Sheet' END AS 'Distribution Account Posting Type'
	,['Account Master - Fixed Allocation Distribution'].USERDEF1 AS 'Distribution Account User-Defined 1'
	,['Account Master - Fixed Allocation Distribution'].USERDEF2 AS 'Distribution Account User-Defined 2'
	,['Account Master - Fixed Allocation Distribution'].USRDEFS1 AS 'Distribution Account User-Defined 3'
	,['Account Master - Fixed Allocation Distribution'].USRDEFS2 AS 'Distribution Account User-Defined 4'
FROM
	GL00103 AS ['Fixed Allocation Master'] WITH (NOLOCK)
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00102 AS ['Account Category Master'] WITH (NOLOCK)
		ON
			['Account Category Master'].ACCATNUM = ['Account Master - Fixed Allocation Distribution'].ACCATNUM
GO
-- grant permissions to view
GRANT SELECT ON uv_AZRCRV_FixedAllocationAccounts TO DYNGRP
GO

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”

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

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

The new version of Jet Analytics has quite a number of new and improved features.

Continue reading “Upgrading to Jet 2019: What’s New In Jet Analytics 2019”

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