How to create reports in Dynamics NAV across companies?
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:
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:
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:
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:
And we get the result as expected:
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:
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:
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:
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:
We may compare the amount receivables of both 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:
We need to remove those columns that we are not interested in:
The last step is to click on the small double arrow and select those columns from the CustLedgEntry-table that we want to expand:
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:
