Dynamics 365 Business Central – Basic Jet Reports

Dynamics 365 Business Central – How to Create a Basic Jet Report

Hello and welcome to TMC’s “How-to” demo series for Dynamics 365 Business Central where we explore the features of Business Central for basic tasks within your organization. If you can’t find a demo you’re looking for let us know in the comments below and we’ll try to make it happen.

Today we’re going to create a few basic jet reports, we’re going to look at customer sales and inventory. My name is John Hoyt, Solutions Specialist for TMC. Let’s get started. In this video we’ll cover the basics in creating a few simple jet reports we’ll do a:

Customer aging report
A sales transaction report
Inventory analysis report

All of this will show that creating reports in. Jet is fast and efficient. Let’s see how this works so we’ll start by just opening up excel because that’s where jet really shines. It’s an interface between my data in the background and the excel interface. I’m going to use to create the reports so i have a jet tab that gets inserted into your excel and I’m just going to use the report wizard here it’ll simplify the process of building our reports now the first thing i need to do is specify what data i want to work with. In this case I want to work with my customer information so i can just scroll down through all the different tables and views and find my customers then I’ll use the next button and I’ll select the fields that i want to work with.

Now the place to start of course is going to be with my customer name and my customer number and you can see as i put the check mark. Next to the box of the data field they’ll show up over here in the fields I’m going to use I’ll grab the customer balance. Let’s see the aging buckets are up at the top here and I’ll grab one two three and four aging buckets and then i want to do a little bit of analysis so let’s grab some recent activity as well. I’ll come down here to the last sections and let’s grab the last payment amount and date and the last transaction amount and date and then i can simply click finish and I’m done with the wizard jet reports now builds out in a preview mode or a design mode. This is kind of what the first records and the headers for my columns are going to look like and the first thing i want to do is make some corrections to some of my formatting so column M and column O – these are my date fields.

So, let’s format those to become dates and then I’ve got column N here and my aging buckets and balance these all need to be set up as dollars of course and then once I’ve got those formatted if I return to the jet tab on the tools and click run it’ll create that report for me and just that quick just that simple, I can put together an aging report. Now at this point of course everything I want to do takes over and it becomes all excel so from that customer aging report let’s move on and let’s look at a sales transaction report I can simply go back to jet and say I want to create you know new tab, new field and I’ll start a new report here. Now in this case I’m going to just type in the letter S and jump to that part of my data and i come down and i want to grab my sales transaction information and just like before i’ll start with my customer’s name and number, so my customer number comes first followed by my customer name.

Now in this database the documents themselves are known as sales orders so i need to come down here and I’m going to grab my sop number and my SOP name all right SOP number will be the document number SOP type will give me quotes or orders or invoices or things like that and let’s grab some document information so from here I’ll grab the document amount document date and the document status now. If i instead of clicking finish and just building the report based solely on the columns. If I click on next, I can start to group things, move pieces together. So in this case maybe I’ll group everybody by the customer number. I’m going to sort by maybe the document dates and I’m going to create some summary fields I’ve got document amount. Let’s see what the totals are for those document amounts, now when I click finish again I go into that design mode and I see a bit of information how the report’s going to look, come back to the jet tab, click run, now it goes and looks through the data, pulls all together, creates the report for me and now I’ve got under one customer number all the transactions of all the different types. Again, I could go through and make sure I’ve got things formatted properly but that’s the real beauty with the jet reports tool is that I just do standard excel from this point forward there really isn’t anything else I need to learn.

For the last report let’s do an inventory analysis report, we’ll run that same process again report wizard. Now in this case I want to work with my item quantities so I’ll go down all right there we go my item quantities. Here I want to work with the item numbers, so let me grab my item number and my item description I’ll take some of those last pieces so let’s see my last order date, my last order quantity, and maybe my last receipt cost and I’ve got multiple warehouses so I want to know which location got each one of those I’ll keep looking the number of receipts over time for that particular item how many times have i received something and then finally let’s grab some quantity, what’s quantity available, what’s my quantity on hand, and maybe my quantity sold. Same thing I can grow through the next and group everything by the item say description. I want to sort everything maybe by the location codes I’ve got a whole bunch of totals. What I really want to see is the only the total for maybe sold could add in filters now which would look for a specific value so maybe I could look for just a specific location code or something if I’m done I can just click finish again we’ll come back to that jet tab and run the report.

So as it’s finishing its run through the data we should finally get a view of that report here. And here i get my report item by item all the various quantities the current available quantities etc. Now I can use this of course as my raw data for example to build a pivot table or a pivot chart or i want to do some other type of analysis just based upon the data that I’ve been able to grab and that again is the real strength of jet reports it simplifies the process of getting to the data that I want.

Once I get past that hurdle I’m connected to my data. Now I can build the report or the analysis or the statement, whatever it is that i need to build. So in this video we’ve covered the basics in creating a jet report, we created a customer aging report, a sales transaction report, and a quick inventory analysis report. Again just trying to demonstrate how easy it is to build reports with jet. That wraps up this video if you have any questions would like to make a suggestion on what we should cover in the next video please comment down below I’ll do my best to answer your comments and if you

Need any immediate technical support I invite you to visit our website www.abouttmc.com . Also please don’t forget to like this video and subscribe to our channel.