Dynamics 365 Business Central: Happy Thanksgiving! Microsoft Stack Continued

Dynamics 365 Business Central: Happy Thanksgiving! Microsoft Stack Continued

Happy Thanksgiving we're here in freezing in New York City, we're going to keep going on the Power BI, just stepped outside a little bit to get some of the greenery and the cow into this shot, so we're not in front of the whiteboard, we don't need to because we're just going to do some cool stuff and Power BI connecting tables, there's a lot of stuff to do but, you know, it's too cold to be out here so let's get upstairs and into the office and continue.

Let's keep going, so we have the item list, we fix the item category code, we have quantity on hand looking really good. Let's get into Power BI and take a look at our table, so why did I want to fix the item category code to have everything populated? Well, the reason is we might want to do reporting on the item category. So, if I go into pie chart, right now it's putting a pie chart of the quantities available or the inventory based on each one of the items. It's just really hard to read this, obviously we have a lot of paper coffee cups but we would rather see it by item, so if I go into values, take that out, I want to take out the number, so the values, I'd like to put in the inventory values and in details I'm going to put the item category. Now I can see I have by cups, we have a lot of cups or cup related things, this would be coffee and cups, we have miscellaneous being a lot less, table and here.

Here we have a chart for inventory by item category, no not very useful because why would I want to compare how many chairs I have compared to how many cups I have? but at least it gives you a visualization, and I can go back into my list, so this is the data that it's taken it's just looking at in category and inventory. What if we want to see something more, let's go ahead and take a look at what else we have available. These are the fields that I can pick out of and this is in the items data stream and this is basically the item list and we can see here that we don't have many values or totals, so I would be interesting for me to see sales. How can we get the item sales and item purchases for a timeline?

Let's play around with this a little bit, so this is obviously not the correct data stream for that, so we're going to get some more data, we're going to go and get data hit more here, find our Business Central data stream, connect to it, and let's see what we have available as well. We get into coffee mugs company and we have all this stuff here, so where is this coming from? this does not look like tables and if you're familiar with databases and querying databases, you would want to see tables here or at least it would expect to see tables here, but these are not tables, what are these? If I go back here into Business Central and I look up something called Web Services, I get a list of web services right here, and this list should look a little bit familiar to you if you read really fast but basically the web service list right here, the pages are the things that are available to you in Power BI.

So, it's looking at web services and for example, here items, which I already picked which is this item list over here, if you go into a Business Central, is the items page right here so we have the item card, we have the item page, etc. If we wanted to expose transactions or at least get the transactions, we have something called item ledger entries here, a query, so let's see if Power BI sees that. This is clearly ordered by alphabetical order and I can see that it does show me the query right here, item ledger entries. Let's just go back and make sure that is correct, item ledger entries, it's showing us item ledger entries. Okay so that's awesome, what I want to do is pick that, so what do I get with item ledger entries? Okay so, this one has been it's obsolete so what can we do? well, this is a real-life example, we're going to just create one.

No problem we'll get in here and create a new, in this case, what I'm going to do is create a reference to the item ledger entry page. I just hit new right here and I get a new line and I'll click on page; you might actually have this experience, in this case, this web service has been made obsolete by probably an update, which is interesting we probably did get an email about that; so here what I'm doing is, I'm looking into all the pages in the system to create a new web service and I want to find the item ledger entries and I know that item ledger entries is a page, so here we go 38, hit OK on that. For people who don't know that, item ledger entries is the page that shows the detail or the transactional detail for an item and I call it ILE. I published that, so now when I want to publish the item ledger entries like that, it gives me a string here, I should do that in a minute or at least when I get out of the records right and I have now given access to the item let your entries for authenticated users out of other applications such as Power BI. If I close that out, I can now get into Power BI.

I go into Business Central and if everything worked out fine I should see ILE, connect, it gives me the companies, I go into coffee mugs and now if I look through it, right here ILE. The great thing about seeing the Item Ledger Entries is that we have some sales and purchasing data and what I'm mostly interested in is the sales data, right here I have both items and item ledger entries. Let me just go ahead and create a new box, open up the item ledger entries. What I want to see is the item number which and then I'd like to see the sales amount actual. These are the total sales for these items and here I'm able to get much more than I got out of the item list, I could already see the inventory because the item list was restricted to only show inventory. Now I can see total sales, also what's very interesting about this is that I picked the item number and the sales amount actual and it brought the total amount for the sales amount for its item, but behind each transaction here, there are a number of transactions, behind each one of this line of this row, we have number of tracks they're automatically being summed up.

If I take a look at this table, I can do that by going in here, this is the item ledger entry table. As you can see here, it shows all the purchase receipts that we have, we show the item, etc. Here we see the cost and here we should see the sales somewhere right here, sales actual. If I drill down further, we should see some sales amount, it's probably sorting by cost and then I have sales. So, automatically the system is summing it up for me. Now, if I go ahead and show this as a pie chart; like we did the other data; now this makes much more sense to view as pie chart, this is basically showing me the biggest seller, the biggest seller is 1996 S. We can also show it as a treemap, which I really like, so you can see here the sales by each one and again 1996 is the biggest but a close second is 1896.

Now since we've set this up by the item ledger entries, we would like to see of course the name of the product unless you remember all these codes, which most people probably don't, it would be nice to see the name and as you notice in this particular list of the Item Ledger Entries the description does not exist, so we'll leave that for next video how to fix that and set that up.