Transcript
Introductions
0:02
Hello everyone, and welcome to today’s webinar, talking about Using Jet Reports to Improve Business Reporting.
0:13
The speakers today, myself, my name is Danusia Jolliffe. I’m the Marketing Manager here at TVision and I’ve been here nearly five years, and now looking after the webinars that we run with our partners and I’ll be passing over shortly to Rob, who’s a very, very experienced person talking about Jet and insightsoftware. Been there since 2019.
0:34
He’s also got a lot of experience with NAV and Business Central, which is obviously what we deliver for our clients. And we’ve got a lot of clients who already use Jet Reports, but I know that a number of you joining the call today want to learn a little bit more.
0:46
Rob has experience with reporting and BI solutions and helps with training, partner support, implementation. Have I missed anything out?
0:57
Nothing more to add. Okay wonderful. So, I’ll be doing today is I’ll be keeping an eye on the chatbot and any questions that come in.
1:07
So, if you do want to know anything, just proper message in there. Rob, is its OK, if I stop you as you’re going through or would you like them all at the end. That’s entirely up to you. If you’ve got a quick question you want to ask during the webinar, that’s fine.
1:18
OK, cool, so I’ll keep a note of those.
1:21
Rob, over to you.
The demo
1:27
Good stuff, thank you very much, everyone, and welcome to today’s session. We will leave some space at the end, so, feel free to ask any questions in the, in the chat box, So, I’ll just be giving a quick introduction to insightsoftware and also what Jet Reports is, we’ll run through a few slides with some examples. And just touching on a couple of specific topics, particularly about report migration, for example, if you’re using NAV at the moment. And you want to migrate later to Business Central, the fact that you can just take your Reports with you, and then we’ll just touch on some of the scheduling and distribution capabilities. And, we’ll show a quick demonstration of that, as well. So just, first, a quick introduction. So, Jet Reports is a software solution from insightsoftware. insightsoftware is a global organisation with a lot of solutions for the office of the CFO.
2:19
And just to give you some reassurance, about 17,000 of the 28,000 customers that you see listed on the left-hand side, or existing Jet Reports customers. And this is identified as a, flagship product for insightssoftware, moving forwards. Insightsoftware has a number of different software solutions. The ones which are particularly focused towards Microsoft Dynamics are Jet Reports, which is what we’re looking at today. But we also have Jet Analytics, which is a data warehouse automation product for business intelligence that more lends itself to doing some powerful analysis in Power BI. But also, we have Bizview, which is a planning, forecasting and budgeting tool as well.
3:04
So, let’s jump in with what is Jet Reports, so it’s basically an advanced reporting solution that is designed and engineered directly in Microsoft Excel.
3:16
And we basically extended the functions inside Excel, designed to pull information from your Dynamics ERP system live and it’s working very well in that direction. Because if you export information from the ERP directly out to Excel at what you end up with is an unformatted set of data. It’s disconnected from the data source. You can’t refresh it or add really much to it. The only option that you’ve got again is just to re-export the data. So, we kind of pulling information as such from Dynamics.
3:51
All of the design tools that you’ll see are using like drag and drop tools, and we’ve got some wizard to help the process, as well as having a library of pre-built Reports, so it means the business users can use this software pretty comfortably.
4:09
So, the way that Jet Reports is working is, we have a Jet function wizard where you can just simply fill out some parameters. And this translates into an Excel like formula. So, this is a formula that is not native to Excel, but it looks and behaves very much in the same way. And when we execute a Jet report, we query the data source.
4:33
Now, this can be either a direct connection to a SQL database, or it can be a connection to Business Central Public Cloud.
4:42
And ultimately, once the report is executed, the result of the calculation is return directly into Excel for us. The way the Jet Reports is licensed and sold is on a per user basis. Users can either be designers, so these are individuals that can actually put the definition of a Jet report together and actually build the formulas. A viewer is somebody who can run and refresh the report that’s already been designed, and if you are typically, would have an opportunity to specify some date filters, or whatever has been built into the report. But also, one nice thing is that we can share any Excel file that has been created with Jet Reports, with anybody that just has Excel. So, if you have a requirement, of course, you know, normally companies have requirements to send the reports out to external parties, like an accountant or an auditor, then then you can quite easily do that with the solution.
5:42
So, some of the particular topics. And I think this is quite relevant in that now a lot of new implementations of Business Central customers, generally speaking, are choosing Microsoft’s Public Cloud as the platform for running the ERP. There’s huge growth in this, in this area. But actually, when ERPs are migrated to Microsoft’s Public Cloud, we don’t necessarily get access to the entire database. So, all of the tables and fields, so Jet has specific adapters which are designed either for NAV or Business Central regardless of whether that is on premise or public cloud. And this gives us the entire access to the database. So, any table, any field, and that includes any custom fields that TVision may well have added into your database for you. And also, if you’re using an add on solution, such as Bevica, the drink solution, you have all of the tables and fields for that solution would be immediately available. And the list of tables and fields that you see in our solution, dynamically updates itself all the time.
6:50
So, if you have something added to your database later on, then this will show up automatically.
6:57
Without Jet Reports. Yeah, unfortunately, again, there is a technical requirement, let’s say, to expose any tables and fields for individual queries, and, of course, these have got to be, yeah, paid for, it requires technical skills to write extensions to expose this information. And of course, that’s got to be maintained on an ongoing basis. So, this is something quite important that our adaptors provide for, and of course uses ERP permissions. So, when we execute a Jet report, we actually enforce all of the same ERP permissions that that you have got set up on a per user basis. So, somebody can’t, for example, execute a report and pull data from the ERP that they’re not entitled to see.
7:42
So, it’s very light touch in terms of the users or the customers to set up because you don’t, for example, have to maintain a separate security model.
7:55
Rob, a question just came saying, so can a report be sent to anybody or do they have to be set up within Jet?
8:05
Yeah, so, you’ve got the ability to send out a report, for example, by e-mail, so that could be done manually or it could be done via the scheduler, and Yes, so anybody effectively could be a recipient of a Jet report. You have to be a licensed viewer in order to open up a report and to refresh the data against the live data source. But yeah, anybody can open up a report that was created with Jet, just with having Excel, but not Jet installed.
8:30
Thank you.
8:32
OK, no problem. OK, so when we talk about report portability.
8:36
So, this is, again, a real kind of value add, and it can save a lot of, you know, consultancy work and time and effort. When you come to, for example, upgrade your ERP. So, if you’re working on Dynamics NAV at the moment and you’re looking later on to migrate to Business Central, the way that the Jet Reports are run on the way that the functions are built the reports effectively are identical. And you can just take your reports with you, point the existing reports at your new data source, and the reports will continue to run exactly as they did before. That is the assumption that the table structure is the same between the old system and the new system. But in most cases, the database structure between NAV and Business Central is very, very consistent. So, when we have available on our website, a suite of reports that you can download, those reports are the same regardless of whether it’s for NAV or Business Central. So, all of the differences between the systems and the method of connectivity that is all managed in our data sources and our adapters. So, this is really what allows us to take the reports along with you, when you, when you upgrade the ERP, we were just mentioning as you know, scheduling or e-mailing out reports, so Jet has got a powerful scheduler built in. This allows us to run individual reports or folders of reports, and to output the reports in a number of different formats, like HTML or PDF, for example.
10:06
We can batch schedule report, so it might be the case that you’ve got the same sales report, but you want to loop through that report once per salesperson and distribute a version of that report to each salesperson. Just relevant for, for their particular customers that they’re responsible for.
10:24
E-mail distribution is possible as well, and reports can also be scheduled based on, you know, certain business conditions being met.
10:32
It’s possible to transfer data from the actual report itself into the body of the e-mail. So here, hopefully, you can see this. But, you’ll see that there’s a sales total inside the body of this report, which is actually a calculation that’s coming from the Excel document itself.
10:50
So, there’s, there’s quite a few opportunities that we’ve got in order to run and distribute reports. OK, so I’m going to switch now just to go into the product, just to show some of the things that we’ve just spoken about. So, I’m just going to flip across to Excel. And the first topic that we mentioned was the actual connectivity to the data source. So, in the Jet ribbon, which is built into Excel, in the settings area, we’ve got available to us, the data source and the company that we want to connect to.
11:24
So, this is the default company that will be connected to when we run a report. And in Data Source settings, for example, this is where we would come to set up a new connection. So, I can show you very quickly an example of how we would connect to Business Central Public Cloud.
11:45
And if I just select Business Central, and Web Services, Public Clouds then what this will do is prompt Microsoft authentication. This will then ask me for some for some credentials to enter. So I’ve got a test domain here that, that I use and just add password.
12:10
The nice thing about this is all of the other parameters are picked up from Azure Active Directory and entered for us in here, so you know, exactly what you’ve seen is the is the setup on the Jet Excel side.
12:21
On Business Central side, when you’re working with Public Cloud, there is a small Jet Reports extension, like I showed you on the, on the slide to install. So, that’s just a one-off task that has to be done. And then you see here, for example, on the Web Service tab, all of the, so, this is actually my tenant ID, and you can see here already that I’m looking up into the, the companies that are available. And this will be the default company.
12:48
There’s a couple of others there that are available for the instances, where you’ve got the, let’s say, an on-premise database, or if I’ll come back to the one I had here already.
13:02
We’ve got different methods to authenticate against the against the database, and then the connection, really, you just specify the server, the database that you’re connecting to, and again, what the, what the default company is. But really, once the connection is set up to your database, or your data source. If it’s Business Central Public Cloud? You know, have the look and feel of Jet Reports, and how it operates is very much similar experience. OK, so next topic we spoke about, I think was the extended fields. So, I’m going to make a connection to the data source that I’ve got here.
13:39
And I have added an extension to the customer table where I’ve added a non-standard build, which is one under my favourites list which is called Custom Reward Type. So, I just wanted to use this as an example of how we can very quickly. And there’s nothing that I’ve had to do in order to expose this information. This just shows up automatically if it’s an extension been added to the database.
14:09
So, where we are here now is the browser. So, this is one of the drag and drop tools that I can use and I can just select some fields in a in a particular order that I want them to show up in the report and then just drag everything across into Excel. And then everything else is basically done for me.
14:27
The formulas that are built, I’ll just explain these quickly, this NL Rows customer will actually produce one row for every single customer record down the page in a list format and any headers, you know we’re using standard Excel tools to format the actual report in terms of how we want the report to be styled.
14:48
And this will be just controlled just by using standard Excel functionality You’ll see here that some of this is a little bit now squashed up because I’ve made it slightly bigger or wider. And you’ll see at the top here, that we’ve got this, these fit keywords.
15:05
So, what happens is, after we retrieve the information during the report execution from the data source, all of these columns will be resize to fit the information that is returned. So, it will always make sure that your information is correctly displayed.
15:22
So, if I come back to Jet and go ahead and run this report. OK, this isn’t the most comprehensive report in the world, but hopefully it gives you an idea as to the kind of experience to how you can, you know, start to author reports pretty quickly with drag and drop tools. And what I’d like to move on to next is just an example of a report that is, let’s say, a bit more comprehensive. This is kind of one, if you like, that I made earlier. And this, actually. Rob, yes, go ahead.
15:56
Quick question, someone’s asked what version of Jet Reports are we using in this demo?
16:02
This demo. This is, just bear with me, there is one very recent version I’ve not upgraded to yet if I just go about 21.3.212244.1 to be exact. So, I think 21.4 is out, or there could be a release that’s out on top of this one, so, I mean, the version of Jet Reports that you’re running, everything is completely backwards compatible, You know, it’s, it’s an earlier version, so, customers, for example, you can, if you’re running Jet Reports as an individual excel add in, you can actually run different versions if you like and in the same organisation. So, if you’re looking to upgrade to the most recent version recommendation would be to upgrade one machine to start with. Make sure that your critical reports run without any issue before rolling that out right out to everybody else. Yep.
16:58
OK, OK, so this report is a classic income statement, but what I’ve done is actually added some commonly used Excel features to this. So, one is the conditional formatting. So, there’s a role in the operating expenses to add an exclamation mark, if my expenses are more than 5% of my budget, and then I’ve added some of these grouping features here as well. So, we’re in design mode of this particular report.
17:29
But the way that this report is designed is that the NL Column’s replicator here is going to basically copy these five columns across the page for how 1, 1 set of columns per month that I specify in the in the date range when I execute the report. So, this is really flexible because the Reports that are built into NAV and Business Central, a very, you know, the layout and design of them is quite restrictive that they’re kind of confined to the size of the page. So, to have a lot of flexibility about the width of the report, for example is something that you, you don’t often get. So, if I run this report, this particular report has been designed such that I can specify some report options. I can specify here, for example, a specific company, and then a specific date range.
18:22
So, if I just put in here, for example, up until the 30th of June 2021, then I’m going to get six months’ worth of columns replicating across the page, and the report initially will show, collapsed, and then I can use I can either expand, for example, an individual month to show what the actual sand the budget and the variances and the variance percentage was. All of this is in a summarized form like this.
18:51
But what I could do is expand all of the columns to, so now I have to scroll across the page, and I could expand all of the, the rows as well, to show the detail. All of the functions and filters against the account numbers are in the same filtering format as you would find inside NAV or Business Central. And one key feature is the ability. So, although we’re working here in Excel, I’ve got the ability to drill down to the ERP, and what happens is the web client is launched exactly the same filters that I’ve specified on the report are displays. And then here, what this allows me to do is to go and kind of find the entries that are associated with this. And I can go and look at the individual sales invoice that that makes up this number.
19:41
So, having the connection, if you like, between Jet Reports, and Business Central, or the same thing, works with the Roll Tab, the client is really critical because if a financial controller sees that we’re over budget or something like that, you’ve got the ability to navigate straight away to the source system and go and find out exactly where any variances are coming from.
20:07
Incidentally, for month end processes, of course, we are pulling fresh data every single time that we run a report. So, even if I post a journal in the ERP and then run the report straight afterwards, I’ll just run this report again. But just putting a star here for my company filter.
20:27
Then this pulls fresh data from the ERP, so any adjustments made during the month end process. You can very simply just run and refresh the report, and you’ve got results here immediately according to what is in your source system.
20:41
So, because I specified, then a star for the companies, you might notice at the bottom here, I’ve now got a tab for each company.
20:51
So, this has allowed me to run in a single report execution an income statement for multiple companies in the same report execution.
21:00
So, again, this is something unique to Jet that you cannot do in the standard reports that you get with Dynamics. When you log into a company, with Microsoft Dynamics, you’re restricted to running reports, just on information about that one company.
21:17
So, if there are any customers on the call with multiple companies and legal entities, then Jet allows you to fix reports such that you can run a report based on multiple companies in, in the same report execution.
21:36
OK, and then one final thing to show, just before we give chance for any questions, so if there’s any questions, please, obviously, pop those into the chat. Just to take a very quick look at the scheduler. So, I’ve set up a schedule which is for the same income statement that we just looked at. I’m just going to trigger this report to run now, so I can run the report on demand, or what I can do is set the report to run, for example, on a daily schedule or a weekly schedule, or an hourly. It doesn’t really matter, but there’s lots of options there available to me.
22:12
And this report is actually configured to be sent out to myself by e-mail, once the report has executed, and hopefully I should be able to show you that in a second.
22:23
If I edit the definition of the scheduled job, just bear with me. It’s just loading up now, and you’ll see here under the Reports section, I can schedule either a single report or a block of reports in a in a folder of reports.
22:39
The e-mail is just in, check the time, if you like. This is all genuine, 12.23, which is the time now, and then, you know, this is the text that has come in to the e-mail address with the report attached. And this is the same information that I’ve got specified in the definition of this scheduled task job here.
23:01
So, under the schedule, we can I’ve just got this to be recurring once, so I just send the report out on demand. But, yeah, there’s a lot of options here into when that report is basically sent out. And, in addition, the reports get saved to an output directory. And it’s possible to date and time-stamp the reports as well so that you can save a new version every single day.
23:27
For example, if that was your requirement, but commonly this is used, obviously, to send reports out. I think I mentioned earlier, you’ve got a number of options to save reports as a PDF or a CSV file, or an HTML file, for example. And of course, that can come out to any number of recipients if the e-mail address, for example, an e-mail distribution group?
23:57
OK, so I’ve tried to, you know, throw in a lot of information there.
24:03
We’ve got resources on our website I’ll just come back to this one here, Getting started with Jet Reports, so we’ve got a lot of resources here that point you in the correct direction. How you for example, get connected to Business Central? Are we making a direct connection? Or are we making the connection to Web Services with Public Cloud? So, this walks you through the process of installing the Jet Reports extension of what you have to do in order to get connected to that in Excel. And in addition, on our website, we’ve got a library of prebuilt reports that you can use to get yourselves up and running pretty quickly as well.
24:45
Actually, Rob, that was one of the questions as well. If you could just give a few examples of the reports that are included in that, in that package.
24:54
Yeah, I’m just going to go Jet Reports, sample report. So, if you just Google that, and then you can come to sample reports insightsoftware, and then I think this is filtered from before, so this is already filtered by Jet for example. In the financial system, you can choose NAV and Business Central at the top, and then there’s a possibility here as well to filter out the type of, the role. So, for example, if I was in finance, then this filters out all the finance reports.
25:30
So, this kind of shows you everything that we’ve got available, we have reports that are available either for Jet Reports, plus also Jet Analytics as well. So, in total, there’s probably about 100 pre-built report templates. You can access the design of those templates as well, or make any adjustments that you want to them, so you can use these as some inspiration, if you like. Or a starting point as to how you want to lay out your own report. But, yeah, it’s very easy to step into design mode, make any adjustments that you need. And every single report has got a tab that explains what the report is, how it works, and any adjustments that you might need to make in order for the report to work, but most of them you can run straightaway out of the box.
26:19
We’ve also had a question if we could send that link out, so I can do that after the webinar, and so I can send the link.
26:28
We also had another question say: Is there a difference in functionality if a client is running an older version of NAV? So, you know, perhaps something as old as 2009. How far back does the functionality go, and is it different depending on what version of NAV you are on, versus being on the latest one.
26:50
Yeah, it’s a good question. I mean, to be honest with you, there really is a very, very little difference. And you know, like say, the differences between the connections to the ERP is basically handled in the definition of the data source.
27:03
All of the actual formulas are very, very consistent. I mean that there’s a number of changes to the database, for example, and how information is stored and the type of database objects. I don’t want to say too technical, but the actual physical design of the report. So, when you when you look at these Reports here, for example, although there is a selection for NAV or Business Central, all of the reports work, actually. So here, this TV report works for either Business Central or Dynamics NAV.
27:34
So, I mean, you know, there isn’t really a different version of the report we make available depending on the version of Nav or BC that you’ve got, and that really is what allows you to take the report with you when, when customers upgrade, cuts out a whole bunch of time and consultancy work, and, you know, reports can be quite a headache area when you’re, when you’re trying to undertake an ERP projects at the same time.
27:58
So, it certainly is work that you can, if you like, do an advance in preparation for any upgrade.
28:06
Wonderful. That’s it for the moment if anybody asks, if you have any further questions, if you just pop them in the box, just while we’re chatting.
28:15
We also had a question around currencies and where the way they are formatted. Is it within Business Central, or is it coming and being formatted in Excel directly?
28:27
Again, the pound signs. Yeah, I actually don’t know whether you spotted that, but if I go back to the Report Design, that is actually something that I specify just on the home screen, and I’ve formatted that as currency here.
28:39
So, any numerical information like this is stored inside the database as a decimal. So really what you would need to do is just format that just by using standard Excel tools, you’ve got either currency or accounting layout. You’ve just pick out the design that you want. But physically in the database, all of these values are stored as a decimal value.
29:03
OK, I think that’s it for question for a moment.
29:08
OK, good.
29:11
I think we are, I think we’re pretty much finished. So, we are right on half past.
29:16
Yeah, if you just jump back to the PowerPoint deck.
29:22
Yeah, so we have the actions at the end. We can’t miss out on them, Rob.
Jet Offer – Available until 20 December 2021
29:30
We do have, or Jet do have an offer at the moment available to the 20th of December. If you’d like to know any more about that, and do get in touch with us here at TVision, and we can arrange a call with either yourself, Rob, or, you know, one of our consultants. do speak to your account manager, and if that’s something that you might be interested in, to either change the numbers that you have or increase, et cetera. So, yes, I did take a look at that when the webinar on demand comes out.
30:00
And then lastly, just to say thank everyone for attending today, the webinar will be available on demand on our website. And, as always, we do send the link out after the event. And to, the person who asked for the link will include that as well in the general e-mail. I’m just to get those sample reports for you. So, thank you once again, Rob, for great demo there. I’m sure, that help people on their Jet journey, just to find out a little bit more, and we’ve been touching. Thanks so much for me. OK, thanks very much.
30:33
Thank you.
