In this blog, I’d like to talk about a feature from Jet Report (The table builder wizard) combined with basic Excel functionalities (Pivot Tables/Graphs).
Jet Reports is a well-known and powerful reporting Excel add-on for Dynamics ERP, including Nav. I have been using it for 2 years, helping our customers building reports for Dynamics Nav. I was mainly developing multi-level grouping reports, which couldn’t be done using any wizard, due to the complexity of the reports I was asked to develop. Those report had to be manually developed using the basic Jet functions (NL, NF, Etc.).
When talking to non-technical users, I realised that the report development mind-process can be difficult to get to develop a report from scratch. That’s where the table builder can help users to create powerful report easily, and the only prerequisite is to know how table are linked in a database/Nav.
I’ll now show a short tutorial on how to use this feature by creating a report summarising Outstanding Quantity per item/location with a graph.
Open the table builder wizard:
On the left part of the wizard, we can select the main table that the report will use (Transfer Line) and select the fields that we are interested in.
If we want to had information from Transfer header, we will have to had a link. To do that, we click on the “Add Table” button on the left, and then, on the “links” part of the screen (green), tell Jet how the 2 tables are linked (“No.” and “document No.” fields). We can now add fields from the Transfer Header Table.
The table builder allows us to add filters. We have two different type of filters, hard-coded ones or user defined ones (when a user run a report he can select the desired filters). On the below example, the user will be able to select a posting date filter when he will run the report.
We can now press OK and the report is generated in design mode:
Let’s have a look at the report when we run it. It’s generating a flat table with all fields selected with the wizard.
The next step of this tutorial will no longer be related to Jet, but with Excel reporting tools.
First, we can build a pivot table using the data generated by Jet, and this pivot table will be automatically updated each time we run the report. We can click on the table, then Insert Tab>”Pivot Table” on the Excel ribbon. The Pivot Table wizard will be setup correctly, and you can build the pivot table as you would normally do with Excel.
Once the pivot table is built, we can click on the pivot table and add a Pivot Chart (Analyse Tab>”Pivot Chart” on the excel ribbon). You can then build the chart following your needs.
Once the chart is setup correctly and working, we can finalize our report by adding slicers and the final user, when running a report, will have access to a customizable chart, letting him exploit the Nav data depending on his needs.
Written by Clement Denat, Microsoft Dynamics NAV Consultant