Chapter 12. Popular Reporting Options

Without messing with the standard out-of-the-box reports, which require a very seasoned developer to create and customize, you can utilize external reporting and spreadsheet tools with live data from Microsoft Dynamics NAV or Microsoft Dynamics 365 Business Central.

It's no secret that Microsoft is trying to make printing reports obsolete in favor of real-time analyses that can be consumed with any of your electronic devices. Printed reports, in essence, are obsolete the moment you print them out. Imagine that you can look at the important metrics of your company with real-time data at any given moment and make impactful decisions right away. Think about how much of an impact that will have on your business. This is the future we're looking at.

With every release of Microsoft Dynamics NAV, there are more and more companies that are designing their applications around it. In addition, with Microsoft's introduction of Microsoft Office 365, there is now a...

What is a query?

Query is the name of a Microsoft Dynamics NAV or Microsoft Dynamics 365 Business Central application object that was first introduced in Microsoft Dynamics NAV 2013. This application object is only meant to retrieve data from the database. It is a read-only object. It cannot modify, delete, or insert new data into the database.

There are many things about queries in Microsoft Dynamics NAV and in Microsoft Dynamics 365 Business Central that point to the future of our reporting world:

• They allow us to retrieve data from multiple tables at the same time
• They allow us to retrieve only specific fields in a table
• They allow us to group the retrieved data according to certain fields without the need of any explicit key for them
• They allow us to total the retrieved data using different totaling methods (sum, count, average, minimum, and maximum)

If you are a Microsoft Dynamics NAV or Microsoft Dynamics 365 Business Central developer, and you have worked with the previous versions of Microsoft...

Query Designer

Queries, just as any other objects in Microsoft Dynamics NAV or in Microsoft Dynamics 365 Business Central on-premise, have their own designer or editor with CSIDE.

Note

These steps are identical both for Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central On-premise. The screenshots are taken from Microsoft Dynamics 365 Business Central on-premises October 2018 release.

To open Query Designer, perform the following steps:

1. Open the Microsoft Dynamics NAV Development Environment. The Object Designer window will open.
2. On the left pane of the Object Designer window, click on Query to see the list of existing queries:

3. Select the query 9150 My Customers (or any other existing query) and click on Design. The QueryDesigner window will open:

The Query Designer window looks a lot like Page Designer or the new Report Dataset Designer. This will make it easier to get used to developing queries.

In the Query Designer window, we can select one or more DataItem values to define the...

Business Charts

The Windows client and the Web client can display a set of predefined charts that use Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central data; queries can be used as data sources for those charts in both systems.

There are two types of charts:

• Standard Charts (available in Windows client only)
• Business Charts (available with JS add-ins)

Note

You can read more about business charts concepts and components at https://docs.microsoft.com/en-us/dynamics-nav/displaying-charts-using-the-chart-control-add-in.

Standard Charts in Windows Client

We will use the query defined earlier on in this chapter as the data source of a chart, and we will display it on the home page of the Microsoft Dynamics 365 Business Central On-premise Windows client.

To define a query as the data source of a chart, perform the following steps:

1. Open the Windows client for Microsoft Dynamics 365 Business Central on-premise.
2. Navigate to Departments | Administration | Application Setup | RoleTailored Client...

Web services

Starting from Microsoft Dynamics NAV 2009, it was possible to publish page and codeunit objects as web services to allow external applications to access Microsoft Dynamics NAV or Microsoft Dynamics 365 Business Central data and business logic. In the later releases of Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central, it is also possible to publish Query objects as web services.

Page and codeunit objects can be accessed through the SOAP (short for Simple Object Access Protocol) web services. Queries can only be accessed through the OData (Open Data) web services protocol.

Note

We will look at how to create and use Web service OData in Microsoft Dynamics 365 Business Central, since its the same for Microsoft Dynamics NAV.

First, you will need to enable the SOAP and OData services from the Microsoft Dynamics 365 Business Central Administrator; perform the following steps to do so:

1. Start the Microsoft Dynamics 365 Business Central Administrator from the Start menu.
2. Click...

Microsoft Power BI

Microsoft Power BI is a tool that is part of the Microsoft Office 365 offering. It provides a web-based analytical tool for your data that can be set up to gather real-time data. The aim of Microsoft Power BI is to provide business intelligence to companies at a fraction of the cost of buying a regular business intelligence solution.

The following diagram shows the Microsoft Power BI layouts:

The Microsoft Power BI layouts in different devices

There are also content packs, which are specifically designed for Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central (Apps, in this case) within Microsoft Power BI in Office 365. Within the content packs and Apps, they provide some of the graphs and charts Microsoft believes most companies will want to see.

Note

You will need a Microsoft Office 365 account in order to use Microsoft Power BI. To access the content pack, proceed to https://app.powerbi.com/getdata/services/microsoft-dynamics-nav.

Once you sign in using your...

Jet Basics NAV (formerly Jet Express for Excel)

Jet Basics NAV for Microsoft Dynamics NAV is a business reporting tool that's meant to let users create high-impact reports in a familiar environment, such as Microsoft Excel. With Jet Basics NAV (formerly Jet Express for Excel), you can use all Excel capabilities, such as formatting, slicers, charting, and pivot tables.

Note

The following is a product definition by the vendor:

"An extension included with Microsoft Dynamics NAV to give users a simple way to create basic reports and business queries inside of Excel"

.

Note that this is a free version of Jet Basics NAV. There's also a paid version of Jet Basics NAV (named Jet Reports) that you can purchase, so a lot of the functionalities have been stripped out of the free version; there is also a version that allows you to create reports in Microsoft Word (using an extension that's included with Microsoft Dynamics NAV to create custom documents with Microsoft Word).

Visit the JetGlobal website for...

Microsoft Flow

Microsoft Flow is a cloud-based workflow service for creating automated processes.

It can be used in many applications to automate processes; for example, it could be used to generate and send reports automatically, starting from a workflow; even if it is not a reporting tool, it could also be used for such purposes, perhaps integrated by the Microsoft Power BI.

Check out the Microsoft Flow page at https://flow.microsoft.com.

Note

The motto of Microsoft Flow is, "work less, do more". With Microsoft Flow, you can create automated workflows between your favorite apps and services to get notifications, synchronize files, collect data, and more.

Microsoft Flow for Microsoft Dynamics 365 Business Central

There are numerous ready-to-use workflow templates, both for Microsoft Dynamics NAV and Microsoft Dynamics 365 Business Central. These templates are useful to understand how both of them work and also understand their potential. The following screenshot shows the ready-to-use templates...

Summary

In this chapter, you saw some of the reporting and analysis options that are included in Microsoft Dynamics NAV and in Microsoft Dynamics 365 Business Central that extend their functionalities and are very useful to users and companies. With web services, the options for analyzing your data are endless. We also explored some third-party tools that can be useful for creating reports in a simple and convenient way for users.