Jet Reports Financial Reporting for Dynamics GP

Jet Reports Financial Reporting for Dynamics GP

During this recorded webinar, learn about Jet Reports Financial Reporting for Dynamics GP.  Jet Reports is an Excel add-in that can be used to replace Management Reporter, so if you know Excel then you know Jet! Jet Reports is primarily used for Financial Statement presentation.



Topics discussed during this session include:

• Introduction and Agenda – 0:45
• Excel Knowledge – 1:30
• Jet Reports Overview – 3:50
• Jet Reports Demo Begins – 6:15
• How to Set Up Data Source – 6:40
• Report Options – 9:15
• Income Statement – 13:00
• Departments – 23:40
• Q&A – 26:20

The intended audience for this video are existing Dynamics GP users.

Contact us if you have any questions about Jet Reports for Dynamics GP.

Upcoming Dynamics GP Coffee Break webinars:

Dynamics GP Coffee Break | Multicurrency on November 16th

• How to revalue Control Accounts denominated in a foreign currency
• Discuss an issue users encounter with realized and unrealized gains and losses
• Register Here

Dynamics GP Coffee Break | Account Structuring – Chart of Accounts on December 7th

• How to plan out a change to your Chart of Accounts
• The tools and options available to assist you
• Register Here

Transcript below:

Tess: Hello, everyone, and thank you for joining us today for our first webinar in the Dynamics GP Coffee Break Jet Reports Financial Reporting. My name is Tess and I’m the facilitator today. If you have any questions throughout the presentation, you can type them into the questions area in the control panel on the right-hand side of your screen and we’ll answer your questions at the end. This session will be recorded and will be available either later today or tomorrow on our blog. So now I’d like to introduce our presenter today, Don McNulty, who is a solutions architect on our Dynamics GP team at Encore. So, Don, over to you.

Don: Hello all. So today we’re going to talk about Jet Reports, a little bit of background on Jet Reports. It’s an Excel add-in and it can be used to replace Management Reporter. I’ll show a little bit of that today. Primarily, what it’s got used for is for financial statement presentation but can also be used for other things as you move forward. It is a robust program that runs inside Excel, so it’s a program that you already know, and most of you use all the time. So from there, we can move forward on that.

So today’s agenda is basically the introductions, which we’ve just done, Excel knowledge, and some Jet Reports. So on the Excel side, there’s a few things that I am going to assume that you already know at this point and that you need to know in order to run Jet effectively. The first is absolute/relative reference. Absolute/relative referencing is the dollar sign that you put around A1 to be dollar A1 so that you can lock down A when you copy and paste it. I’ll be using that quite extensively today. So, hopefully, you already understand that. Most of us do. Named ranges. We can name a range or a cell inside Excel so that it becomes a custom cell that you can then reference without having to worry about it being on a certain page or where it is. It will always bring up the correct information, and I’ll be using that in order to streamline and make the formulas more readable. Concatenation. Concatenation is used within Jet in order to allow us to take multiple cells and bring them into a single cell so that…We need a single cell link in order to link the formulas properly. The Concatenation allows us to have two different cells such as the fiscal year and the fiscal period of two separate cells and bring them together in a single cell.

The last piece on this is the linking of cells and sheets. This is fairly straightforward. This is allowing you to put information on one worksheet inside a workbook and linking it to other cells on other sheets so that it can be used exclusively across the board. The objective here is to make the same kind of report that you currently get from say a Management Reporter or any financial statement generator where you key in things like the fiscal year and the period and all of your reports then change based on that one entry so that you don’t have to go through 15 different spreadsheets to update them. So that’s the Excel side. It is fairly straightforward. Everything else…The standard Excel functionality is always available as well as all of the standard Excel formatting. So I’m not gonna do a fancy format on this, but know that you can bold things and put them in boxes and underline them and do whatever you need just using the Excel borders and things like that that you currently do for any of your reports that you do in Excel.

