Watch the recording by clicking on the play button
Introduction
0:02
OK, thank you for attending, everybody. So, just a few housekeeping rules before we start the webinar. All attendees will be on mute and will remain so for the whole webinar. Please use the question box to answer any questions related to the topic, and all questions will be answered after the demo. If we run out of time, or if the question requires a longer answer than it will be answered offline directly, please use the chat box for any questions related to the webinar, for example, if you can’t hear the audio or the presenter’s going too fast. And please note that a link to view the webinar recording will be sent to all the attendees after the demo.
0:43
So, to introduce the top five webinar series. This is a small amount key points in NAV to focus on. This is the 8th in a series of monthly webinars. You can sign up for one, some, or all of them, and we will be sending out e-mails to register for the rest of the series.
1:03
OK, so my name is Karen. I’ll be facilitating the demo today. I’m an account manager here at TVision. My background is 17 years of software account management experience working for ERP companies. Ian is going to be doing a demo today. He’s at the Support Consultant here at TVision and he’s been working for here about two years, and he’s been using NAV since 2000.
1:31
OK, so the topic today for this webinar is Reporting. Now there are many ways to report on the information within NAV in order to satisfy your requirements for KPIs, sales reports, management reports and ad hoc reports. But which one is right for your business? Well, here at TVision we have put together this webinar as a guide to understand what output you actually need and therefore, which solution will be the most suitable? And it’s going to be even more important with Making Tax Digital coming in April of next year. And just let you know we will be doing a webinar on that soon as well.
2:07
So these are the five key considerations when deciding which reporting method is best. So, first of all, who is your audience? Who are you producing the reports for? Second, what’s the desired output? So, what did they actually need, is it management reports, is it KPIs, is there anything legislative that they need? Then interesting consideration is why, you know, why are you doing this? What is going to be the actionable insights that come from this reporting? Have you just always been producing this report that you don’t know what it’s used for. So, it’s always good to kind of consider, you know, why are you actually doing the report. Number 4, so, what level of granularity do you need? How much detail do you really need to go down into? You know, s it just a summary which is needed, or do you need to kind of drill down into very key information?
3:02
And then finally, How do you want to access the reports and how often do they actually need to be updated?
3:09
OK, so we’re going to be using a scenario for this webinar, looking at reporting from different company roles and then suggesting the most effective way to satisfy these needs. So, before the demo, I’d like to take a quick poll to see how happy you are with your current reporting method. So, if you wouldn’t mind putting your answer in that, be great. I’ll give it a few seconds, and then I’ll let you know the answers.
3:52
OK, I’m just going to give it a few more seconds for everybody to submit their answer. OK, just still got some more answers coming in.
4:13
OK, so it’s quite interesting there. It’s nearly half of the answers are actually saying that you’re not very happy with your current reporting method and that you are looking into better solutions. Then it’s pretty evenly split between, you know, we know what the issues are but we don’t have the time and resource, and yes, but it takes too long. Only about seven, 13% actually now saying that they are using the most effective way. OK, I’ll now hand over to Ian who will be doing the demo, please?
The demo
4:44
Thanks, Karen. And hi, everybody. Today, I’m going to be pretending am a business analyst and different people ask me for different types of reports. It’s all going to be sales-based reports as I can’t cover everything, there’s just so much. And for today’s demonstration, I’m using a standard NAV,2018 database. And, as I said, I am working for Kronos UK, which is a demonstration company. And I’m going to be answering some people’s questions.
5:22
First scenario that comes up, I’m going to look at the simplest option, which is a standard report. Somebody’s asking me for a report about sales, so I know to answer that question it’s my top 10 customer list, I can go search for it in the search bar.
5:39
But, instead of doing that, I would invite you to click on your departments and looking each department. And when you drill down, you can see a list of all the standard reports that are available in NAV. If I go to Finance, I’m going to see a whole host of financial reports.
5:58
As I say, we’re doing sales reports, so I’m going to concentrate on this side. Person asked me a question. I choose the customer top 10 lists to answer the question about, who are our biggest customers? And they get a standard report dialog.
6:12
Up at the top of this report before, it asks me if I want to continue using the same setup I used last time in which case it pre-populates all options for me but then it lets me put it in my own options as well. So, what do I want to see: local currency, foreign currency. Do I want to see 10 customers, 20 customers, do I want a pie chart or a bar chart.
6:34
Some of them might ask me for an ending date. They might ask me for a posting group, product group, etcetera. So, I fill in the options of what I’m looking for, and then I can filter down on that. This is a customer report so it doesn’t filter by customers.
6:48
So, I can choose Customer number. I can put in a range of customers to look at from a particular posting group, or currency code. I can add as many as I like. I’m just going to look at Sales for January so I put in a date range, and I preview my report.
7:04
The report takes a few seconds to run. And it pops up on my screen, and you can see I’ve got my report filtered for that date I asked for. Those are my top ten customers, how much we invoiced them in January, a total and a percentage of everything, and it produces a nice little bar chart.
7:24
This might be enough to answer the question of person standing at my desk, getting the answers printed out, let them take it away or I can e-mail it to him. And that’s just a standard preview, but there are other options available. So, looking at the same report, again, same options.
7:42
This time I’m going to say print. I can choose to print to Microsoft Excel, it would open an Excel spreadsheet with that exact same information we saw in the preview, formatted in the same way. Same with Word. I’m going to choose this PDF. This is nice. When I open the PDF, it’s exactly the same report, it’s just presented on the screen. It’s a little bit more user friendly.
8:07
I can zoom in and zoom out easily. I can search for text within this document. If there are a lot of pages, it will often give me a contents or index on the side so that I can jump to a particular page. I’ve got all these options. This is nicer than the built-in preview. I would say that this is better way of looking at reports.
8:33
So that’s a standard report. It’s very static. It gives me information to answer the question, if someone else ask another question about sales. They want to know how many sales invoices we produced in January. I can’t find a report that gives me this information on the system. So this kind of ad hoc reporting, I go look on the system.
8:54
It’s about sales invoices so I go search for my posted sales invoices, and I get them up on my screen. And here, I’ve got this list view of all the sales invoices on my system, looking at the dates, I see that I have got January, but I’ve also got some dates in there as well. So, I need to filter this list to only show January. I’m going to use this Filter box up here. And if you can’t see it a filter box, clicking on the arrow makes it disappear, and re-appear.
9:26
I can add as many filters as I want, but I’m just interested in the posting date for this one so I am going to select Posting Date. I’m going to enter the range of dates that I’m interested in, so that’s first of January up until the 31st of January. And as soon as I click enter, what I’m seeing on the screen is now a filtered down list, it’s only showing me the information that I’m interested in.
9:49
While someone standing at my desk I might be able to just answer their questions, just looking at my screen, I could sort these entries by any of the columns, so, I might choose to sort by customer, for example, and you are going to see how many times did we sell to The Cannon Group.
10:12
I can see that we sold to them three times. This might be the answer they are looking for. Then you might want to know what was the lowest invoice, what was the largest and sorting by amount gets to that information very quickly. I can sort it by date and see the order that the transactions are posted.
10:30
If I can’t answer the question while the person sitting at my desk or they’d like a copy of this that they can go away and work with, I can highlight all the rows by clicking in the blank space in the top corner here and that highlights everything for me. I right click, copy the rows and once I’ve done that, I can open Excel and paste this information into Excel.
10:55
This is available on all versions of NAV. Paste it here and extend those columns so we can see them. And this is the exact same information that I was looking at on my screen. The exact same columns sorted in the same order. And once I’ve got this in Excel, obviously, I can put in a formula to calculate the total, calculate an average invoice, minimum, maximum. Whatever I need to meet my reporting requirements to answer this question. So, that’s a kind of one-off question I can send the information to someone. There’s another way of getting things into Excel on newer versions. Sometimes, you see a button up there that says Send to Excel.
11:42
On this screen it’s not available, but don’t worry, if you click on the little help arrow, and choose Print and Send there’s an opportunity to send the information to Excel. It opens an Excel spreadsheet. Again, it’s exactly the same information, but as you will see, this time, it’s been formatted nicely for me by NAV. Putting in the headings with filters, particular currency or I could drill down and look at a particular customer.
12:14
Um, it’s created a named range, so it’s a table than just numbers on the spreadsheet. I can still put in formulas. It’s slightly different, depending on which way you prefer to see your information, plain text, or formatted text. Both options are available in newer versions. So that’s another nice, easy way of getting some reports.
12:40
Another example, someone from Finance comes and asks me that about sales. They are not interested in customers or items. They are probably interested in what’s happening in the general ledger. We’ve got accounts schedule reporting to do that. So, let’s go and have a look at some account schedules.
12:56
Now, we’ve done the webinar on account schedules before, So I’m not going to go into too much detail here. What I have done I prepared a very simple account schedule that looks at some sales. And I’ll just show you how that was designed.
13:11
It’s taking information from our GL accounts that relate to sales and it’s taking information on cost of sales. And I put in a formula that works out gross profit looking at total revenue and total costs to get us that number. Let’s see what that report looks like when we view, go to the Overview button and oops, there’s no information there. I need to look at January rather than December. So I scroll back and here we have our sales information in the general ledger for January.
13:47
Any of these numbers, I can click on them to see where it came from, and standard NAV functionality, I can drill down into that number and look at the source documents to see exactly what makes up that number. Nice thing about account schedules is, quite apart from seeing the numbers, I can choose a different time periods. I can put weeks or quarters instead of looking at months. I can put in a custom date range.
14:15
I can do global dimensions, I can filter and if I am giving a report that compares budgets I can choose which budget. This one is to just show you that I change. Let’s change it to a budget comparison. I can change a column layout as soon as I tab off that screen.
14:32
And now, let’s look at the 2019 budget, and we can see the same net change we saw before, the budget for the same period, what is the variance and I can see percentage variance. I’m about 60% behind target. I can flip between periods. This one doesn’t have a budget, because it’s 2018, and I’m looking at a 2019 budget.
14:59
Again, this is a nice report. I can do the same things. Again, copy the rows, paste them to Excel. This screen has the nice shortcut button to export directly to Excel. If you can’t see it, once again click on the help arrow, I can put this report in Excel. Obviously, if I put in an Excel and want to send it to someone, they’re not going to have that drill down functionality in their Excel spreadsheet.
15:28
Let’s say we want to a richer reporting solution. Someone comes to us, and they want a little bit more detail, a bit more dynamic kind of report. So that we’re going to go outside NAV, and we’re going to use the first tool I want to talk now called Jet Reports. That sits in Excel.
15:48
Everybody’s familiar with Excel, we know how it works. Once you’ve installed Jet, you get a new toolbar within Excel which gives you lots of options for building reports. This is a typical toolbar for Jet Professional.
16:06
I’ve created a sample report, so I’ll just show you what that report looks like in its raw state. And here we can see that’s the formulas that we put in to create my sales report. Looking at the function, this is bringing information from the value entry table within NAV, sales reports based on inventory items.
16:33 This is quite a simple report to build. Once we run it, it just builds a table for us. Because we’re looking at inventory, it’s got all sorts of positive adjustments. It has our sales and purchase information as well. So, we’ve got a lot more that we can report on this time.
16:55
Again, it has created for me a named range and I can build a pivot table from this. I’ve already built one. This pivot table, we are looking at 2018 and 2019, Just looking at the sales information from that data. And we’ve got each customer, the items that they bought, with an amount and the total for each customer.
17:21
Very nice report. We can choose what we want to show and how we want to display it on its side and very easy to change, instead of looking at sales value, let’s look at quantity sold. And now I am looking at quantity of items instead of the value, Let’s put it back to value, that’s always more interesting. This report is very easy to customise on the fly.
17:46
So instead of looking at customers, items that they buy, let’s swap that around, drag and drop. Now I’m looking at each item, all the customers, the ones that bought that item. How much did they buy and there’s a total.
18:00
So, on the fly, people can ask me questions, I can answer those questions. Instead of looking at it this way, let’s make it more into a matrix. Now, I’ve got the items down the side, customers across the top, so I can see Antarcticopy bought Mexico swivel chair £493 worth, I’ve got the total for each customer at the bottom. And if I scroll to the ends, I’ve got a total by each item.
18:29
You can see how quickly I’m generating different reports from that same set of information. This is an incredibly powerful tool. You get lots of options. If you go for a professional one, you get extra options. This report could have been created on the free version. On the professional version, we get options for extra functions, the best financial reports, drag and drop reporting, and the ability to schedule reports so that every evening at midnight, this report can be set to run an e-mail with the results to everyone in the sales team for instance. They come to work. They have the report.
19:17
The next one that I want to show you is Power BI. Unfortunately, I don’t have Power BI working on this machine, so I recorded it already in different machine.
19:28
Let me just show you what I recorded for Power BI. OK, a very busy screen, and the idea here is that you can pull information from various data sources. You get a lot of options, you can get information out of Excel, from SQL servers, from web feeds, You can bring information in from your Salesforce databases or from Websites, and there’s literally dozens of places you can bring information in and report on them.
20:04
You can choose how you want to display that information. You can see, I’m showing a map of the world with sales information, bar chart, and you’ve got dozens of options over here on how you want to visually present information, different types of charts. You can add extra pages and on every page the information is related. There’re loads of information coming in here, about customers, about items.
20:31
And once we put this all together, we get a very rich reporting environment. Everything here is linked. If I change one thing on this page, it changes everything else. If I have a look at Peter’s sales, he only sold in England, I can see where he sold in England. I can see the sales amount, the margin that he’s made and a nice chart, and I can drill in on that chart to get more detailed information.
20:55
Once I’m finished looking here, I can go back, or I can drill in even deeper and start looking at this day by day, instead of month by month. And go back to my original report. Let’s choose John. And John has got International Sales. If I click on North America, you’ll see the chart on the right-hand side change, click again, go back. Let’s have a look at Progressive Home Furnishings. We only sell to them in the United States, the map moves, the bar chart changes, and the numbers change depending on where I’m looking on this report. I can zoom back out on this map and bring back everything.
21:41
And this is a really rich reporting environment event that a few years ago, something like this, wouldn’t it be impossible Now? This is pretty easy for anybody to set up. Please don’t worry about that message in the corner. This is pre-recorded, so I’m looking to leave you to do e-mails. So that’s five different reporting scenarios I’ve looked at very briefly.
22:07
The standard reports, ad hoc reporting from tables in NAV, account schedules, all three of those, they are available in NAV. If you have a licence, you can use them. It’s not going to cost you anything.
22:22
Big things to learn for your standard reports and ad hoc reporting is how to use those filters to see the information that you’re interested in. The standard reports, if you have some statutory requirement to produce a set report every month, and it doesn’t exist we could create one for you. The nice thing is its cast in stone, you’re never going to make a mistake.
22:47
Ad hoc reporting, you can do that on the fly, and your account schedules, again, they are nice, you drill down where that information came from. The two tools we looked at outside of NAV, Jet Reports. Jet Reports comes in three flavours. The Jet Express is free. You could download it today. And if you’ve got an on-site NAV server, you could be looking at reports tomorrow. You don’t have to pay a penny. It’s just the investment that you make in your time and effort, and figuring out how to make it work.
23:23
There’s a professional version, which you pay for, which gives you richer functionality, allows you to schedule reports. It allows you to produce those reports and put them on a website instead of in Excel. And then there’s Jet Enterprise, which is the big boy in Jet’s arsenal, which is full on BI reporting, builds cubes, and everything.
23:46
Power BI, that is Microsoft’s flagship reporting tool. Again, there’s a free option that lets you look at the product. It does impose some limits on how much information you can analyse at once. If you like it, you go for the paid version. But then that gives you access to bring in information, not just from NAV, from your other data sources, your Salesforce or your back-office solution, and you can put reports together quite nicely.
24:18
You can set that up internally if you have your own IT department. And again, there’s a bit of a learning curve on that. One, to put your reports together and build your cubes nicely.
24:32
Both Jet and Power BI, the user looking at the report doesn’t need NAV installed on their machines, so they can be accessing via web page on a tablet. So, it’s a perfect solution if you have a mobile Salesforce, and they need to be on the road looking at reports. And both of them are very customizable. You’re limited by your imagination and the amount of effort you want to put in.
25:00
There are other options available for reporting. If you’ve got the IT skills, you could start using Microsoft SQL analysis services or reporting services. And then you could use Crystal Reports or Cognos or SAS or some product like that is your front end. And those are incredibly powerful reporting tools.
25:22
If you’re interested in that level of BI, we can help you with advice on which one would work for you, and we can help you with your initial setup as well. So those are the five different options. I’ll pass back to Karen
Questions
OK, that’s great. Thanks Ian. And so, this is a time to ask any questions about reporting. Anything that you’ve seen, anything that you haven’t seen so far? So, we’ll just give you a minute or two to do that and then we’ll read some out.
26:01
The first question that’s come in is a really interesting one. Can we restrict access to certain reports? OK, I’ll assume that’s only NAV standard reports. Yes. The security that you set up within NAV, just because somebody can click on an account schedule. If they’re not allowed to read the general ledger, they won’t see anything on their screen. They’ll only be allowed to see the information their security lets them view so it’s your standard NAV security. Every one of those reporting options they have different security protocols in place that you can restrict who’s allowed to see what quite nicely.
26:46
And another question, Does Power BI work from live data or exported data? Power BI works from a cube. So you’d have on a schedule, say, every hour or every 15 minutes, depending on what processing power you have available. That cube will get fresh information from NAV, and then Power BI would report from whatever is in the Cube. So, Power BI is almost live information. It’s usually 15 minutes to an hour behind reality. If you compare that to something like Jet. Jet is live, when you click Run, it goes and fetches new data from the database. And obviously, the built-in reports NAV, and ad hoc queries, that’s all live information as well.
27:35
Someone else. We don’t really have time to create report templates. Is this something TVision could do for us? And yes, it is. Certainly, if it’s standard NAV reports, that’s something we do for you quite happily. If it’s ad hoc reporting or something you need helping with, we can provide training for you. Jet reports. We provide the training for you, a bit of advice that we let you go and create those reports for yourself, And there’s a vast online community for both Power BI and Jet Reports, where users help each other for free, and then, of course, there are paid options to get help on those two more complex reporting solutions.
28:17
I see there’s quite a few more questions, and anybody that I haven’t answered your question now, I will send you an e-mail this afternoon with a more detailed answer. Thank you.
Five key takeaways and close
28:31
OK, thank you, Ian. So, I’m just going to leave you with the five key takeaways about reporting, which we hope will assist you when considering what the best method is for your business. So, first of all, you need to understand what the potential impact could be on your business, in order to justify any cost or time investment in the new reporting method. These could be areas such as saving in labour or quicker, actionable insights to take advantage of opportunity areas. Then there is the question of what you actually need to report. If you have been producing the same report for years, but don’t know where they’re actually useful or why, then perhaps question this and find out what really is important.
29:06
Next, what do you actually going to with the reports. Are decisions based on the sales commissions or legal requirements? If there’s no action that takes place from reviewing the reports, is it worth continuing to produce them?
29:17
Next, do you need to access the report out of the office or at home? If so, how easy is it for people to be able to access them via phone, tablet, or web browser? And then finally, best practice really is for somebody to own the reporting maintaining them within the central repository. So, who’s in charge of refreshing the reports and distributing them, who can the company go to if they have a new reporting requirement.
29:44
OK, so we hope you found this webinar interesting and informative, As I mentioned at the beginning, this is the eighth in the series, we’ll be sent out an e-mail saying for the next one. Please do that, on our website, you can go back and review previous webinar recordings.
29:59
So, thank you again for attending. I’m going to close the session now and a survey will appear. It will be great if you could respond with feedback, you do have any further questions, please feel free to e-mail me at marketing@tvisiontech.co.uk. Thank you very much.
