Jet Express for Excel with Dynamics GP is one of Microsoft’s most up-to-date reporting tools yet. An extension included at no charge with Microsoft Dynamics GP version 9.0 or higher, it gives users the unique ability to create basic reports and business queries directly in Excel. Users are also able to effortlessly update their reports with real-time GP data; resulting in reporting information that is both accurate and timely. Not only is it free for customers, but it’s quite a powerful extension to add to your existing Microsoft Dynamics GP software. Below are a few tips for using Jet Express for Excel with Microsoft Dynamics GP.

“Alert” Parameters

One of the nice features of your Jet Express report is the ability to trigger reports be sent to yourself (or distributed to other team members) based on a set of “alert” criteria that you define. An example of this might be to create an alert when “budgeted results exceed actual” for a particular expense category or account.

Reporting Filters to Show/Hide Data – User Defined Box

When establishing filters for your report, it is important to pay close attention to the user-defined box which appears to the right of your chosen report filter. By checking the box, future users can choose to alter the filter criteria for the report, making the report interactive. However, unchecking the box prevents the user from doing so, and acts to keep that specific reporting filter unchanged with each successive running of the report (remember that while the reporting filter remains unchanged, the associated financial data will always reflect real-time information when the report is “refreshed”)

Jet Function Wizard (Jfx) for the GL – “Budget” parameter

Budgets are associated with a specific year in GP, so when designing your report using the Jet Function Wizard (Jfx) for the GL, be sure that you budget and fiscal years coincide; or the report will return a -0- value for your budget!

Jet Function Wizard (Jfx) for the GL – “Help” Tab

The “Help” tab within the Jet Function Wizard serves to turn the parameters you’ve chosen for your report into an easily understandable sentence structure; enabling you to verify that your chosen parameters will result in your desired report. An example appears below:

Parameters

“Where” = cell
“What” = balance
“Account” = SE6

Help Tab Display

“Retrieve the current balance for a set of accounts”.

In this example, the current balance would be returned for the account number referenced in cell SE6, with the result placed in the cell you have designated.

Using Table Builder for Multi-Table Reports

Using the Jet Express for Excel “Table Builder” design tool, you can link information contained in one or more Dynamics GP tables, filter the information within each table, and then create a new “combined” table for future queries. Once the combined table has been created and the associated query is run, creating an Excel pivot table will display your tabular results in a user-friendly report format.

Automatic Report Formatting

Jet Express allows for some automatic formatting of your report using certain “key” words. Typing the key word “Fit” in row 1 of your report design will ensure that the columns in the report will automatically resize themselves should there be a large amount of data contained within the cell. Placing the key word “Hide” in row 1 of the column you’d like hidden, or in column A of the row you’d like hidden will automatically hide that particular column or row. To hide an entire sheet, enter “+HideSheet” in cell A1 of your worksheet.

Wildcard Filters & Order of Operation

Jet Express supports some commonly used reporting filters such as <, > and =, but also allows for the following wildcard filters:
* *jet* those fields containing the word “jet”
*jet those fields ending with the word “jet”
jet* those fields beginning with the word “jet”
? Mass?y those fields with values such as “Massay” or “Massey”

A specific order of operations can be combined with existing filers as seen in the example below:
30 l (>10&<20) those with field values of 30, OR having field values greater than 10 and less than 20. Jet Express for Excel with Microsoft Dynamics GP is a fabulous reporting tool for Dynamics GP users; combining real-time reporting of GP’s existing financial data with the familiarity of Microsoft Excel to engineer better and more consistent financial reports for all team members. Contact our office to take advantage of this freely available solution!