So the next thing I’m gonna talk about is Jet Reports itself, and these are the things I’ll be going through during the demo. So, first off, there’s the setup, and the setup of Jet, once you’ve downloaded the program from Jet Reports online, you can load it up. It’s an Excel add-in and out of the box, without registration keys, it will do everything that I’m doing today. I am not using any functionality today that is not in the basic piece. The basic and the…I will notice a couple of things. There’s a few small differences between basic and full. Full allows you more. You can schedule the report as well as do some [inaudible 00:04:37]. So we’ll be adding it to Excel there.

Then I’m gonna show you what the ODBC link looks like so that you can set it up so that you can look at it. This works on an on-premise basis. The ability for you to be able to handle it is if your Excel worksheet needs to be on the same network as your SQL database. That way, your performance will be good. If you’re running GP in the cloud, then you would be running an RDS server in the cloud in order to access GP, and that is where you would run Jet from would be off in Excel on that RDS server. There are a large number of reports that are available from Jet out-of-the-box. I think there’s about 75 of them of which I believe 20 or 25 of them are available for basic. The rest are more advanced, and you can use those, and all you need to do is change the ODBC driver and they should operate directly. They run off of things like category and things like that.

I’m gonna show a quick financial statement. That’s what I’m going to work on today. That quick financial statement is going to be the gross profit for Fabrikam, which is a Microsoft Dynamics GP’s test company. We’ll do that. And I’ll be using the Options page in order to drive changes onto three separate sheets. Okay. So that’s kind of where we’re headed for today.

All right. So demo. All right. So we start with Excel. So here’s my Excel, and what happens once you load Jet is that you get a new icon at the top called Jet. When I hit Jet, what we get is we get a number of different features and functions that are listed across the top. Over here on the Settings, when you first start, you need to set up your data source. And when we set up the data source, what we do is we give it a name. I called it Jet Demo, we type in “Jet Dynamics GP.” This also works for Business Central but I’ll be demoing it in the GP cell. We then select Authentication. Now because mine is in a demo environment, I would use database authentication. Most people would prefer to use Windows authentication. The database authentication, I’m using SA and then I type in the password. The password is stored in here. It’s in an encrypted format, so you cannot read it from this. So if you do use SA, it’s not exposed on different works stations.

Underneath the connection we tell it what server it is, mine is the desktop because I’m running it on a virtual machine. We then give it our Dynamics database. This usually says the word “dynamics.” I have multiple Dynamics databases in order to provide demos on DP18, DP16 and DP15 as well. And then we select from inside here. It looks up the different companies that I have set up when I have Fabrikam multi-bin as well as Fabrikam. The display, there’s some features inside here where you can have some table counts and friendly names and things like that. This is more for the professional side, so it’s not really important to fill these in. I’ve got mine filled in this far.

And then inside here, what do I wanna do? Are my table and field names case-sensitive? And no, the answer is no. We don’t worry about that. We trim trailing spaces from text fields so that we get rid of any blank space data, and then blank filters match with null values. So if I wanna…You can’t put null in Excel. You can only put a blank. So when you do a filter on this, if you want to filter it to blank, it also wants to pick up anything that is null, which is empty in SQL. So that is what it looks like from there. We then hit the Test Connection and it should test fine like so. And then once that’s done, we can hit okay. And at this point, we can now…This will then give a list here of the company so I can change my company on the fly. And this one here shows which is the ODBC that I’m using. So that’s basically the setup for GP.

Let’s see here. Okay. Okay. So existing reports. So I’ve got three sheets on this list and I’ve got an Options page. So I’m gonna talk a bit about the Options page, about how to use them. So the challenge that people face is that we’ve got companies, you got multiple companies inside your environment each with multiple databases. You have different fiscal years, you have different current periods, and you have different budgets, and you have different start and end dates. Now Jet Reports can be done on a day-by-day basis. In other words, if you need weekly reports or biweekly reports, you can get them using Jet. Unlike Management Reporter, it is not strictly period-based. We can do either period-based transactions or we can do date-based transactions, the difference being how fast the system will run. So in the case of a date-based system, a date-based piece, we’ll look at every single detailed transactions and summarize up the number for you if you use a period base that uses the summary tables inside GP for a faster report. I will be using period-based today, but know that you can use the date-based.

