Role Center Charts in Dynamics NAV 2017

Role Center Charts in Dynamics NAV 2017 - Part 3

(Transcript)

We are continuing on our chart adventure. This is the third video in this theme. So, in the first two, I mean the first one, we actually created this chart using the generic charts and changed the role center to include this, and this is showing the customer balance by customer number; and it’s based on a table.

So, the second one we actually did look into how to convert lists to charts and vice versa, but this one we are going to continue on this generic chart path and answer a question we posed in the first demo which was: How do we deal with multiple tables inside a chart?

What Will Be Demonstrated
If you have one table like this case, we actually are working with the customer ledger entries and you get all the data out of that one table, you can display that here and it's fairly easy to do. Just attach that table to the generic chart. But If you want to get data from multiple tables, it becomes a little bit more complicated and you have to use something called a Query and this is what I'm going to demonstrate today.

The problem I have is: on top of showing of course the customer balance here, and this is only showing Peter Saddow’s. I would like to see the customer—I mean the balances for all the customers per salesperson and I want to see the salesperson name down here [points at the x-axis]. I would not like to see their code, I want to see their name.

And this is a problem for two tables, because I cannot get the customer name out of the customer ledger entries. So, the salesperson name comes from the salesperson table and the balances come from the customer ledger entries table, and so these are two tables that I have to combine together. And that’s what I’m going to do right now.

Table VS Query
So, if you don't have any programing experiences, fear not, this will be very simple. Although it will be on the verge of some programing so don’t shut me off right away [giggles]. Ok, so what we’re going to do is, I’m going to just navigate here into the charts; And I have a list of charts here.

And if you remember from the first video; we have charts here that start with Q; those are Queries, and Charts that start with T; those are tables. So, if you have table then you are only working with one table and that's very simple, but if you have a Query it gets slightly more complicated.

NAV Development Environment
Now how do we build a query (make our own query)? In order to do that we actually have to go into the development environment for NAV. Again, this is not going to be development, it’s more just building a query. So, there is no real programing involved, even though we are in the development environment. And I clicked here into the NAV development environment, this should have installed; if you installed the full client, with everything on the computer, you should have a development environment with you.

If you come in here, you can see that you have a list of all objects are in the system; tables, pages, reports, etc. We are only concerned with something called a Query, which is probably the simplest object in there.

Creating A New Query
So, in here we have a list of all queries (and some of them you see in the generic charts are being used there) and we would like to create a new query, right here [clicks on new]. so, I just create a new one and I would like to use— So actually it brings up this new query window or query designer, and it asks me, ok, so what do you want to put in here?

And I’m ready with the data item. Data item just means table in our case. So, yeah, I want to actually pick the salesperson table and it’s table 13: Salesperson/Purchaser, in Navision or NAV we have the salesperson and the purchaser in the same table, that's ok.

Then I go to the next line, and it asks me, ok, you want a table? That’s fine, give me a column; and you pick columns out of the table. So, you start with a “code”, I want to see a code, and I also want to see the “Name”. The name is maybe more important for me because I want to display the name, but I like to have the code in the data stream that’s coming as well. And then I go to the third one and it says, “ok, any more columns?” I’m going like, “no, this is good. I want to change this to another table.”

Ok, now we have a table underneath that table, so they are connected together, you can see that form the indentation [points out the indentation], and I'm going to pick here the customer ledger entries table, ok. And It says, “ok, which columns do you want for there?” I would like to get the remaining amount in the local currency; that’s really all I need for there.

Ok, Great! So, what is left? What do I have? I have— I’m getting the Salesperson/Purchaser table, these two columns out of there [points to Code and Name], and then the Customer Ledger Entry table, I’m getting the remaining amount.

Ok, what we want to do is sum up the remaining amount, because I want to sum it up for all of the codes. And the method type comes in here, I say, “totals, give me totals”. It automatically knows, then, it needs to group by the code and the name; and it checks that off.

Tell the Query How the Tables Connect Together
Alright, there is one more thing I need to do. I need to tell it how the customer ledger entry and the Salesperson/ Purchaser table connect together. So, I go into here [the ribbon] and I go into view properties.

So, I’m looking at the properties for the customer ledger entry table. In here there is something called the data item link. I go into that and I say that the salesperson—I look for the sales person—code field in the customer ledger entry table, connects to the salesperson table and the code field inside of there.

So, the code field in the salesperson table, connects to the salesperson code in the customer ledger entry table. That's how they connect together, alright. Now I’ve defined what is called a relationship or a data item link in NAV.

Save The Query
So, I close that out, and I want to save this, so I’m going to save this as 5001. And this is going to be, “Customer Balance by Salesperson” [types save as name] and I’m actually going to copy that because I’ll need it again. Hit ok, close this out.

Create a New Chart
Now I have that here, 50001 Customer Balance by Salesperson. And if I go back into the client into my generic chart list here, I can create a new chart. And I’m going to use customer— actually, the ID is going to be the Customer Balance by Salesperson (CBSP), the name however, is going to be Customer Balance by Salesperson.

And the source type here is not going to be table like we did before but a Query. And the source ID…and look at this, I actually look up now and I go down and I see my new query here, 50001.

I pick that up and now I go into the required measure for Y-axis, and I want the remaining amount; that's what we are summing up. It comes up with a funky name here, it's basically coming straight from the query but we call this “balance” in normal English. So, I just put that there [types in the data point label field].

And…over here in the axis field, x-axis field, I’m going to pick the name (query column) because I’d like to see the name of the salesperson. Codes are great but names are better. And the chart description, pick the same description I had before, and then that’s it. So, now I hit ok.

Adding Chart to the Role Center
And I want to add this into the Role Centre so: (1) I go back into the role center, (2) go into customize, (3) customize this page, (4) and I add the chart part, (5) customize the part, (6) customer balance by sales person, (7) hit ok.

And now we can see I have the customer balance by sales people. I have all the sales people here, and I can quickly see that we have John Roberts with 112,000 and Peter Saddow with 946,000; everybody else has nothing. So not much going on in the sales department or like everybody is really good at collecting except two, something like that.

Now we have a query connected to a chart in the role center. This is actually really handy, it’s easy to do, and I recommend everybody to start using this.