73 - Account Schedules in Dynamics NAV 2017 - Part 8   

Today I’m going to keep going on account schedules, and these time around I'm actually going to go around power BI as well so it's going to be some more technologies in there, everything connects, obviously.

So how do we print out or view the account schedules and how can we get them into power BI? We’ve created a few accounts schedules so far, and we can actually view them in a different way, for example, this financial o finance performance chart right here, I can select the chart and as you can see these are account scheduled chart list and they come from account schedules so they can be selected an viewed as charts over here but there are some limitations and I did cover that in another video, but it's kind of nice to get it here into the role center.

Also if I go into the accounts schedules and go into this one, for example, We can view them so they are live, that what I’ve normally been doing. You just see them right on the screen and you can export them to excel, we did that in one of the videos, we can print it out that can be printed out into PDF, normally or into word.

I want to do now is go into something a little bit more elaborate than that. I want to get the data of the account schedule into power BI and show as a chart there or play with it in the power BI desktop. So if you are familiar to the power BI, this is going to be interesting for you from that perspective, if you are not I encourage you to actually get familiarized with power BI it's a very versatile product really powerful, definitely recommend it.

Web Services
What I wanna do here is to go in here into web services, basically that's just something that allows you to move the data from NAV to power BI in this case we are using called old data, it does much more than that, but in this case we are just using this to funnel the data over, so If I go in here into web services I get a list of all web service that are setup on my system and out of the box NAV sets up something called power BI finance service.

The account schedule KPI web service and its connected to a special area inside NAV with allows you to set up KPI out of account schedules, and they have an old data URL generated by NAV which gives access to the data and I need to specify this string here when I open up power BI so I connect them together.
Right now I'm just going to go in here if I click on it, it actually gives me the web page lists out the data there, but I just want this string so I’m going to copy it for later.

Let me go ahead and keep going here, there is something called account schedule KPI web service set up so that is actually setting up the web service, and in here we can select the account schedules we want to expose to our power BI and you are going to see in a minute how it works, right now there are 3 of them exposed not mine but it definitely want to expose mine.

New retail statement
I’m going to pick the new retail statement, so on top of just bringing in the accounts schedule I might actually bring in the account schedule that has dimensions, it’s just displaying dimensions and that is going to be really interesting because it shows that we not only bring over the GL accounts, because schedules have dimensions in them, they have GL accounts in them, and all of that as you have seen in previous videos, and in this case I just want to bring in dimension to show that that is quite possible.

I bring in the new retail statement and I want to set the period to be current calendar year here, I want to view by quarter, that good, the GL budget name I actually want to use my budget here, so I am going to use the coffee mug with dimensions in here and I don't have any forecast here we will just leave that. I just hit here publish web service so that this web service is published as power BI finance.

Accounts schedule KPI 
Now I'm just going to get quickly into the screen here, that’s called accounts schedule KPI web service, it's not the set up one, but this one here, this actually shows all of the amounts for all of the accounts schedule lines and for the period. So you can see since it's by quarter here's the first quarter of 2018 and you can see it has the net change, the balance of date, net change budget, all of that out here data, basically raw data out of the system. 

Now these columns here, net change, actual balance, etc cannot be changed, these are the columns you have to work with they are pretty good but it does limit this feature a little bit, no to be able to kind of get this differently laid out.

But you can work with that on the line as you know with the accounts schedule there is quite a lot that you can do on the line with the schedules itself so you can sort of enable it to get the data you want in here, but if you go all the way down. We have the new retail statement, you can see the data is here coming. So what we want to do now is consume these data into power BI.

Actually, go into power BI, this is a Microsoft product it's meant to be business intelligence for the masses, it's not very expensive, its very powerful and it's very easy to do business intelligence in here.

I'm just gonna go in here and say get data, and get the old data feed, that's how it’s coming from NAV and then it asks me for a URL, and I'm going to copy what I just posted before and hit ok, and it actually connects so it does do a credential check before so you might get a credential screen you have to enter in your credential to log into NAV.

Right now I'm using the credentials for the user that I’m logged in as, and then it gives me a snapshot of the data set, I just say load, and then I get all of the KPI data basically here for the account schedules.

Get data out of ERP system
How do we work with this?, now there is a little bit of clean up that we gotta do when we get this data in, when we get data out of ERP system it doesn't come in the exact format that we want always, so I can actually click here and see all of the columns and the data that is coming in hand as you can see the date, for example, comes in a date- time format with includes the time, now time can be important in some situations but most accounting situations we don't care about at what time during the day things happen so everything is of course at 12 am and we would like to remove that out of there.

You can see that nothing has currency in here, it would be nice to see everything in dollars, and if you look further you can see that the data that we want to take a look out is negative because I happen to turn it around so credit negative, we want to see positive.

What I'm going to do now is create 3 columns: I’m going to create a column for the budget, I'm going to create a column for the sales retail and one that has fixed to date. I can manipulate this data set, this is power BI I didn't want to spend a whole lot of time in here but I realize this is important because I otherwise I can't really show a nice final example so I'll go for it.

New column here, create a new column and I'm going to call that Quarter and this is going to be the date quarter so like instead of using this format over here we want to convert that, so I’m going to format this date; and if you are wondering what commands I’m using and how I'm setting this up, these are the same commands you can use in excel its a language called DAX; I’m going to format this and the value is going to be date, so I actually have to format it twice, and then the year of this particular date over here, and how your reference fields inside the scanner here is by bracket so, date, and then month.

This is if you have worked with docs, this is the standard way of actually converting things from having time on them into not having time in them so basically, you have to deconstruct the date into this. I actually want to present this with first the years, then the months then the days, there is a reason for that and I’ll show you guys that in a minute.

My new column over here now looks like this 2018 11, 2018 41 etc, so it's nice to lay it out. It's much better than it comes straight out of NAV like that. I'm going to create a new column as well and this one is going to be called sales and that is going to be equal to negative and currency, I can use the currency function and I'm going to use net change. And then I've turned around and made it into dollars. Which is nice. Then I am going to create another column called budget, you can imagine this is just like working with excel where I'm actually referencing column in excel to create new columns.

Now I have created all of that, and I see a budget and I have the date correctly in. ok. So now what I want to do is go in to create a visual right here, and I’ll just pick one of these column views here, and for the axis, I’m actually going to have the KPI name and for values, I’m going to do net change actually.

So now it's displaying everything for all of the accounts schedules, so I just want to look my accounts schedule. I can actually go in here and filter the account schedule, I get a list of all of them right here and check them off. So you can see that works fine. Actually, I didn’t want that, I wanted my sales here, so now you can see I have my sales and let's see If I can get my budget, now I have a sales and budget, now Which period am I looking at.

We are actually going to create another visual, which is going to be a filter and I’m going to show quartet like that and now I can filter on the first quarter or the second quarter where I Just have to remember I only have budgets but no sales, but here I do have sales I can go in here and see data and it does show me the sales and the budget from NAV correctly setup.

I know this was a little bit of an exercise in power BI as well as connecting it to NAV, I think I’m going to conclude in the account schedules topic with this, who knows I might pick it up again, but I think next will be analysis views.
I hope you learned something from this and enjoyed it.