So what I’ve done here is I’ve basically set up like six different fields. These fields are company fiscal year, “Currper” is for current period, the budget ID, the start date, and the end date. So what I’m gonna do here is that beside each of these, I have selected this and I have typed in the word Fabrikam. But most importantly, what I have done is you’ll notice up in the top left-hand corner I have named that range Comp, C-O-M-P. And the reason I’ve done that is so that when I need to bring up a company anywhere on my workbook, I can just type in C-O-M-P and it will automatically bring up this cell. I’ve done the same thing for fiscal year, and I’ve called it “Fyear” for fiscal year. The current period is set to Current Period. Budget ID is Budget ID, and then Start and End. This is the naming of name ranges.

Now to do that very effectively and easily, I’m just gonna type in here. I’m gonna go down here. I’m gonna type in report date. And inside here, I’m going to put in April 30, 2027. Now, if I hit Return right now, what’s gonna happen is this is going to change itself to a date field, i.e., 04/30/27. So in order to ensure that I’m going to use…I want it to be text. I’m putting a quote mark in front, which makes it a text field inside Excel. Now, this is not named yet. So I’m gonna call this “repdate.” All I do is I just type up to here. I just type in the word “repdate.” And when I hit Enter, it’s now named as “repdate.” To edit those, you can go to your Formulas page and there is a Name Manager inside here, and you can see there they all are, and these will then show you what page they’re on and what cell they refer to, okay? And then you can change these or edit them as required.

So having done this page here, I’m now ready to move on to the actual spreadsheet that I’m gonna be working on. So I’ve got three of them here. I’ve got an income statement, a department and I’ve got a division. These are gonna be my division. So when I go to my income statement quickly, what I’ve got here is a list of all the GL codes that are in the revenue and all the GL codes that are cost of goods and a gross profit. Now you’ll notice that up top here, all of my revenues are negative and all of my cost of goods are positive. So in order to make this work the way you would want this, in order to make this positive, I’m gonna put a negative sign in front here. Come on, don’t be slow on me. So this is calculating…I’m not sure why it did this. It didn’t do it 20 minutes ago. So, what happens is this is gonna change the sign.

So we have to build the formulas around what we know. So inside here what we have is, since we have the account structure, we have the account number, so I have a department, an account, and a division. So, let me just see if I can free up some resources. There we go. So as you can see, it’s now changed. So this inside here, what we have is different pieces inside here. So this is the total gross profit, $252,000, which if I was to change all of these to negative, this number down at the bottom would be that $244,624.96 less the amount that’s in here would equal the 252. I am picking up everything inside this range, 4,100 to 4,800. And you’ll notice down below it I have 4,000 to 4,109 and then 4,110 all the way through to 4,500 and then all the way down to 4,800. So these are all the GL codes that are in my financial system. So these are accounts inside Jet.

So what I’ve got here is I’m looking at this going, “I need to pull up this cell.” So this cell here is my balance. This is a balance that’s coming through and it looks complicated but it’s really not that bad. So the first thing we’ve got to do is we’ve got to have a date with it. What is it that we want the formulas to do? This is the same as what you do in your Management Reporter under Column Specs. So up top here, I have a from and a to. Now, inside here, I have two periods. I have period number one, and I have period number four, but number four is actually listed as current period. You remember on the front page underneath Options I have Fyear and Current Period. So inside here, what I’m doing is I’m taking the fiscal year, which is 2027, I’m adding to it or concatenating, a slash, and then I’m adding the period after number four. And on this one here, I want to have year to date. So I’m gonna say this is a year-to-date column. If I wanted a current month, I could type in “Currper” here and it would change it to only April. So I want from period one to period four. Those are my from and my to for my dates. That’s my fiscal periods.

