← All posts

Report on NAV's Account Schedules in Excel by using OData

Publishing account schedules as an OData feed and analysing them in Excel — using the web services NAV 2013 introduced.

Archive post from 2014. It describes Dynamics NAV and the Microsoft BI tools of that time; Dynamics 365 Business Central and today’s Power BI differ in places.

Today’s blog post is about using NAV 2013’s new capability to publish Pages and Queries as OData-Feeds in order to analyse NAV’s data in Excel.

As explained here both Pages and Query object in NAV can be published as Web Services. Microsoft used this approach in NAV 2013 R2 to bring life to account schedules in Excel - read more here. We want to carry that idea forward and explain some of the limitations of the standard solution and possible workarounds.

First, let’s summarize what NAV 2013 R2 provides out-of-the-box:

Account Schedules that are supposed to be used in Excel may be configured in the Account Schedule KPI Web Service Setup that is located under Departments/Administration/IT Administration/General

Report on NAV’s Account Schedules in Excel by using OData

Multiple Account Schedules may be added to one KPI Web Service Setup. The button Publish Web Services adds the appropriate page to the Web Service table. The Action Web Services on the Navigate-Ribbon show the web service entry and its URL.

Report on NAV’s Account Schedules in Excel by using ODataReport on NAV’s Account Schedules in Excel by using OData

Let’s use the OData URL that is displayed to get the data in Excel. After everything has been imported into Excel, the following Power Pivot table is created:

Report on NAV’s Account Schedules in Excel by using OData

…just one flat table without any related tables. For the sake of this example, let’s focus on the following account schedule that allows to analyse the success of campaigns:

Report on NAV’s Account Schedules in Excel by using OData

In Excel the following Pivot-Table will be created based on the Power Pivot connection:

Report on NAV’s Account Schedules in Excel by using OData

The first thing to note is the ordering of the items that differs from NAV’s account schedule. This is a result of the fact that Excel just used the alphabetical sorting (descending in my case) of the KPI-Name. How to solve this? We need to use the Line number that NAV used to display the items in the right order what is actually a very simple modification of the standard export page.

We need to change Page 197 Acc. Sched. KPI Web Service slightly by adding the Line Number as an additional Output-field:

Report on NAV’s Account Schedules in Excel by using OData

Now we need to tell Power Pivot that the KPI Name should be order by KPI Line No in the background:

Report on NAV’s Account Schedules in Excel by using OData

Now everything is ordered as expected:

Report on NAV’s Account Schedules in Excel by using OData

This Pivot-tables already provides quite some flexibility, for example we can change the measures that are displayed, we can use date field to narrow down the time period to report on:

Report on NAV’s Account Schedules in Excel by using OData

However the structure of the flat fact table does not allow doing any advanced date calculations – for example imagine that you want report on different years, quarters or months.

In order to achieve this, a “real” date table is necessary in Power Pivot. Date tables may be manually created in Excel, imported from a SQL-database or created as a Page in NAV that is published as an OData-Feed. The last option provides the great advantage that everything can be configured in NAV – from the range of dates that should be reported on to the name of the columns.

A simple Date-Tables based on a NAV-Page may look like the following:

Report on NAV’s Account Schedules in Excel by using OData

It is technically based on the virtual date table and adds some reporting-friendly columns such as Year-Month, Year-Quarter etc. to the view.

With the help of the resulting OData-Feed we can import the date table in Power Pivot and connect it to the date field of our account schedule export:

Report on NAV’s Account Schedules in Excel by using OData

This now helps us to do all sort of date related actions in Excel such as adding the date hierarchy to the columns what allows users to drill down into any date level:

Report on NAV’s Account Schedules in Excel by using OData

In upcoming posts we will go into other details of the integration between NAV and Power Pivot such as handling of NAV dimensions and combining multiple fact tables into single reports (e.g. sales opportunities and sales quotes to create a comprehensive sales forecast).

Next postAnalyze Sales Receivables with NAV and Power Pivot

Questions on this topic?

Get in touch — we are happy to share what we know, beyond the blog too.

Get in touch