Top 5 things you need to know about Getting started with Jet Reports
Watch the recording by clicking on the play button
Thank you, everyone, for attending today our webinar on the top five things you need to know about: Getting Started with Jet Reports. Just before we start the webinar, just a few housekeeping rules. All attendees will be on mute and will remain on for the whole webinar. Please use the question box to ask any questions related to the topic. All questions will be answered after the demo. If we run out of time, or if your question requires a longer answer, then the question will be answered offline directly. Use the chat box for questions related to the webinar, such as if you can’t hear the audio, or if the presenter is going too fast. But please note, that a link to view the webinar recording will be sent to all attendees after the demo.
This is our monthly webinar on key areas of NAV and Business Central. You can sign up for as many as you like on our website. We’ll also send out regular e-mails to register for the next one.
My name is Tiziana Giardina and I will be facilitating the demo today. I’m an Account Manager here at TVision. I’ve worked here for 2.5 years, and my background is 18 years in sales and marketing. Rob Jackson from Insight Software, will be doing the demo today, and his role is Technical Jet Account Manager.
Today, we will be showing you the top things you need to know about getting started with Jet Reports.
Jet Reports is an Excel add on, which integrates with NAV and Business Central, has advanced features for creating, scheduling, and automatically distributing reports directly from NAV and Business Central.
Jet Reports is used globally for many companies, as it adds an additional level of sophistication to the standard reports and account schedules you have within NAV or Business Central, you can easily access and consolidate the data you need to create meaningful reports in a familiar Excel environment. So, for example, you can include your formulas and formats.
Jet Reports also comes with over 45 pre-built report templates, saving you time and effort and reduces the amount of errors due to manual Excel report creation.
By the end of the webinar, we hope you will understand why many of our clients use Jet reports and how it can be used in your business to improve your KPI Reporting. We can’t show you every feature. But we will show you those, we believe are the most useful and applicable to the majority of companies.
So, the agenda for today will be introducing you to Jet Reports. We will have a demo, the top five things you need to know about getting started with Jet Reports, a chance for you to ask some questions, and then finally some information about Jet Report incentives. I will now hand you over to Rob for the demo.
OK, just checking. You can hear me OK?
We can, and you can see my screen? We can, that’s good, right. I’ll get started. So, thanks, everyone for joining the session, so it’s a good opportunity for us to have some connection directly with some end users of TVision. So just as a quick introduction.
The Jet products, so including Jet Reports, are purpose built for Microsoft Dynamics ERPs. That’s NAV, Business Central, and also GP, as well. So, we’ll be focusing today on Jet Reports. But just to make you aware that we do have in our product stack Jet Analytics, which is a data warehouse automation platform for business intelligence. We have Jet Budgets, which adds some workflow and collaboration to the budgeting process, and all of these products sit inside the Jet Hub, which is a web-based portal, where you can store and manage all of your information centrally.
So, as was mentioned, I mean, Insights Software is a global company that incorporates Jet for approximately the last 18 months, and just to kind of give you some reassurance, about 15,000 to 25,000 customers of Insights Software are Jet customers, and it’s used across the globe, and in many different languages.
So, let’s get into things and to explain really what Jet Reports is. So, it’s a report solution that is built directly inside Microsoft Excel. And we add some extensions to the standard Excel functions in order that we can extract information directly from your transactional ERP.
So, you can refresh and run reports on effectively live data in your transactional system. And you can surface that in Excel in exactly the format that you want, and there are some techniques that we can use in Jet Reports to dynamically create rows, columns, content inside the Excel document. And all of the formatting is in the way that you, that you require. You don’t require any programming skills. Any kind of hard coding skills to design Jet Reports. Everything’s based on functions, and drag and drop tools, and I can show you some examples of that as we as we move through. And one of the advantages of Jet Reports is that you are able to run and execute a report, pulling information from multiple companies. So, that’s something that you can actually do inside the standard ERP report, because when you are logged into a specific company, you can only report on that, on that one company.
So, what we’ll go through today, we’ve got a lot to cover but first of all, how we get Jet Reports connected to the ERP and the data source. We are going to use one of the wizards as brief introduction to Jet. And I’m going to use that to explain how the basics of a Jet function works, how it pulls data from the ERP directly.
I’m going to use the browser briefly to show you the beginnings of how we use the browser, if you like, as a report designing tool, which is a drag and drop tool. And then, we’re going to look at a couple of the standard reports that we have available on our website.
So, I’m just going to jump into Microsoft Excel, and this is just a blank workbook, and you can see installed with the software we have a Jet tab, and then we have a specific ribbon for the, for the Jet products. And what I’m looking at here is all of the tools inside Excel, which are appropriate for a designer, so we have different types of users. We have people that can design the report and put together the actual report definitions. We have viewers, which are able to run and refresh the reports, and you can also send the reports outside of your organizations just to companies that might just run Microsoft Excel. So, they might just literally want to open and view the content of the report.
So, what I’ve just done here is just jumped into the area which defines the data sources. And I’m just going to call this one AAA Webinars. So, it appears right at the top of my list, and we can connect to NAV, Business Central or even to universal data sources. On my machine at the moment, I have a NAV database. And I’ve got connectivity to a SQL Server. If you’re connecting to Business Central Public cloud, that’s fine, too. We have to install an extension onto the Business Central environment that Jet in turn connects to, but I’m just using this example here, just because it’s quicker for the purposes of the webinar.
So, we make a connection. I have everything running locally on my machine, on a server here, and I’m just going to make a connection to the correct database. If I can lay my hands on it, Jet corp for NAV, and I can choose from here, there’s three companies, but I can just choose a default company.
One of the advantages of Jet is that we have got the ability to configure a drill down server. So, this allows tight integration between the reports in Jet and your transactional systems.
So, you can see how specific numbers are made up, so that just requires a couple of configuration options just here. With that setup, I can just change the data source to my new one that I’ve just set up, and if everything is working correctly. This is why I love live demos.
Yeah, I’ve got connectivity to all of those three companies on my database. Now, depending on what you’re connecting to. We’ve got a website page here, which is actually also called Getting Started with Jet Reports, and inside this web page, you’ve got all of the configuration options. So, the type of database that you’re connecting to. So, for example, if you’re connecting to Business Central, you then choose, if you’re using, for example, the, you know, the public cloud version. And this runs you through all of the steps and the extension that you have to install, in order to make a connection from Jet.
There are a number of different connectivity scenarios, but I think it’s worth mentioning that all of the details of the database are handled inside our adapters. But the design of the Jet Reports themselves are very consistent, regardless of the actual data source itself.
So, with that, the second thing I said I would run through is how we can use one of our wizards which is called Fast Financials.
This makes a connection straight to the transactional system and it pulls out the structure of my chart of accounts. All of the general ledger account. I can choose the columns that I want to include in my report. This particular wizard puts together a financial report that compares my General ledger (Net changes per account) versus my budget, and then it will show me a variance and also a variance percentage. So, I’ll include all of those things in my selections, and in the chart of accounts I’m going to scroll down and pick out the expense section of the general ledger, which is my 60,000 range. That is, all of my expense accounts. I can use my Shift key to then, left click and select whole selection, and then simply drag that report straight from the Fast Financials Wizard straight into Excel. So, what happens now is a Jet builds in the background a report for me, I’ll just take you through some of the specifics about a Jet report, that that’s just been carried out.
So, where you see these numerical calculations, for example, in G12, if I click on that cell, you will see that we have a GL function, which is a Jet specific function. So, here you will see that there are four different functions that we have introduced into Excel. The formulas are built in a very similar way. But for example, I can click the function wizard button, and this will show me all of the parameters that make up the function itself. And in addition, the functions operate just like a standard Excel formula. So, if I was to press F2 I can see the dependent cells for the formula.
So, what this asks is, to the database, please show me, you know, the Balance, i.e. the net changes of this specific account between this start date and this end date, the start and the end date are parameters I can enter as a user. So, if I’m a viewer of a report, I’ve got the opportunity to run and refresh the content against my live data source.
So, when I press run, if the report is designed to such I get some report options. So, this allows me the opportunity to put in things like a start and an end date, so I only see the results for a specific timeframe. I can choose a different company, I can specify a budget, that type of thing. I’m just going to leave this as, as is for the moment and then just run and execute the report.
So, these numbers now, that I see represent just the changes between these two days, i.e. for the whole of last year, and now that the report has run, I mean, what is called report run mode. And if I click now on this same cell you will see that the formula has been replaced by just a numerical value. This is what allows a report to be sent to an external user that just needs to open the report in Excel.
But, if I click on the cell, now, I’ve got a drill down arrow, and what this does, it will launch the ERP client, so, regardless of whether you’re working with NAV, Business Central, whether it’s the role Tailored Client, whether it’s the web client, it will launch your client with the page that represents the detailed transactions that make up that balance. So, this is a great tool for finance managers because it gives you tight integration where you’ve got connectivity to the ERP directly from your reports that you can refresh to live data directly from Excel exactly in the format that you want. So, the idea is here that the General Ledger entries have been filtered according to my date range and for the same General Ledger account.
So, the sum total of all of these entries will marry up back to the total that I’m showing here in my cell G12, H12 is doing the same thing. It’s showing me the balance for the budget entries. And then I12 is just doing a very simple Excel subtraction, one from the other, and then a very similar thing. So, you can see the use, if you like, of standard Excel functions, in conjunction with Jet functions, also. OK, so that’s, you know, one of our standard tools that allows you to get to get started pretty quickly. The next thing to explain to you is about the drag and drop browser. So, the browser is a tool that allows you to investigate all of the tables and fields that you’ve got in your database. This will automatically include any table extensions, any extended fields that you’ve had added to a database. So, there’s no queries or anything that you have to write in Business Central in order to surface that information. Everything’s available to you directly inside the browser from your database. So, our connecter handles all of that piece for you.
So, if I just come, for example, to the Customer Table. I can see all of my fields available to me here, And I can very simply kind of filter out. This drag and drop tool allows me to build content by dragging and dropping the fields that I want. So, I’m just building as a very simple list report here just to show you the basic functionality.
I could just drag and drop Number, Name, City, and I’ll just put in their sales, for example. In terms of the formatting, everything is basically working with Standard Excel tools at this point, so I can format numerical values as a currency. I can colour and format this and make it a different colour. I can make it bold and I can make it large.
So, just note, there’s no programming skills. Any hard coding or anything like that involved everything, in terms of formatting is, is standard Excel functionality. I have the ability also to filter by certain fields. So, I know that if I run this report, it will include customers that I have sold nothing, too, so I can eliminate those, and I can also saw the results such that I’ve got the largest customer showing the at the top of the report.
Now that I’ve done that, you’ll see that I’ve got some hashes here. But when Jet Reports execute these keywords, for example, in H1, it will resize the column automatically.
For me, such that you know that the width of the columns is automatically controlled by Jet for me, OK? So, that’s a very basic reports to kind of start us off. Now, what I’ve got, again, because of time, is a variation of this same report, but instead of just creating rows in this direction for all of my customers, this next report, which is an extension, shows columns being produced in this direction from Jet at the same time.
I’ve also got some report options included on here so I can say, OK, please only include, I’ll leave that as pre-set as it is, the first six months of the year. And I only want to show my top 20 customers. So, if I run this report, just to explain to you the way that it’s designed, I have a list of 20 customers, so that’s controlled by the amount that I put in the report options. And columns are basically produced in this direction. So, I’ve got one set of columns that every month, but I’ve got two date columns. I’ve got one that represents the, the months for the year that I selected. And then I’ve got the same month for the previous year, So that’s a calculated date field. But the results are just filter down accordingly and the variance just shows me the difference between the two, and I’ve used some Excel conditional formatting, just such that I can pick out any kind of outliers or the biggest movers.
When you go to our website, Getting started with Jet Reports, you can find on here some pre-built reports. So, this takes you to a page where you can download an entire zip file. And this once downloaded, gives you a whole range of standard reports that you can use with the solution. And all of our products are in here. So, the Jet Analytics ones don’t apply to this. But you’ve got a whole range of reports here.
And what I’d like to do is just spend a couple of minutes showing you an example of 1 or 2 of those, just so you get a flavour as to what we actually provide out of the box.
So, the first one to show you is report number 60. This gives us a consolidated general ledger, like a consolidated P&L. Each of the standard reports that we have downloadable from our website have got a Readme page. This just explains some things about the report, and any changes that you want to make about it, but on the whole, the reports are designed to run pretty much straight out of the box. So, if I come to my Jet tab, and then just press run.
This is exactly the report as it’s downloaded from the website. So, I can enter my date filter, I’ll include the whole of last year, some reports are in this format where you put the start and the end date in the same field, or like the other report that I had. It just depends.
You can design reports either way, and what this report does is dynamically create me a consolidation of the multiple companies that I’ve got inside the database here. So, I have three companies. So, so the report automatically builds me three columns, one per company. So, it kind of expands the columns out in this direction, and then gives me a consolidation, i.e., a total of the three for me, and then dynamically it will produce one sheet per company for me. And at the company level the results are broken down by one of my dimension codes, which, in this case, is department. So, this kind of gives me a consolidation if you like at a company level, but broken down by department, and because these reports can be designed in a way that is dynamic if I was to add a further company to my database and then rerun the report, then I would just get a fourth tab for my additional company and I would get a fourth column in this consolidation here, by company, So it’s quite easy to build reports that flex dynamically based on the content that you have inside the ERP. I think I’ve got time just for one more, which is an item report. So not everything is, you know, financial reporting. This particular report shows me product groups and then, you know, the specific items for that product group as well.
I’ll just run this report quickly, and then just explain to you what the report is doing. So, I’ll just put in there, again, you know, the whole of 2019 team, where I know that I’ve got some data. I can filter for a specific product group. So, if I look this up, and I can pick sports bags, for example.
If I’m just interested in a particular product group, this does a look up to the database to show me the possible values that I’ve got. And again, if I run that report, this report, again, is kind of, doing me a comparison between the date period I chose, and also the comparable date periods for the last year.
I’ll just scroll across here, then we use some standard Excel visualisations to show me the differences between them by using some Smart graphs. But this is more of a grouped report where we list the product group, and then we list all of the items individually that belong to the group. But, in principle, any data that you’ve got in your ERP, you can design a Jet report based on it So it doesn’t matter whether it’s customer data, item data, general ledger data, that type of thing, and not forgetting that every single time that we run the report it’s refreshing against live data in the ERP.
OK, So I think we’ve got seven minutes left, I think, so I’m going just put up a slide so we can take some questions, and hopefully they will be read out to me.
OK, thanks, Rob. So, if you have any questions at all, but if there’s anything that you’ve seen or haven’t seen yet, if you’d like to submit them through the question box, and we’ll just read them out as we go.
OK, we’ve got one for you. Rob, can you schedule for the reports to be sent to anyone, or do they need to be set up in Jet?
You would need to set up in Jet for them to be scheduled, but yes, they can be scheduled to you. So, if I just come here, for example, you’ve got a scheduler directly inside the Excel add in. So, this will run a scheduled task.
And then also, in the application settings, you’ve got the opportunity to set up like an SMTP like an e-mail connection. So, what you can do is schedule a report to run. It will refresh the data against the report with any options that you want to specify. And then the report will be sent as an attachment to an e-mail to one or any number of recipients. And it’s also possible to run reports on the schedule the only run if a specific business condition is met, for example. So, you can use reports that go out as exception reports as well.
Got a few more questions, can you show us how to link tables in an NL table builder query?
That’s going to take a little bit longer to do, so, the answer is yes, but it’s probably an idea that we put something together on an e-mail just explaining the steps for that. And then we can send that out as a follow up. I think that’s, that’s going to take a little bit longer. But, yes, the answer is, yes, and I can, I can show you that no problem, OK. and unknown,
Ok, how is Jet Reports joining across tables. So, we understand all of the table relationships in the database. If I’d just come back to the design of this report, that allows me just to jump into the browser. So, for example, I was looking at the customer table. And, if I just show the details of the customer table, it will show me all of the table relationships that I’ve got.
So, for example, in the Table builder, which is a tool that we’ve got, when we introduce another table that’s linked, we understand the links to all of the tables from the Customer Table. So, for example, we’ve got a link to the country region table based on the country region code that sits on the on the customer table itself. So, a lot of the things that we’re doing inside Jet understand the links to any additional tables, I’ll just show you a very quick example of that. In the NL function, for example, if I put here a rose based on my customer table, which is what we had before, what I can do here is actually do a link equals function to my country region table, and then that link is put there automatically these. These are the two corresponding fields in both of those tables. And now, this list will be my fields from the country region table.
So, this is how you can filter results based on one table based on records of a linked Table. That makes sense. So that’s a bit more of an advanced topic. But yep, that’s, that’s all available and all these links are generated automatically.
Brilliant, Just one more question.
If I open up reports as a viewer, will it already be updated or only when I click Run, Yes. So, the report will contain inside of it, the results as to when it was last run, when you first open it. But report viewers are able to press run, and then you see the report based on, you know, it will refresh against the against the live data source and then show you the updated results, But, yeah, when you, when you open the report you have to press run after that in order for it to refresh.
OK, any other questions we will take then we will respond to you later. Thank you, Rob, OK,
We would like to make you aware of a current Jet promotion. So, if you purchase Jet Reports or Jet Analytics between now and December the 18th.
You will receive 20% off Jet Reports or Jet Analytics foundation packages, and 50% of all Designers and Viewers added in a one-time order. The offer also applies to existing Jet customers who upgrade to Jet Analytics, or would like to add users to their Jet license.
In conjunction with this campaign, Jet are also extending their lowest ever discount customers currently running Jet Basics with Dynamics NAV and GP. Until December the 18th Jet Analytics users can upgrade to Jet Reports for £100 a month for the Foundation package.
50% discount on add on viewers and designers will also apply to any Jet based users who upgrade, so please contact me if you’re interested in taking up any of these offers.
We will also put a reminder out about the webinar in our newsletter.
So, thank you for attending the webinar. Once I close the session, survey will appear. It would be great if you could respond with some feedback. And if you have any further questions, please feel free to e-mail them to me at firstname.lastname@example.org.
Thank you very much.