I’ve got my account here. I want all departments and all divisions, okay? So inside my formula here, I could type this, but you go, “Well, there’s no way I’m ever gonna remember that.” So what we do is we use the Jet GL function. So we highlight the cell that we want to use and we bring up the Jet GL function. So inside this Jet GL function, what we do is we say what is it that we want? A cell, a row, a column, or a sheet? I’ll be talking about columns and sheets in a second. I won’t talk about rows today, but this particular one is gonna remain as a cell. So in other words, in this cell, this is what I want. What do I want? Well, inside there, I have a choice. I have a balance. I can get a budget. I can get account name, or I can get a category name. In this particular case, I want to know the balance. I want to know what the actual balance is for that account. Start period. You’ll notice here it says 2027/1. This is M3. M3 is listed up to here. So it’s pointing up to itself, and you’ll notice that I locked down the 3 so that when I copy it down the sheet, it will remain at three, and when I copy it to the right for additional columns, it will change so that I can use this as a single cell. Once I’ve written this cell once, I can copy it to everywhere down below. And I do the same thing for the end period, the end period there.

As we scroll down, we come down and then we say segments. So these segments are the segments inside your chart of accounts. In my structure and my count, I have a three-digit segment followed by a four-digit segment followed by a two-digit segment, department, account, and division. So D10 is looking to here. So it’s always going to the star and you’ll notice that the D is solid so that when I copy it to the right it’ll copy properly, E10 and F10. So I’m looking at this, this, and this. And then if I scroll down a little farther, you’ll see that I have the word C-O-M-P here, and that’s going back to the front page for the Options to the company so that if I decide I wanted to do my multi-bin version instead, instead of having to change each formula for this, all I would need to do is go to the Options and change the name on the company and it would change all my spreadsheets. When I hit Okay for that, it goes out to GP or out to the GP database and brings in that number. So now that I’ve written this formula, I can copy it. I can copy it from there and I can copy it all the way down, which is what I did here. I just copied it all the way down to the bottom. These are all exactly the same formulas. The M’s are changing accordingly, and the rows are changing, but everything down here is exactly the same, including the ones down here. The revenue, of course, is just a cell, just as normal. So this is what an income statement would look like.

So at this point, I now have my year to date. So at the top here, I can type in “YTD,” and maybe I wanna put underneath here a date. I can go “=repdate,” like so, and it will bring up that rep date that I created on the Options page. So now when I go back and change this to May, I can change that rep date to May and it would automatically change the underlying description at this point. If I wanted a month inside here, all I have to do is I can grab this column and copy, and all I have to do is paste it into here. There it is there. There it is. It fits. You’ll notice that the numbers have come through. The formulas are still working properly. And then at the top here, I’m gonna change this to month to date, like so, and now if I want a month to date, instead of putting in period one here, I can go “Currper,” and now you’ll notice that these numbers have automatically changed and they’ve gone down. So this is what happened in April, and this is what happened from January to April.

Now, other things that we can do quite quickly, we can copy this again. We can compare it to last year, like so, and in that case, if we wanted to compare it to last year, what we need is fiscal year minus one. And this one is fiscal year minus one. Here we are. So now I’ve got comparatives. If I wanted to do last month or whatever, I can do the pluses and the minuses against either the fiscal year or against the current period. So if I want last month, I can go current period minus one. Issues with that occur when you are on period one, if you go one minus one is zero. There is no such thing as zero period. You’d need to then change the year and the date. You’d have to write some IF statements to know what that is. But ultimately, this is what you can do. So I can build as many columns out here as I need to report for the information that I need. And obviously, if I wanted to know the percentage increase or decrease, that is strictly just an Excel formula. So we don’t have anything to worry about from that point of view.

