70 - Account Schedules In Dynamics NAV 2017 - Part 5
I'm gonna keep going on account schedules, and I am actually gonna show a feature which involves dimensions and analysis views so it's a little bit involved s has kind of three concepts boiled into one solution. I do have a video on dimensions so if you want to check that out, definitely have a look at that and on analysis views, I think I have something about that but I'm definitely gonna be diving deeper into that going forward so, let me go ahead and star out here.
If I go into analysis and account schedule like I have done before I'm going to take a look at the account schedule I just created which is called income statement for coffee mug, so over here we actually pulled in accounts from a chart of accounts, and these are accounts straight from Cronus which is the demo company that Microsoft ships or NAV for Microsoft with and as we can see they actually separate it out sales of retail, sales of raw materials etc, and within sales of retail we have domestic EU exports, And then the cost same thing, domestic EU export.
So this is sort of old thinking, you don't want to create these sub accounts per se inside your chart of accounts you want to shrink your chart of accounts, have fewer accounts and then separate them out with tags, and so those tags are actually called dimensions in a NAV, you can do it also with something called posting groups, that is sort of an old way of thinking.
I prefer the dimensions when I'm trying to solve this, so what I'm gonna do is go ahead and show you a solution to actually take the chart of accounts like these three accounts into one account,and that is separated out with these tags: domestic EU and export. So I want to get rid of having three accounts in my chart of accounts an I want to have one. And at the same time, I want to be able to report like this in my account schedule so I want to able to sometimes show it broken up, but not on my chart.
So let me just go ahead and this is sort of like a cooking show where I show you the dough already made, but I’ll go thoroughly through how that was done. First of all, I’m gonna create a dimension that has these three elements, domestic, EU, and export. I’m actually just gonna call it sales channel and if I go here into dimensions and, like I told you I have already done this just for sake of time, I’ve created a sales channel dimension inside the dimensions list and the dimensions values for that are domestic, EU and export so I basically created a new dimension called sales channel with three options.
Next if I go into my chart of accounts and take a look at that, inside my sales of retail I created a new account called sales retail, and I'm skipping this suffix of post-fix, because I don't want three accounts on my chart accounts for this, I just want one, and then I want to be specifying as I’m posting into to which tags goes and so when I post I’m gonna actually post on transaction just to show you into this account, I can tag it with dimension.
If I go to my journals right here and go into my general journal, edit journal, I posted some transactions on the 25th already but I’m just going to post one here on more, so I find that new account that I created which was 4450 and lets say I’m posting here: Domestic sales and it’s - 50,000 negative credit is always good for sales, balancing account I’m just going to put it into a receivable account. I think I have other receivable here or something like that.
Now notice that I actually now have a sales channel code that I can specify, this shows up as long as you setup the dimensions to be one of the eight shortcut dimensions, then you can “select column” and show it here, just as a quick reminder of that, If I go into general ledger setup which is a setup screen in NAV, there is a dimension section here and if I select that as one of the first eight actually prefer to be one of the first four, you'll see that in minutes because the account schedules just has four visible, we can expose more if you are a developer.
I have picked this as dimension three and then I am able to select this as a column in the general journal, let me go back to the general journal, and I'm gonna pick this since its domestic, I pick domestic, I’ll categorize it as domestic, then I go ahead an post, yes successfully posted.
Now I'm gonna go into my account schedules, so the idea here is I want to be able to separate it out on the account schedule, maybe first take a look at this here, this way I only got one account here and I got 95,000 dollars in here, technically these accounts should be just going away and I just have one account 95 thousand and here I can't see whether it's gonna to be EU, domestic or export.
But I like to see that on account schedule, so I go in to role center, analysis, account schedules and I’ve actually created a new income statement where I’m separating these out and here is a little bit of a tricky thing when you want to use dimensions like these inside an account schedule, you will have to attach an analysis view, alright, its a technicality, and I’ve created on here called coffee much test. I'm just gonna go into that and show you how it's created.
So basically it is an Analysis View and Analysis View is a way to view dimensions in different ways, you can look at dimension on the X axis and another one in y -axis and see the cross-action to those, I’ll go through that in a separate video but just a simple one that I set up is just a test here, there is nothing different that I did, I just put a starting date early enough so that it brings in all of the transactions and, I setup which dimension I'm looking at and then I hit update.
The reason why I need this is because the systems then starts tracking dimension entries and it has that database to include in the account schedule, so it's a little bit of a technical thing, all you have to remember is to create an analysis view with the dimension that you want to report on and attach it to the account schedule, that's all, and make sure you hit update, and you can also enable update on posting and I’m gonna do that right now. That is actually a good thing because then you don't have to hit update here to get updated numbers, it will just update automatically going forward, so I recommend that definitely. So I get out of here.
Back into my account schedules; so I told you this brings in three concepts so it gets a little bit complicated but not too much hopefully, and so I get here into my edit account schedule, and I’ve actually created this beforehand so I didn't have to type it up and now I am setting up a three different rows, like so, and I have sales retails domestic, sales retail export and sales retail EU, remember this is all coming from the same GL account.
But I need to separate it out, so how do I separate it out? , I show column again, and when I show column I see the sales channel co totaling a new field here, and I can go ahead and specify which one it is: domestic, EU, export. So I did that layout and then when I hit overview, I’ve actually separated out all of the dimensions into an account schedule.
So I can be posting into one account just flocking in with dimension and then separating out like this, so you can see the power of this is like you can have 4 or 5 dimensions and they can be all separated out and you can be drilling into just the minutia on the account schedule that makes sense for whoever you are presenting this for, so you create many account schedules for different groups.
I hope this made sense I know I said three topics thrown into one just to show capabilities of account schedules.