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

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

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.

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:

…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:

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

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:

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

Now everything is ordered as expected:

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:

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:

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:

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:

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).