Excel Steps to the Fore in Microsoft Dynamics NAV 2013

June 18 2012

Like it or not, having accurate data is the single most important reason why businesses implement ERP. Yes, we are all talking process improvement, productivity increase, increasing throughput, reducing inventory levels, or whatever other key goal you may have in mind, but in the end what makes all this possible is having accurate and timely data. If your ERP does not enable a solid reporting strategy, then nothing else that you do makes much sense.

When it comes to reporting in Microsoft Dynamics NAV, especially with the impending arrival of NAV 2013, the traditional approach of building custom views of the data inside the application is starting to look obsolete in many ways - from budgeting a project to training users to managing the long term maintenance of the solution.  With access to improvements in data sources, and desktop reporting tools, the future of reporting and analytics for NAV solutions has stepped forward: it is Microsoft Excel. 

Dynamics NAV is moving to a world where data models are open, flexible, simple, and easy to understand; where the simplest reports can give you answers to most complicated questions. Let's examine the traditional reporting options and how Microsoft has re-engineered both NAV and Excel to offer a more attractive value proposition.

Reporting, a traditional view

With Dynamics NAV, the word report typically brings up one of the following ideas:

  • A report object, which a programmer develops according to your specification. It shows you a predefined set of data across a predefined set of columns and rows;
  • An account schedule, a flexible definition which shows you financial data with a fairly comprehensive set of functionality to twist and tweak it to your liking;
  • An inventory analysis, the same as account schedule, just not for finances, but for inventory data;
  • An analysis by dimensions, which lets you to crisscross financial data over up to seven dimensions (three of which are predefined by NAV).

Talking out-of-the-box functionality, that's more or less what people consider. Bands of customers simply go along with the list above, and consultants most certainly don't complain, because there is a lot of consultancy work and man hours to keep the cash-flow beast happy in all of the above. Talking customer value, though, most of the above should be avoided.  Read on to understand why.

Report object

There is only one thing the report object in NAV is good for: stuff that you put in folders. Sales invoices, shipment notes, picking lists, are all good candidates. Anything else? Not quite.

The beauty of a true reporting system comes from its ability to answer questions which you couldn't foresee at the time you designed the system itself. The report object in NAV is not particularly good at that.

With the report object, one report answers one question. If you have a different question, you develop a different report. And then you have it: the ability to answer the same question. Today's answers don't answer tomorrow's questions.

I would take a wild guess that about a half a million of the report objects are idling in NAV systems worldwide, and have yet to see some run time. They were needed to answer a one-off question, and by the time a developer produced the report, the question had long been answered. To give some perspective to that: that's about $75M of investment in development effort thrown in the wind.

Financial and inventory analyses

To an extent, they are flexible, but to tune them to do what you really need, you need to know a lot of quite geeky stuff in NAV that's not applicable elsewhere. It's difficult to find employees who can really turn financial analyses into powerful analytical tool. And when you do, there are the technical and functional limitations, the things that these features simply can't do, and you are back to square one.

Analyses by dimensions

As long as answering ad-hoc questions is what you need, the analyses by dimensions are your best bet. They are simple to use, easy to configure, and you don't need much training or special knowledge to get new ones up and running.

Still, with the limitation of up to four user-defined dimensions, they can only provide the answers within the four-dimension boundaries.

Excel

It may look strange that I put an Office application into the NAV reporting stack, but by far, the best analytical tool in NAV is Excel:

  • It's ubiquitous. You can safely bet that when you are installing the NAV client, Excel is already there.
  • It's common. Most of users in a business setting know how to use it already, and finding people with good Excel skills is easy.
  • It's flexible. You can tweak it as you go, change anything from the layout to data sources, and do it quickly.
  • It's powerful. Once the data is in, there is hardly a question Excel can't answer.

I'm not religious about Excel, but I could continue this litany until the world's end. And when I finish, I bet you could add a couple more "it's this or that" of your own. As a matter of fact, Excel is a part of general IT literacy and it's probably the most versatile computer program ever written.

Whichever limitation of any of the technical choices above you take, Excel efficiently mitigates all of them.

NAV has always been good at getting the data into Excel. If you know where the data is in NAV, building an Excel summary report on top of that data is virtually a no-brainer. As versions of both Excel and NAV have progressed, the two have worked together better and better.  But the possibilities that NAV 2013 opens with Excel are limitless.

Microsoft Dynamics NAV 2013 allows you to publish any information and then to consume it from OData-capable applications. Excel features, such as PowerPivot, allow you to subscribe to OData sources, and create multi-source data models for fast and powerful in-memory data analysis. Now, reporting on any kind of information from NAV is literally a click away, and all it requires is publishing the appropriate page web service.

Where to invest

Reporting should be least about how you run the reports, and most about the questions they can answer through open, flexible, simple data models.  When data models are complex or data is unavailable, inaccessible, or unintelligible, you won't be able to report on it in easily.  Reports don't answer questions-data models do.

When you are budgeting for your NAV implementation project, don't budget for a set of reports. Plenty of project scope documents list fixed reports and what kind of information they will provide. While these may be valuable, investing into them will give you answer to only those questions. When you come up with new questions, you'll need to spend more. And the cycle simply repeats. Any investment you make into static reports has a very limited, if any, return on it.

Instead, budget for data model development, and choose Excel as your strategic reporting tool. Developing decent data models for PowerPivot reporting will take considerably less time and will have lower impact onto your budget than developing static reports, and the return is huge. Instead of getting a list of seventeen "clickables" in your NAV solution, you get a versatile platform that can answer those seventeen questions, and hundred more of those you couldn't think of when you were signing your statement of work.

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About Vjekoslav Babic

Vjekoslav Babić is an independent Microsoft Dynamics NAV consultant, trainer, author and blogger, with 10 years of experience in NAV and 16 years of experience in IT.

As a solutions architect and a project manager with a leading Microsoft Dynamics President's Club service provider company, as a Microsoft Dynamics NAV consultant with Microsoft Services, and as an independent consultant, he has been working on Microsoft Dynamics NAV implementations ranging from tiny one-man-bands to international mega-corporations, delivering services and trainings all over the world.

In 2008, Vjekoslav co-authored the acclaimed book "Implementing Microsoft Dynamics NAV 2009". Vjekoslav runs an active blog about NAV implementation, project management and development best practices, acts as a columnist and editorial advisory board member at MSDynamicsWorld.com, and as a columnist in a number of other web or printed periodicals in Croatia and worldwide. Vjekoslav is also a frequent speaker at Microsoft or Microsoft Dynamics conferences.

Since spring 2010, Vjekoslav has been awarded the prestigious Microsoft Most Valuable Professional (MVP) award for Microsoft Dynamics NAV.

More about Vjekoslav Babic

Comments

stephenhogan's picture

Try out #JetEssentials version 12. You will have probably better fine tuning if that is what you are after!