Jet Express for Dynamics NAV is a new report-building solution integrated into Microsoft Dynamics systems that allows users well-oriented with MS Excel to create complex analysis reports and dashboards on company financials, sales, inventory and other data. Using Excel features such as charts, pivot tables, filtering, etc. gives users flexibility in generating a visual view of reported data.
Let’s look specifically at how the solution works based on integration with the MS Dynamics NAV 2013 system.
Before starting to use the Jet Reports, there are some setup and configuration steps required. These include:
- Installing an external component (which is an Excel add-in)
- Importing Jet objects into Dynamics NAV system
- Registering the Jet web service
Creating the connection to the Dynamics NAV database and configuring the data source from the Excel application
Installing the external component:
There are 3 versions of the Jet Reports solution: Jet Express, Jet Essentials and Jet Enterprise. We are going to download the free version of the add-in, Jet Express for Microsoft Dynamics NAV. The installation file can be downloaded from the official site: http://jetexpress.jetreports.com/
Be sure to select the correct platform – it should be the same as the platform for your installed Excel product, either 32-bit or 64-bit. Otherwise, you might get compatibility errors during enabling the add-in within Excel.
Once the package is downloaded, follow the Installation wizard to complete the process.
After finishing the installation, select the option to enable the Jet Add-in in Excel for the current user. Optionally, select the option to launch the Jet Resources page. This is a community link to report samples, snippets and useful tips and tricks for working with Jet Reports.
Importing Jet objects into Dynamics NAV
A set of objects should be also loaded into the Dynamics NAV application to provide the integration between the database (i.e. data) and Excel. The objects can be downloaded from the following page: http://jetexpress.jetreports.com/ under the Training and Quick Start Materials section, Jet Reports Business Objects for Microsoft Dynamics NAV 2013.
Once complete, open the Dynamics NAV 2013 Development Environment, and import the downloaded .FOB file into the Object Designer:
Registering the Jet web service
The Jet Data Source codeunit needs to be registered as a web service from Dynamics NAV. To do this, open the Web Services page in the Dynamics NAV menu, click New to add a new line and enter the object that needs to be published:
Close the Web Services page.
Configuring the data source from Excel
Open the Excel application, and on the Jet tab, click the Data Source Settings button. In the Data Source Settings window, click Data Source Version, and select either Dynamics NAV 2009 or 2013 depending on your installed product version (in this demo, we will be using NAV 2013).
Select the Web Service tab, and in the Jet Codeunit, type Jet_Data_Source. Also, type in your server name and select the Company. The company selection will drill down to the NAV database instance you have specified and will show you the list of available companies. Click OK to close the window.
Now everything is ready for the report creation.
We are going to create a simple report displaying item availability as per the specified date filter. The data that will be fetched from the database will then be presented as a pivot chart.
Item Availability report
In Excel, on the Jet tab, click the Table Builder button.
In the Table Builder that is now open, click the Add table button to select the table on which our report will be based.
In the drop-down list, find and select the Item table and double-click on it to select it. Jet Table builder allows adding multiple tables and creating links between them in order to create complex analysis reports. For this demo, we will use only one table to demonstrate how Jet Reports work.
Below the Item table, a list of its fields will also appear – both normal and Flow Fields. Locate the No. field and select it to add to our report:
This No. field is also added at the top section of the Table Builder. The field caption can be changed as needed. For example, click in the No. caption field, and change the text to Item No.:
Select the Item table under the Tables section. The Filters – Item part appears to the right side of the pane:
Click the Add Filter button, and add the following Date Filter for the inventory values for the year 2014:
Select the Flow Fields fast tab, and add the Inventory field to our report:
This field will also be added to the Report Layout section at the top of the Table Builder.
Click OK to close the Table Builder. The Jet Reports engine will generate the report structure based on the data source information we entered. The values which will be displayed after processing is complete, are editable, however, the end user may skip this information and click the Refresh button to generate a table view.
An Excel table with filter-enabled headers is generated.
Click on any cell within the table. On the Insert tab, click the Pivot Table button, there select the Pivot Chart option. Let’s add the pivot chart to a new Worksheet:
Click OK to add the chart. The chart is built in real time as the user adds the fields, filters, etc. Let’s enhance the view of our results.
In the Pivot Table Field List, drag the Item No. field to the Report Labels section and Inventory field to the Values section.
In the Row Labels, select the first 5 items to be included in the chart and click OK:
Now we need to hide items with zero inventory. To do this, in the Pivot Table Field List, drag the Inventory field to the Report Filter Section. In the Inventory filter’s cells to the left, filter out all values excluding zero and click OK to apply the filter:
We might want to change the chart type. To do this, right-click on the chart area, select Change Chart Type and select the simple Pie chart. The result might look like this:
The report can be refined with other information, such as filtering out items by Location Code, Inventory Posting Group, etc. Apart from that, Item Ledger Entry can be linked up to have an overview of item sales and adjustments.
This sample report is a demonstration of how Jet Reports can facilitate in analyzing the data extracted from the Dynamics NAV system in an Excel environment familiar to the user. Also, the solution eliminates the need to manually copy the data from Dynamics NAV pages and create filtered tables. Jet Reports is a powerful tool for creating high-impact reports in Excel and achieving productivity.