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!
