I promised a blog about reporting in Microsoft Dynamics NAV using Dimensions and indeed Analysis by Dimensions covers this need in spectacular fashion but there are also the Account Schedules which unfortunately are often discarded because people don’t understand how to use them or don’t have the time or sufficient knowledge on how to build them to get the end results they need.
In this blog I am not going to go into in-depth discussions on how to write Account Schedules. There are already quite a few blogs out there on how to do this and I am not a particular fan of reinventing the wheel. If you want to know how to set up an Account Schedule then I suggest you check out the excellent series of Account Schedules 101 by Kerry Rosvold here.
What I will be covering in this blog is the thought process behind creating your Account Schedule reports. Often I find that customers have a set of reports that were written for them by partners and they have never had to look at creating new ones. In fact I see many customers fall back into the comfortable habit of exporting data to Excel and manipulating it manually. This is often because the need for new Account Schedules does not seem to come about until there has been a shift in staff and the reporting requirements suddenly change. The shift in staff could be either at senior level management, who will now be looking for different data for analysis or new accounting managers at ground zero who are looking to extract data to analyse in a way that they are comfortable working with.
So what is the best way to train Account Schedules? Easy – Take a step back!
As with most training I find that it is not the how to do something that matters but will the customer remember how to do it months/years from now when I am gone and the fuzzy comfort of having a consultant to hand is no longer an option; and will they remember how to apply that knowledge? Let me expand…
Writing any report is simple. There are lines and columns and data that we analyse and we get some figures out that we can manipulate and put into pretty pie charts to impress the boss; but is it really THAT simple? Diving straight into writing any report will likely leave us with more questions than when we first started and this often leads to a great deal of frustration for the person who has written the report because they are constantly plagued with more requests for data so it is better to plan first before you even attempt to start writing your report.
Here’s my take on planning your Account Schedule Reporting…
Repeat after me: First we crawl, then we walk, then someone drops a brick on your head.
No I have not gone crazy. This is the first step in helping customers remember the best way to approach their report writing.
First we crawl – Imagine a baby crawling. His back is horizontal to the floor and he is moving in a horizontal direction from point A to point B. This can be related to first creating lines for the report.
Then we walk – Imagine someone walking. They are standing up, they are vertical and they are very tall. If we look at them we can relate this to the vertical columns of our report. This is the second step in writing our report.
Then someone drops a brick on your head – The idea of a cube type object landing on you is very significant here. I have mentioned cubes before in my discussions about Dimensions. The brick can be related to the Analysis View we can choose to apply to the report. This enables us to choose a particular set of Dimensions with which we can slice the data.
Now the concept of the steps we need to take are understood, we also need to answer a few more questions about the report. This is best done at each stage and I use the 5W & H approach to this.
For the lines:
- What data/accounts am I analysing? Should any of these be restricted? What is the best way to summarise the data? Will the data need to be sliced using dimensions?
- Who will be reviewing the data? Should they have access to this data? Are they in a position to understand the data that is being presented?
- Where will I get the data from? Will this be from a G/L Account or Budget? Where will the final data figures from my report be used?
- Why has this particular set of data been requested? Is this a one off request or will this be used for management reporting packs or in the analysis of other figures?
- When? This triggers the question of the time frame for which we are analysing the data. I once helped a very confused client with a report that was pulling up incorrect figures. We found that they had entered specific G/L Accounts in their rows and the report was working well for the previous year figures, however they had added new G/L Accounts for the following year and these were not being picked up in the report. The best solution is to ensure you add a range for G/L Accounts but When? is not a question to be ignored.
- How will the data be presented? This will make a difference to how you choose to use the bold and underline functions and where to leave blank lines. You should also consider how the report will look when exported to Excel and when printed.
For the Columns:
- What data are we analysing? What is the goal for the report we are showing? i.e. Do we want to show a Budget vs. Actual Variance and we can therefore hide all other columns or do we need to show our workings.
- Who is the data for? Will they understand it in the way you have chosen to present it?
- Where is the data coming from – G/L or Budget Entries?
- Why has this particular set of data been requested? What is the possibility that changes to the analysis will be requested?
- When? Are we looking at this year compared to previous years or are we looking at this quarter compared to the same quarter last year? This will make a big difference in our column build
- How do we want to view the data? Should this be shown as Net Change or as a Balance? Should we total columns to show differences?
For the Analysis Views:
- What Dimensions should be used? Will these dimensions always apply to this report? Do you need more than one Analysis View for comparing different sets of data?
- Who has requested the information? Will it be easy for them to apply the dimensional filters to get the results they are looking for? Should the report be pre-filtered for them?
- Where is the information coming from? Is this G/L or Budget entries? Have dimensions been used for budgets and posted correctly to the G/L? Do we need to look at setting up Default Dimension rules?
- Why has this particular set of information been requested? Is this for a department or just a single person? Will the data results prompt request for more reports? Can the requester create/manipulate their own reports in NAV or in Excel?
- When should the Start Date on the Analysis View Card be set to?
- How will the data be presented? Can it be manipulated to form interesting and dynamic charts for Management Report packs?
If you can take a step back and answer all these questions (and the others that are raised as a result of these answers) and then plot your report first on paper, you will find a more structured approach to your reports means less tweaking and rewriting and also a report that makes sense first time around. Just remember the 5W & H approach and repeat the mantra: First we crawl, then we walk, then someone drops a brick on your head.