Create a Power BI report in Microsoft D365 Business Central
I might dip into NAV 2018 again because of course Business Central is only in the cloud, NAV 2018 is still on-prem as well, a lot of people are using that. And It looks quite different so, not to alienate anybody, I might actually dive into NAV 2018 as well although is hard to go back because obviously Business Central is the new technology and we should always be striving towards getting to that, but yeah it will take a little time for everybody to catch up to what I'm demoing.
What I want to do today is go into Power BI and Business Central, so Right here we have Power BI reports on dashboards, just like we have in NAV 2018, we have this Power BI web parts hanging out here, you can select reports and if you wanted to change the report that comes in here, in 2018 it was a little bit of finagling but in Business central is its super easy.
Create a Report
I'm going to create a report that I can show here, I want to see the sales by item category, that what I'm interested in seeing. So, it gets slightly technical while I'm going through this, but I'm going to go through it fast and I'm sure you get it. Anyways, in power BI we can log into the Power BI app.
If you log under the same login as you logged into business central, you can basically activate the app. App power BI .com and you get this work space. On top of that, you can download the desktop version of Power BI. I have done that, and the desktop version of Power BI comes up here, this is where your development happens, where you can create your own reports. Don’t get scared, it's not that hard, basically in here what I need to do is I need to get data.
I can come in here and say, get data. And it brings me up this list here, I can start typing Business Central. Here I can connect to Business Central, there is a specific data stream for Business Central. I don’t need to do any old data connection any strange string, any query string into the database with a sequel and stuff like that. No, just this. So right now, you just log in, this comes up here and its who's me all the databases that I have here and I'm just going to look at Cronus USA. So, in here I want to see the item ledger entries.
Here I get a sample of that, I just check it of right here and say load. Basically, Item ledger entries are all the transactions in the system and now it's basically bringing those entries in. Let's look at that. Over here you can see that I have all the fields from the item ledger entries. All the fields are exposed, I want to bring in fields over here. I want to bring in the item category code and bring in the sales amount. You can see here we have the item categories, I have chairs and tables and I have the sales. The item category has nothing; we should fix that; total sales here is 677,000. This is basically it.
I don’t want to see it as list, I want to see It as a pie chart. And I'm going to make it really big, there is a reason for this. Because when it pops into the screen here on the bottom it is very small. It assumes that you used the entire frame. So, I'm Going to make it big. Also, this is a bad title, so I'm going to fix the title. Let's go ahead and look at the legend, I'm going to change that.
Title, I'm going to call this Sales by item category and I'm going to make the text much bigger. Also, if you see the detail labels, I'm going to make them much bigger. Save it, call it test and then I'm going to publish it. Now it's publishing to the cloud. Success! great, now I go into the cloud right here and go into my work space, look at my reports and now I get my test report here. Click on that and I get my categories.
It's in the cloud, I just did this, it took me no time at all, if I go into here into the Business Central and I go and look at what I have here, go to select the report, and there it is! Test, I enable it, ok, and look at that! It's there, that how easy it was, I just created a power BI report and put it into the dashboard and selected it. How crazy is this? really cool, no programming. Just clicking.