Order Intake – reporting is about the data as well as the technology.

So according to almost all of the major research organisations, the biggest single issue that most users have with any ERP system is their inability to provide the reporting that they need. It seems to be agreed that while the processing of business transactions is ‘adequate’ they believe that huge amounts of insight is tied up in their systems that they are just unable to access.

That is true for Dynamics NAV users historically, as well I was reminded this afternoon when visiting an organisation using a now rather old version. They complained, not without justification that they had from the beginning, now nearly ten years ago, just wanted to know (what we here in the UK term) the order intake value. That is the value of orders taken, amended less returns or credits in any given day. It’s a KPI that companies use to understand their sales trend well before they can invoice or credit those orders, helping them plan. Not an unreasonable request really – to know much have we sold today?


Especially with the uptake of modern reporting technology like the PowerBI toolset, I should be able to create a lovely dashboard that I publish up onto a big screen in the sales office telling me my sales, in every form of graph known – shouldn’t I? You’re right, that part is easy now. Bravo Microsoft for giving me that ability at I have to say either a free or ridiculously affordable price (£10/month).


But it’s still not as easy as it should be – why? Because if the data is not easily accessible, even the best reporting tools (and PowerBI is a great tool) struggle, as Dynamics NAV’s data is structured in a way that doesn’t help at all.

Sales Documents deleted when complete & both show as positives?

The first issue is that as standard Dynamics NAV cleans up its Sales Documents as soon as they are complete. It not an option it just does it. As soon as an order is fully invoiced it’s deleted. That means it’s not just a simple report on the order file to know what we’ve sold today or this week, as if it’s already shipped and invoiced, it will have gone to Sales Invoices. If it partly shipped and invoiced, it will be in both places meaning I have make sure I report on the outstanding amount (LCY) from the sales line to prevent reporting it twice.

The second issue is that Dynamics NAV has one table for Sales Documents that has both orders/invoices and returns/credits in; all as positives with just a document type field differentiating. That means I have to perform two separate queries and subtract the one from the other to get a figure of the net outstanding value difference.

Separate tables for Invoices and Credits?

Thirdly, unlike the order documents, the posted invoice and credits are in a separate table. Don’t have a go at Microsoft for inconsistency here as it goes back to the Navision days and so many reports are built on that structure – they would have a storm of protest if they changed it now. It still means two more queries though.

No date of entry or user who entered?

Fourthly, the sales invoice and credits only have an editable order date and don’t have a field that shows the date and time the order was entered. (Yes I know you can see the date last amended from SQL in 2016, but that does say when it was inserted and each change). This means that back dating orders is possible, something most companies don’t want, again making reporting difficult.

How do you include changes or cancellations in today’s figure?

And finally what about changes or amendments to orders? How would the system cope with those? Imagine the order was placed for £1000 yesterday but today they rang and tripled it? By the usual rules I should report £1000 against my order book yesterday and £2000 today. Where do I report this from when there is only one record for the sales line (assuming I have invoiced any of it yet).

Change Log?

What about the change log I hear some say, that feature that you can turn on that logs the time and date and user that changed any piece of data in Dynamics NAV? Well first of all I don’t think you should turn on the change log for transaction data like the sales line table because of the performance impact and secondly – go on then try constructing the report you need from that table. Each changed field is logged as separate record so good luck with that.

Really needs a customisation

What we need and currently have to customise in on almost every installation is a custom order intake log. This maintains an entry for every change to a sales order line from its initial entry to any amendments against an order date at the least and sometimes a date and hour so we can report on the change to the order book in the last hour for instance.


With 2016 this can be done using the insert and modify ‘events’ on the sales line so every change is logged, every time, but before that a developer will need to create some hooks to call a procedure that updates this log. Obviously you have to be more careful with the delete trigger through as deleting because it’s been invoiced or credited does not count but deleting because the customer has cancelled the order does. You need to create the entries as positive for new order and negatives for cancellations or new credits, cancelled credits are of course positive.

