← All posts

How to create reports in Dynamics NAV across companies?

Reporting across companies without building a separate report for each one: treat the company as just another dimension. In detail, with Power Query.

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.

Wouldn’t it be nice to to easily report on NAV data across companies? To use the company as just another dimension to slice the data by? Today’s blog post explains how to use Power BI in combination with NAV to create reports such as this one:

How to create reports in Dynamics NAV across companies?

Cross-Company reporting in older NAV-versions

Dynamics NAV is generally strictly separating each legal entity – called company. Each customer, product and transaction is created for a single company. There are some exceptions within standard NAV such as intercompany processes that automatically create inbound and outbound transactions for two companies.

In order to consolidate financial information, NAV allows to consolidate multiple companies. Since NAV’s consolidation is strictly based on G/L-Entries, reporting on multiple companies in other areas such as sales receivables, product sales or purchase is nearly impossible.

For quite some time NAV allows to create so-called linked tables that are sourced from SQL-Views. Since SQL-Views may span across multiple companies, it is possible to create pages that show information from more than one company. You can read more about linked tables here.

This approach is rarely used to our experience since creating and maintaining SQL-Views can become a challenge – especially for databases with multiple companies. Reading and writing such a SQL-View is something not every NAV-Developer feels comfortable about. Whenever companies are added or renamed, all SQL-Views need to be modified as well.

Using NAV 2013’s OData and Power Query

NAV 2013 introduced a new object type in NAV – Query objects - see our blog post here. We want to use Queries in combination with Power Query to create a very clean and straightforward solution to report on multiple companies.

We would like to use our previous example of sales and receivables in order to focus on the aspect of across-company reporting.

To start we just create an empty Excel-File. In case that you have not installed Power Query you can find it here. In Power Query we create a blank query:

How to create reports in Dynamics NAV across companies?

The solution to import customer ledger entries from multiple companies is based on a pattern explained in Kasper de Jong’s blog post.

The main idea is to call the OData-Query for a single company, to wrap this call into a function and to call that function for each company that we want to report on.

The first query is the following:

How to create reports in Dynamics NAV across companies?

The first line states that we are creating a function that expects one parameter called CompanyName. The second line establishes the connection to my NAV 2013 R2 OData Feed. The NAV Query behind that OData-Feed is joining the detailed customer ledger entries and customer ledger entries:

In the next two lines CompanyList = Source{[Name=“Company”]}[Data], andCustLedgEntries = CompanyList{[Name=CompanyName]}[CustLedgEntries], we are using OData’s inherent capability to navigate between related entities – in this case from the Company element to the CustLedgEntries-table.

*CustLedgEntriesWithCompanyName = Table.AddColumn(CustLedgEntries,“Company Name”,each CompanyName)*is used to add a new column “Company Name” to our customer ledger entries table whereas the value is set on each row with the value of our parameter CompanyName.

We may invoke this function manually:

How to create reports in Dynamics NAV across companies?How to create reports in Dynamics NAV across companies?

And we get the result as expected:

How to create reports in Dynamics NAV across companies?

The next step is to call this function for all companies that we are interested in. This is also a rather easy and straightforward query:

How to create reports in Dynamics NAV across companies?

The interesting line is this one:

Source = Table.Combine(List.Transform(Table.Column(CompanyList,“Name”), QueryPerCompany))

It uses a list of all companies that are given (CompanyList) and calls my previously defined query QueryPerCompany by leveraging the List.Transform-function. The result of each call is then combined (Table.Combine) into one table that is returned in the in-clause.

This query will now be used as our main data source. It is important to have the load settings set this way:

How to create reports in Dynamics NAV across companies?

In Power Pivot we add two tables – Customer and Date-table that both originate from the same oData-Source. In our example we assume that the customer numbers are harmonized across companies, if not some additional steps would be necessary – for example a mapping table could be used in Power Query.

The resulting data model looks like the following:

How to create reports in Dynamics NAV across companies?

The measure Amount of Receivables needs to summarize all Customer Ledger Entries up to the last date due to the semi-additive nature of the underlying fact table.

CALCULATE(Sum(CustLedgEntries.[Amount LCY])]Filter(All('DateTable');DateTable[Datum] <= MAX(DateTable[Datum])))

Tying everything together, we can create this nice-looking pivot-table:

How to create reports in Dynamics NAV across companies?

We may compare the amount receivables of both companies:

How to create reports in Dynamics NAV across companies?

With just a few additional clicks the receivables could be visualized using Power View on a map - as shown at the beginning of this blog post.

Another approach using the Power Query GUI is to start from the Company-Table that shows all companies and all available queries:

How to create reports in Dynamics NAV across companies?

We need to remove those columns that we are not interested in:

How to create reports in Dynamics NAV across companies?

The last step is to click on the small double arrow and select those columns from the CustLedgEntry-table that we want to expand:

How to create reports in Dynamics NAV across companies?

The advantage of this approach is obviously how simple it is - no knowledge of Power Query’s M language would be necessary. However, it does not provide any flexibility (e.g. in terms of automating which company to report on).

Power Query bug and workaround

One issue arises (at least in the most current version as per end of March) when you try to import oData-feeds into Power Query that return more than 1000 rows. Dynamics NAV splits OData-responses into pages of 1000 rows by default. It seems that Power Query does not know how to handle this and runs into the following error: [DataSource.Error] Cannot parse OData response result. Error: An error was read from the payload. See the ‘Error’ property for more details.

The same problem seem to occur in Microsoft Dynamics CRM, see this MSDN-Forum entry.

The easiest workaround until this is fixed by Microsoft is to change the Page Size property in NAV to something very high:

How to create reports in Dynamics NAV across companies?
Next postReport on NAV's Account Schedules in Excel by using OData

Questions on this topic?

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

Get in touch