Overview
The GP Update Utility creates a set of tables, views and security roles in your Microsoft Dynamics GP database which help with security and significantly improve the reporting experience.
Jet Reports recommends that the GP Update Utility be used with all Microsoft Dynamics GP databases.
The GP Update utility is intended to be used by a database administrator and requires write access to the Dynamics GP SQL database
Benefits
- Friendly names for tables and fields- Tables and fields in Dynamics GP have both Physical names (e.g. “GL30000” and “ACCATNUM”) and friendly names (e.g. “Account Transaction History” and “Account Category Number”). The physical names can be cryptic and hard to use. Installing friendly names allows report designers to see and use friendly names.
- Jet Views - The Jet Views are a set of SQL views which combine data from GP SQL tables. These views can significantly reduce the effort it takes to create reports.
- Table Relationships – Installing table relationships makes it easier for Jet Reports designers to create reports. In the Table Builder and other features, the linking between related tables is automatically entered so that the designer doesn’t have to define the relationship.
- Jet Date Table – This table can be used with Jet Reports to easily report on date periods.
- SQL Roles - These Microsoft SQL security roles permit users to read the Dynamics GP data. The GP Update Utility creates a set of roles which enable administrators to easily manage the security model.
Roles include:
- JetAllUsers –All Jet Reports designers and viewers should be assigned this role.
- JetEssentialsGLUsers – Anyone who needs to use the “GL” function in Jet Reports should be assigned this role.
- Dynamics GP Reporting roles –These roles (e.g. rpt_bookkeeper, rpt_executive, etc.)provide users access to read a set of tables and views that are critical to their role.
Prerequisites
The GP Update Utility is intended to be used by a database administrator. Before running this, you will need:
- Write access to your GP SQL databases.
- Jet Reports or Jet Basics installed.
- Your Dynamics GP application open and be logged in.
(the friendly names are stored in the application, so the application needs to be open to access them)
You may also want to watch the video:
How to install the GP Update Utility
The GP Update Utility can be found in C:\ProgramFiles\JetReports\Jet GP Updater.exe. (or under C:\Program Files (x86) on 64-bit systems)
Select the Windows orb (Start Menu in the bottom left corner), and select “All Programs”.
Find your Jet Reports application menu, click to open the Administrative Tools folder and choose GP Update Utility.
Establishing a Connection
If you have defined a GP data source in the Jet Data Source Settings it will appear under Data Source. Select your data source from the Dropdown menu. Your server name, Dynamics database and authentication should appear.
If you have not defined a GP data source, you will need to enter the Server, Dynamics Database, and your Authentication information. The GP dynamics database is named “DYNAMICS” with GP 2010 or earlier.
Choose a Company. Typically you will want to select “All Companies”. When you have finished, click Next.
If you have multiple Dynamics GP data sources, you will need to run the GP Update Utility on each.
Installing the Tables, Views and Roles
Select the features you want to add. We recommend that all features be included in the initial installation.
The default Table Relationships are for the standard Dynamics GP database. If you have 3rd party tables in your Dynamics GP database, you must first install the default table relationships, and then you can install customized relationships for your database if desired. See Installing 3rd Party Table Relationships below
Click Run. At this time the Jet GP update utility will be adding the tables, views and roles to your Dynamics GP SQL database. This step may take several minutes.
You may be prompted with a warning dialog box, this is just to make known that changes will be made to your Dynamics database. To successfully run the GP Update Utility, select Yes.
After the Utility has finished installing the features, you can view a detailed log of the installation.
Icons:
You can click on the individual tasks to see additional information.
Review and address any warnings or errors.
If you wish to retain a record of the changes made, select Export Results.
The installation is complete. You can select OK and Close the GP Update Utility.
Installing 3rd Party Table Relationships
Be sure to install the default table relationships prior to installing any 3rd party table relationships.
Dynamics GP table relationships files are created by running the Tables report from the Dynamics GP Dexterity Utilities. Select the 3rd Party dictionary for which to extract table relationships and run theTables report with these options: Keys and Relationships. Set Series to All and click Insert All. Click Print and save the file. This file can then be used as input for the Jet GP Updater.
To install,re-run the GP Update Utility, add only Table Relationships, and select Load from file.
Detailed information on Features
-
Friendly names for tables and fields
In Dynamics GP, you may notice that the table and field names that are listed in the Designer window are somewhat cryptic. The design tools in Jet Reports (e.g. Table Builder, Browser, Report Builder, Jfx, etc.) can display your table and field names in several easily readable formats, similar to the Smart List window in GP.
The friendly name feature requires three tables - JETFPG, JETTMAP, and JETFMAP. The GP Update Utility adds these tables to your database and populates them with them with the Friendly Names provided by Dynamics GP.
-
Jet Views
Jet Views are SQL views which bring together information from one or more SQL tables into a view that is designed for reporting from. The Jet views have been designed around common report writing areas to make it easier for report designers to create reports. The GP Update Utility adds SQL views to the GP company database with the prefix "Jet”. These views include:
Jet CustomerMaster
Jet CustomerOpen Application
Jet CustomerOpen Transactions
Jet CustomerPeriod Balance
Jet Date
Jet FiscalPeriod
Jet GL Account
Jet GLTransactions
Jet Item AllLocations
Jet Item byLocation
Jet PayrollTransactions
Jet ProjectAccounting
Jet PurchaseDetail
Jet PurchaseHeader
Jet SalesDetail
Jet SalesHeader
Jet VendorMaster
Jet VendorOpen Applications
Jet VendorPeriod Balance
(this list subject to change, please refer to your log file for definitive information).
-
SQL Roles
This GP Utility feature takes advantage of the Role-based security model in Dynamics GP by offering the user access to certain tables and views. Jet Reports has created our own User Roles as well.
The JetAllUsers role provides users with access to the following tables: SY01500 on the Dynamics database and JET_DATE, JETFMAP, JETFPG, JETTMAP, JETVMAPon the company database. JETFMAP,JETFPG, JETTMAP, and JETVMAP are used by Jet in order to support Friendly Names.
The JetEssentialsGLUsersrole provides user with read access from the set of tables that are required to use the GL function in Jet Reports.
These tables include GL00100, GL00102, GL00105, GL00200,GL00201, GL10000, GL10001, GL10110, GL10111, GL20000, GL30000, SY40100.
Dynamics GP Reporting Roles provides the user access to download standard default GP roles, if these are not currently in your Dynamics GP Database. Each role (e.g. rpt_bookkeeper, rpt_executive, etc.) is made up of security tasks that allow any user assigned to the role to connect to a number of standard Tables and Views.
This is a set of roles which Microsoft introduced with Dynamics GP 10.0 and may already exist in your database – so your users may already have this set of permissions applied. Jet Reports users and designers should be assigned to one or more reporting role (or otherwise have SQL security permissions to allow them read access to appropriate SQL tables and views).
The GP Update Utility will only add roles if they do not exist. It will not delete or modify any existing roles.
Comments