Geographical Reporting with NAV

Today we are writing about how to create geographic reports in Excel based on NAV’s data. We are using NAV 2013 as the data source. In order to focus on the geography we are using a very simple model. We are just creating a report on the sales of Cronus broken down by geographical attributes.

To start we just create a copy of page 22 (customer list) since we need to make slight modifications to that page for our purpose.

What we are doing here is to avoid empty country region codes since NAV-implementations are typically using empty code as the implicit value for the local country. By using the country code from the company information we create an explicit value that will help us later. This is a typical “ETL-like” step that we could have done later in Power Pivot as well. In order to allow reusability it makes sense to do any ETL-steps as early as possible what in this case is the page in NAV.

The result of the page now shoud look like:

We create a very simple query to allow exporting our fact data – sales invoices:

In Power Pivot we import these two data feeds and a date table.

We add the relationships (SalesInvGeo.Bill-to Customer No. -> Customer.No, SalesInvGeo.Posting Date -> DateTable.Date) and create a hierarchy in our customer table that consists of Country_Region_Code, City and Address. This hierarchy will be important when analysing the data later to avoid any geographical ambiguity.

Let’s first create a simple pivot table based on these – Sum Sales is just a summary of the Line Amount:

Now let’s create a Power View visualisation using the fields of our hierarchy:

One of the great things about Maps in Power View is the ability to drill through into different levels. Double-clicking the green bubble for Germany will show this view:

If we are interested in sales in Hamburg we can even drill down into the street level: