Jet Reports is a powerful reporting tool that integrates well with Microsoft Dynamics 365 Business Central.
In this guide, we’ll walk you through what Jet Reports is and provide a step-by-step guide on how you, too, can start using it effectively to speed up your business reports process.
Unlock the full potential of your reporting capabilities with onpoint as your trusted partner. Our expertise in implementing Jet Reports for Microsoft Dynamics 365 Business Central empowers you to harness real-time data, create customised reports, and drive informed decision-making.
What is Jet Reports?
Jet Reports is a business intelligence solution that seamlessly integrates with Microsoft Dynamics 365 Business Central. It allows you to extract real-time data directly into Excel, create customised reports, and visualise information within familiar spreadsheets. The tool helps you automate data extraction with accuracy and also allows you to drill down from summary reports to transaction-level details.
How to Set Up Jet Reports
Before we talk about how to use this tool, you need to first set it up on your computer. So, let’s walk you through the steps you should follow to do that.
Download and Install Jet Reports
Open Excel and Access the Jet Tab
Launch Microsoft Excel. You’ll see a new "Jet" tab in the ribbon. This tab contains all the necessary tools for using Jet Reports.
Configure Your Data Source
Click on “Data Source Settings” in the Jet tab and select "Add." You'll then be asked to provide information for the following:
Server Name: Enter your server’s name.
Database Name: Enter your database’s name.
Authentication: Choose Windows Authentication or SQL Server Authentication, then enter your credentials.
Test Connection: Click “Test Connection” to ensure it works.
After filling in the required information, click “OK” to save the configuration.
Set Default Data Source and Regional Settings
In the Jet ribbon, go to “Options” and set the newly configured data source as the default.
Moreover, ensure the regional settings in Jet Reports match your location. Access these settings through “Options” > “Regional Settings” in the Jet ribbon.
Verify your setup
Reopen your Excel software and the Jet tab to confirm your data source is accessible. Check if you can view and access the tables and fields in your database. If everything is showing as you expect, then we’re ready to go, otherwise, you may have to carefully start over.
How to use Jet Reports
You can start using this tool by following these steps:
Access the Jet Reports Interface
After installing Jet Reports, open Excel and you will see a new tab labelled "Jet" in the ribbon. This tab includes all the tools you need to create and manage reports.
Using the Jet Browser
Click on the “Browser” button in the Jet tab. This opens the Jet Browser, which displays all available tables, fields, and functions from your data source. You can pick any field you want from here into your Excel worksheet. This makes building reports easy by allowing you to visually select and place data elements.
Creating a Report Using the Report Wizard
Launch Report Wizard: Click on “Report Wizard” in the Jet tab. This wizard guides you through what you need to do to create a report.
Step 1: Select the data source you configured earlier.
Step 2: Choose the type of report you want to create. Options include Financial Report, Business Report, and Table Report. For this guide, we’ll use Table Report as an example.
Step 3: Choose the fields you want to include in your report by dragging them from the available options into the layout section. Now, organise these fields into rows and columns as needed. After that, you can add filters to refine the data. For example, you might want to filter customers by country or sales data by date range.
Step 4: Once you have defined your layout and filters, click “Finish”. Jet Reports will retrieve the data from your data source and populate the Excel worksheet with the report.
Creating Advanced Reports with Jet Functions
Jet Reports allows you to use some powerful functions for creating dynamic and interactive reports. Let's talk about some of them.
NL Function
Purpose: Used to create lists or retrieve specific values from the database.
Syntax: =NL("What", "Table", "Field", "FilterField", "Filter")
Example: =NL("Rows", "Customer", "Name", "Country", "USA") retrieves the names of customers in the USA.
NF Function
Purpose: Retrieves a field from a specific record returned by an NL function.
Syntax: =NF("Record", "Field")
Example: =NF(A2, "Name") fetches the name field from the record in cell A2.
NP Function
Purpose: Used to control Jet functions programmatically, such as setting filters dynamically.
Syntax: =NP("Function", "Argument1", "Argument2", ...)
Example: =NP("Filter", "Customer", "Country", "USA") sets a filter for the Customer table where the country is the USA.
Try creating Pivot Tables. After retrieving data using Jet Reports, you can further analyse it by creating a Pivot Table in Excel. To do this, you need to highlight the data range you want to include in the Pivot Table. Go to the Insert tab in Excel and click on PivotTable. Choose where you want the Pivot Table to be placed. Now you can arrange the fields by dragging and dropping fields into the Rows, Columns, Values, and Filters areas to summarise and analyse the data.
Refresh your Data. Click the “Refresh” button in the Jet tab to update your report with the latest data from your data source. This will help you to make sure your reports always reflect the most current information.
Saving and Sharing Reports. Now that everything is done, you should save your Excel file as usual. This ensures that your report configurations and data are preserved. Finally, you can share the report by saving it to a shared location, such as SharePoint or a network drive. Or you can email the Excel file to those to whom you want to send it so that they can access and view the report data.
Conclusion
Jet Reports is a very powerful tool for creating dynamic, real-time reports directly within Excel very quickly. And that makes it something you may want to consider if you need a very robust reporting system. In this guide, we've shown you how to install, configure, and start using Jet Reports to generate insightful reports with your Dynamics ERP data.
Partner with onpoint today and experience seamless integration, automation, and robust business intelligence solutions tailored to your unique needs. Request a consultation to get started.