If that title doesn’t get you excited about Microsoft Dynamics NAV then nothing will. What on earth does that even mean?
This post forms a part of a series that is really just about getting the most value out of the data that you are already collecting in your NAV system. In recent NAV releases Microsoft have been giving a lot of attention to how you can report on your data and the ability to expose query objects as OData services is one of the new tools at our disposal.
Let’s say that you want to show:
Item1 Sales2 Quantity3 and Amount4 by Customer No5
In this example we don’t actually need to use a query because everything we need is available from the same table.
- Posted item transactions are stored in the Item Ledger Entry table.
- Ledger entries relevant to sales transactions have an Entry Type of Sale.
- The Quantity field holds the posted sales quantity.
- The Sales Amount (Actual) fields holds the actual sales amount of the transaction.
- The Source No. field holds the related Customer No. when the entry type is sale.
The Item Ledger Entries list page can be viewed as a chart to provide the data that you are after or alternatively filtered and the data sent to Excel. Ad-hoc charts on pages are discussed in more detail here.
What if we changed the question? How about:
Item Sales Quantity and Amount by Customer Posting Group
This isn’t quite so straight forward. The Customer Posting Group isn’t an available field in the Item Ledger Entry table. Instead, we need to retrieve the Customer Posting Group from the Customer record corresponding to the Item Ledger Entry record. How can you do that?
Creating the Query
- In the Development Environment open the Object Designer and create a new Query object.
- Leave the Type of the first row as DataItem and select Item Ledger Entry as the Data Source i.e. the Item Ledger Table is the initial source of our data.
- In the properties of this row set the DataItemTableFilter property. Set a filter on the Entry Type field of Sale.
- View the properties of this DataItem row.
- In the DataItemLink property select Field of No., Reference DataItem of Item_Ledger_Entry and Reference Field of Source No.
- This tells the system to find the Customer record where its No. field matches the Source No. field of the Item Ledger Entry record.
- The DataItemLinkType property allows you to define how the query behaves if it can’t find a Customer record, but the default of “Use Default Values if No Match” is fine for now.
- Notice that the Method column default to Sum. This is what we want, but you can select Count, Average, Min or Max as well.
- Also notice that the Group By box is automatically ticked for all columns with a Method Type of None i.e. show the sum of Quantity and the sum of Sales Amount (Actual) for each Customer Posting Group.
To confirm the query is calculating the results as expected you can run it from the Object Designer. A preview of the results will open in the NAV client.
Pretty sweet. Now that you’ve got the query in place you could use it as the basis for a Generic Chart (search for “Generic Charts” in the menu) to be displayed on your Role Centre and bask in your query-writing skills every time you open the client.
Alternatively, you can expose the results that the query calculates via a web service so that an external application can grab them.
By “external application” I of course mean Microsoft Excel. if there is one business intelligence tool that your users already know and love it is Excel. Microsoft know that, which is why you can export any page to Excel or save any report to Excel. Lovely.
Exposing the Query
Now, open the “Web Services” page in the NAV client. Create a new record with an Object Type of Query and Object ID of the number that you saved your query as. Give the service a descriptive name, avoiding spaces and symbols and then tick the publish box.
You’ll notice that the OData URL field is populated with the URL at which this service is now available. Wow, that was easy. To confirm, you can hit the link to the right of that field and see the contents of the OData feed. It may not mean much to you, but Excel will lap that up like a cat with cream.
Consuming the Query
Take a copy of the URL and open Excel. Open Data (tab), Get External Data (group), From Other Sources, From OData Data Feed. Paste the URL from NAV into the “Location of the data feed” box. Follow the steps in the wizard to load the data from the feed and create a table in the Excel sheet.
And there it is. The results of your NAV query loaded in real-time into your Excel workbook.
But, wasn’t that quite a lot of effort for only a marginally impressive report? Yes it was, so let’s make it more interesting.
We’ve answered very specific questions with the previous examples. What if we were interested in sales per month rather than Customer Posting Group? Or sales per item Product Group per Customer Price Group per Month?
You could have a query that joined the Item Ledger Entry table to the corresponding item and Customer records. Use web service to expose the raw data via OData and allow your users to create their own reports with PivotTables in Excel. Better.
Here I’ve joined the Item Ledger Entry, Customer and Item tables together and hooked Excel up to the feed. Load the results into a table in one of the sheets. Once you’ve got the data you can harness the remarkable power of PivotTables to report on whatever you want.
This report shows the sum of sales quantity and sales amount by item by posting date, grouped by month. I’ve also added a “slicer” on the Source No. field that allows you to filter the source data.
Once you’ve designed the report in Excel you need only refresh the data from the feed to update your report. You can even set the workbook to refresh the data whenever you open the file or on a timer.
Business intelligence has never been such fun.
Disclaimer: this is a great way to extract data from NAV to analyse in Excel, but be aware of the limitations of both OData and Excel. In my tests, Excel was able to load records at a rate of around 1,000 / second.
It is probably a bad idea to attempt to load your entire G/L Entry table into Excel. if you want to analyse more than a few hundred thousand records speak to us about getting an OLAP cube set up instead.