So we can do this across this. So what I wanted to then talk about…so that’s what you would do when you move down. So in these cells inside here, we can do ranges. We can do individual numbers. We can also add two together. So if I wanted to add 4,040 and I wanted to add 4,180 to it, all I need to do inside here is you use the pipe and you go 4,180, and now that 70 has now been included in there and this 349 is now 70 plus what it was before. Obviously, that means that I would then have to remove my U.S. sales discounts because it would be duplicated, but that’s a matter of how you wanna set it up. And you can mix and match those. So you can add those to whatever you wish. I’m gonna take that back out, like so, and it brings it back to where it was there. You’ll see that the numbers in front of you are changing. They change as you write these sheets so that you can make sure that you’re in balance and that they are matching up to what you expect from your financial statements.

Departments. So inside here when I do departments, what I have inside here is I have the individual accounts. This is all of the departments, and now I’ve got this. So the only change in this formula is that instead of looking over to column D for my segment number one, I’m going to N5. Now, inside here, what I’ve done, I’m just gonna cancel, I’ve done is I’ve said this particular one is looking to a column. It’s looking at segment one and it’s picking up M5, which is this star right here, and then it’s picking up the company. So when I run this and I actually go into my Run piece here, what will happen is that it will run through this and it will expand out my workbooks. So this particular one running a column is gonna give me the individual accounts total here and then it’s gonna give me a column for each of my departments side-by-side, okay? And this one takes a couple of seconds. There we go. Expands the columns, the departments.

Tess: Hi, Don, it’s Tess.

Don: Yeah.

Tess: We’re coming to the end of our time, and if you wanna wrap up…

Don: Yeah, then we’ll get to any questions.

Tess: Okay, awesome.

Don: Okay. I just wanted to show this and I’ve done the same thing as well on the 00 down here. What I’ve done is I’ve asked it to give me a new sheet for each of my divisions. So as you can see at the bottom, I have all my sheets, and this was just a single cell inside here. All I did was put in a sheet version. And if you go to my departments, you’ll see here 00, 100, 200, 250, all the way up to 999, and it shows all of the different pieces. Now I don’t have a lot of data in my other departments, but it gives you an idea of what it looks like, okay? So there’s lots of things to know about Jet, but that gives you kind of a basic understanding of what it is that’s done and how we do the actual reports. So now what I’m gonna do is I’m gonna pop over to the … and questions? What have you got for me, Tess?

Tess: Awesome. Thanks, Don. So if anyone has any questions, they can enter them into the questions area, but we do have a few that have come in. So the first one is, how long does it take to implement?

Don: How long does it take to implement? Usually, what we do is the actual load of the software takes approximately one hour plus probably 10 to 15 minutes per machine that you want it to run on if you’re running it on a local workstation. Training is I do it in two four-hour stints, four hours. The first four hours gets you up and running with a financial statement. The next four is to answer the questions that you have after you’ve used it for a while. We can convert Management Reporter information as you saw. I don’t know, if you noticed, I will show on my screen quickly while we’re talking here is over here this came from Management Reporter. So I got this auto Management Reporter. Dumped it in here, and then I copy and pasted it, so I can show that’s how it’s done. So it usually takes about four hours to get the training done. After that, it’s a matter of how many reports you need to write.

Tess: Okay, awesome. And we’ll do one more question, and that is why would we use this instead of Management Reporter?

Don: Two reasons. One is it’s a lot easier to write ad hoc reporting. You can do things inside Excel that you cannot do in Management Reporter. I can add working papers and then have it tie back to my…so I’ve got a list of information inside Excel and then I tie it back to my GL. I can do that in Jet, which I can’t do with Management Reporter. The other reason is that Management Reporter has been put on notice that it will be discontinued in 2026. So at some point, you’re gonna need to get a new financial statement generator before 2026. Microsoft sunsetted Management Reporter in 2016 and gave us 10 years’ notice. We’re now five years into that 10-year program.

Tess: Okay. Awesome. Thank you, Don. So thank you. No, that’s it. That’s all that’s come in. So thank you for your time today, Don, and we hope to see everyone at our next Dynamics GP Coffee Break in November.

Don: Thanks all.

Tess: Thank you. Have a good day.

Don: Okay.

Read Next: 18 Warning Signs Your Business Need the Cloud

This post first appeared first on Encore Business Solutions.