One other thing to watch for if not using 2016’s events is that the copy document function, using ‘Copy Document Mgt.’ codeunit, has functions that insert records without the (True) parameter that means the insert and modify hooks will not get called.

Other things to watch for are any function that create temporary sales lines as these should not create order intake entries so you need to check for this.

Then build a query

Once you’ve created this you have a single query on that one table that you can filter by date, hour, by the user that entered the order, sales person, type and number and even item category to get one summary list of figures back. And yes I do suggest you use a query object if on 2013 or above as the ‘total’ function will make it a lot faster and have less performance impact.

Add to the Role Centres

Don’t forget it’s simple once you have the query its simple to publish to a Cue on a role-centre. Our most common trick is to set a filter on the salesperson or customer service agent so they see the value of the orders they have taken today, this week and this period. When this is published to the phone client (as shown in the screenshot above) you’d be amazed how more often sales people are logging in, nothing motivates them more than seeing how they are doing financially. Don’t forget you can use the Cue Setup screen to set targets as shown below.


I’m guessing your sales managers and even CEO are also going to be interested in how sales are going so maybe their role centres need the overall figure as well.

Remember to clean up afterwards

Remember to build a tidy function that deletes the entries after a period of time – you don’t need the data beyond a month and certainly a year in my experience as you’ll be looking at actual invoices not the value ordered by then.

Lost Sales Analysis

One bonus – you can get great analysis of ‘lost’ sales from this which is there is almost no other way of doing. What orders have been cancelled and therefore deleted because you couldn’t supply etc. Understanding these lost sales is critically important to making sure they don’t reoccur. In fact, we have customers who enter a sales line and immediately cancel it when they get an enquiry that doesn’t order just so they can analyse what they need to do to make the sale next time.

Please Microsoft, make this standard

Final point is that I’d love Microsoft to incorporate this into standard Dynamics NAV for Madeira. Knowing what you have sold (as opposed to invoiced and credited) today is key to most businesses, I’d like to see anyone at MS survive for a day without that information!

Author: James Crowter

I’m passionate about how businesses can improve their efficiency by getting process optimal more of the time. For the last twenty five years I’ve worked to help organisations of all sizes and types implement the ERP & CRM software that typically they decide they need when things are going wrong. I’ve seen that work unbelievably well and enabled those organisations to rapidly grow but I’ve also had some hard projects over that time where it’s felt more like warfare at times. Since 1996 (and version 1.01) I’ve been working with a small Danish product called Navision that’s now become Microsoft’s Dynamics NAV and I’ve also been using and consulting around Microsoft CRM since 2005. As managing Director of one of the longest established first Navision and now Microsoft Dynamics partners I’ve been involved in the complete history including numerous product councils and system design reviews. It’s my privilege to know many of the key Microsoft executives and product designers and have insight into both where the products are now and their future direction. So colleagues & clients have asked me to start this blog to share some of the insight that both this knowledge (obviously where not restricted by NDA’s or client confidentiality) and experience can help. Specifically I want to concentrate not on the specifics of how (there are some great blogs already for that) but why. If any user helps their business make better decisions or consultant can give better advice then that will be objective achieved. I founded Technology Management in 1992 and have led from the front ever since. Helping clients use technology to grow their business is my passion through explaining technology in terms that everyone can understand. My interest in computing began at the age of eight, long before my school had the equipment to cope. Throughout school and university I developed software commercially. I hold many IT certifications, such as Microsoft Dynamics NAV (for over 17 years), Microsoft Dynamics CRM (for over 10 years), as well as Microsoft Windows Server, Exchange and SQL. In October 2015, I was awarded the title of Most Valuable Professional (MVP), a title given to a select few individuals (31 currently) across the world specifically for Dynamics NAV. After years of working with a range of distribution and manufacturing software for hundreds of organisations, I focus on understanding the business requirements of an organisation, what it will take to deliver the systems required to maximise their potential. Follow me online via my other social channels: - Twitter: @jamescrowter - LinkedIn: linkedin.com/in/jamescrowter Or email me directly at james[.]crowter[@]tecman.co.uk.

Leave a Reply

Your email address will not be published. Required fields are marked *