The 5 must-know facts about building Microsoft Navision Reports
Building Microsoft Navision reports can be a chore. Why does a system like Navision, which after all is based on modern underpinnings and is a current product, not provide flexible, ad hoc reporting and analysis out of the box?
Here are the 5 must-know facts about building Navision reports and why NAV doesn’t have the reports or report creating functionality you need, without you having to do some work first:
- It does (kind of)… part 1. The reality is that Microsoft Navision and most other modern ERP systems do provide lots of standard reports out of the box. If you need to produce a cashflow report or a VAT return, for instance, then you can be fairly sure Navision is going to do that, really well, as standard. The challenge comes when you want to produce ad hoc reports – Navision reports that the software vendors did not predict in advance that you would need. These tend to be a) specific to your business b) consequently, of high business value and c) frequent. An example would be a report on the total margin generated by sales of Product Group A to customers in Poland in Calendar Q1, broken down by sales rep and compared to last year.
- It does (kind of)… part 2. To create new, ad hoc reports you need some technology and tools. Microsoft Navision does ship with some of these. Your Navision package includes SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services) and SSRS (SQL Server Reporting Services). If you’re a tech whizz who knows what to do with these tools, who knows Navision really well and who understands your business too, then you can use these tools to create new reports. The downside is that most people are not. If you have these skills in-house then bottlenecks will form around the one or two people who can create new reports. If you do not, you will need to employ or contract someone in. In either case, new reports are slow to create and then need to be maintained.
- Easy, self-service reporting is always impossible over an ERP on its own because of ‘normalisation’. Normalisation is the way in which system architects and programmers design the databases that underpin your ERP system. They don’t do it for fun, it is done for good reasons, but it does make reporting really difficult. With a normalised database, information is spread across lots of tables. Imagine, for instance, a table of invoices. Each invoice will have a customer associated with it and that customer will have an address. Rather than have the address stored on every invoice, however, we store a ‘customer code’ (or similar) on the invoice table, then have a separate ‘customer’ table, keyed by the ‘customer code’ where the address details are stored. The advantage of this is that if you then want to change the address, you only have to do it in one place. The disadvantage is that if you want a report of ‘all invoiced revenue for Cheshire’, then you have to join multiple tables together to make this happen, which is what turns something seemingly simple into a complex, IT person’s job. In real life, the complexity is much greater and modern ERP systems are made up of literally hundreds of individual tables. To get almost anything meaningful out of them, you have to join the tables together (using a language called SQL – ‘structured query language’) and relatively simple or common Navision reports may require multiple tables to be joined. To bring this to life, MS Dynamics Navision 2009, for instance, has over 1380 tables with, on average, over 100 columns each, in its database (assuming you are running the standard package with no modifications).
- Running complex queries over databases slows them down. We now know why reporting over an ERP system database is complex – because of normalisation. So when we want to get a meaningful report, we have to build an SQL query that joins multiple tables together. The bad news is that this kind of query negatively affects performance of the database. The best-case scenario is that your report is going to run slowly (the ‘kick-off the report then go for a coffee’ scenario). The next best case is that in order to serve your reports (and keep your system running at the same time), you have to upgrade your hardware (the ‘throw more horsepower at it’ scenario). The worst-but-not-that-uncommon situation is that when you run certain reports at certain times of day, the performance of the whole system can be affected. This is pretty scary when you consider that Navision is also running your sales order entry processes, warehouse, and maybe your production. We have seen at least one customer where they used to send out a tannoy announcement just prior to running certain reports, warning users to ‘get off the system and go for a coffee, while the sales report runs’.
- Not all the information that you need in your reports is in your ERP. No matter how clever or nifty the ERP system is, it can’t really be expected to tell you something it doesn’t know. So if you have information that isn’t held in the ERP but which you do want to include on a report then you have a problem. Many businesses have ancillary systems that hold valuable data, such as CRM, e-commerce or HR systems. Many also have information only held on spreadsheets. Good examples include budgets, targets, rebates and ancillary costs.
So while ERP vendors ship their products with the reports that they think you’ll probably need every day, inevitably you’re going to want many more reports that are specific to your business, your managers and your situation. It’s these very ad hoc reports that represent the greatest value to your business.
But because of the technical nature of reporting tools and the underlying normalised nature of your ERP system’s database, the business user is never going to be able to self-serve their own reports and/or analyses, with the tools that come out of the box. And those technical users who can use these whizzy, complex tools are going to become bottlenecks, and spend their lives creating and maintaining reports for others.