Without messing with the standard out-of-the-box reports, which requires a very seasoned VB developer to create and customize, you can utilize external reporting and spreadsheet tools with live data from Dynamics NAV.
It's no secret that Microsoft is trying to make printing reports obsolete in favor of real-time analysis that can be consumed with any of your electronic devices. Printed reports, in essence, are obsolete the moment you print them out. Imagine if 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 Dynamics NAV. In addition, with Microsoft's introduction of Office 365, there is now a native integration to Power BI.
You can also utilize web services to publish queries data to be consumed by any external applications such as Excel. Third-party reporting developers such as Jet Reports Express offer a free version of their Excel-based reporting that integrates with Microsoft Dynamics NAV.
This chapter will explain some of these popular reporting options and how they can be used with your existing Dynamics implementation. In this chapter, we will cover the following topics:
- Defining queries and charts
- Defining web services for external applications
- How to configure and use PowerPivot in Excel
- How to configure and use Power BI with Office 365
- Downloading and installing Jet Reports Express
Query is the name of a Dynamics NAV 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 Dynamics NAV 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, min, and max)
If you are a Dynamics NAV programmer and you have worked with the previous versions of Dynamics NAV, you will see the advantages and the possibilities of this new object right away. The Query object makes the summarizing of data much easier without complicated coding in reports. It makes data retrieval a lot faster.
In this chapter, we will show you how to define a query using the query editor and where and how to use queries on your developments. Once we know how to write and execute queries, we will compare them both in time and effort of development and in speed, against the old ways of retrieving the exact same data out of the application.