Business Central has many reporting facilities, standard reports, account schedules and on-screen reporting capabilities, but some users aren’t aware of the benefits of using these standard reports.
In this webinar, we take a look at the standard reporting functionality included in Business Central such as:
- Standard reports
- Word layout reports
- Excel reporting functionality
- Account schedules functionality (often under-utilised by BC users!)
Transcript
Introductions and welcome
0:01
Danusia: Good afternoon everybody and welcome to today’s webinar. It’s on the Standard Reporting that you can find within Business Central.
0:10
My name is Danusia Jolliffe and I’ll be facilitating the session today, and alongside me is Ian Robinson.
0:17
So, a few housekeeping items before we get started.
0:19
Please do use the question box to ask any questions and they will be answered as we go along. If we run out of time, or if your question needs a longer answer then we can answer offline directly, and don’t forget that a link to watch will be sent out afterwards that you can share with your colleagues as needed.
0:37
I’m the Marketing Director here at TVision, and I’ve been with the business for about five years, and have about 20 years of marketing experience. Ian is going to be delivering the demo. He’s been here about five years as well, and he’s going to take care of the rest of the session.
0:53
But just to give you the agenda of what we’ll be talking about today.
0:57
So Standard Reporting. Reports are everywhere, and Ian is going to show you exactly where you can find them. They’re on Business Central Pages. They are built-in as standard reports, within Excel, the Microsoft Stack, account schedules, and also Word layout reports.
1:14
So, Ian, over to you.
Standard reporting options Demo
1:16
Ian: All right. Thanks, Danusia. Hopefully you can see my screen.
1:22
So, hi guys.
1:24
One of the reasons we’re doing a webinar on reporting, we’ve done one before, but I thought it was worth doing one again. Some comments we’ve heard from prospective clients or people coming from other systems is that they’re not overly impressed with the reports in Business Central.
1:41
And I think that’s because, when you’re new to Business Central and you come in, you look at this screen, there’s nothing. There’s no menu that says “reports”.
1:50
And if you go to Sales, and there’s nothing here that says Sales Reports, so people probably think, you know, where are the reports, we want to see our reports. How do I do my job without reports? Once you get used to the system, however, you kind of realise, Yep, reports actually everywhere within the system when you know what you’re looking for. They’re really easy to find.
Reports in Business Central
2:14
So, the first thing I’ll say is, almost every screen within Business Central is a report.
2:21
So, if we look at our chart of accounts, for example, I have a list of all my GL accounts with their balances.
2:32
I’m just going to remove that filter effect. This is a trial balance. I’m seeing it on my screen. So it is a report. It’s not a printed report that perhaps people are used to, or a PDF report or an Excel report, it’s an actual live, in your face, report within Business Central, and the nice thing about this report, it’s interactive.
2:55
Any of the numbers on here, you can click on and see where did this number come from.
3:01
And you can get the background information of all the general ledger entries that make up a number on the report. So it’s a living report. It’s not one of those static, boring, old printed reports that we used to use back in the day.
3:17
This is a little bit, you know, it’s not exciting, it’s a trial balance, it’s nothing more, but you have the ability to apply filters to this report.
3:28
So you could filter for a specific range of GL accounts. You could filter it for a date range. For example, I’m going to add a date range filter.
3:38
And let’s put it in from the first of January, 21. You’ll notice I’m not putting in all the slashes and everything. The assistant is clever enough to figure that out. To the 31st of December 21.
3:54
And you will notice that as soon as I did that, this net change column changed.
3:59
And now it’s showing me the movement in each of these accounts for this date range.
4:06
So suddenly, it’s not just a trial balance that shows me the balance of the accounts. Now it shows me the movement within the accounts for a date range.
4:16
I could add another filter for a department, for a cost centre, for a customer channel and I’ll see a trial balance with the movement in those accounts for that particular dimension.
4:28
So suddenly it becomes all sorts of reports just on my screen as long as I can figure out how to use these filters. There are other places where exactly the same thing happens.
4:41
So anyway, when you get that kind of list view, if we go look at posted sales invoices, for instance, This is a good place to look.
4:51
I’m going to get some kind of sales report out of the system, so I just go to Posted Sales Invoices.
4:58
The system gives me a list of all our posted sales invoices. Again, I can filter this list to show me something useful.
5:08
Perhaps, I just want to see what were the sales this year?
5:11
I can put in the filter, I can look for the posting date, which is down there’s somewhere in the Ps.
5:21
Gone too far. Posting date
5:25
And I can type it from 010122 up to today and let’s put a T for today.
5:35
And as soon as I move off there you can see it’s figured out what today’s date is and it’s put it in for me.
5:41
And now seeing those are my sales this year that are posted on the system.
5:48
But I can get even cleverer than this.
5:50
I could save this view by clicking the Save icon.
5:57
And, I could call this my “sales up to the 16th of March 2022” view.
6:06
What if I wanted the sales for last year? And I’ve already created a report for the sales for last year And I’ve saved it.
6:20
And you can see from the 010121 to 311221.
6:25
But if I click on that, I actually got there by typing in a formula. I didn’t type in digits.
6:32
So I said, it’s the current. You go back two years, and one day that gets to the first day of last year.
6:38
Up to the end of the current, we’ll go back of a full year. That gets me the end of last year. We could create something similar, and what if we bought at last month’s sales? How do I create that?
6:51
I’ve taken a formula that says, get the current month end take away two months and add one day, that gets me at the beginning of last month.
7:04
Up to the current month and just subtract one month.
7:11
And as soon as I press Enter, you can see it’s showing me the dates for last year. It’s figured out that with 28 days in February, I didn’t have to remember that. The system has done it for me.
7:22
I can save this view by clicking this little Save icon, then come July.
7:29
If I open this view again, it will show me June’s sales. It would always manually calculated based on today’s date, which date range to show. So suddenly, it becomes a really dynamic report that moves along with you. You can still drill through it.
7:48
And it’s on the screen, you don’t need to print things out and give them to other people anymore. You can just teach them this is how you see the information that you’re looking for.
7:59
And it’s interactive. You can see where it came from, not just what that number is.
8:05
Then, going back to your home screen. Again, when I say that everywhere, even your landing page, it’s just a massive report, this is more of a KPI type of report where it’s showing me these outstanding documents that need some attention. I’ve highlighted some of these in red to show me who it’s meta criteria that I think deserves special attention.
8:33
And this is completely customisable. You can choose. If you’re not interested in purchases, you can hide this section.
8:42
If you want it to highlight when it gets to five sales invoices need attention, you can hit the personalised button up here.
8:55
Personalise and you can start changing the things on this screen.
9:01
And here within Activities, I can set up my queues.
9:06
I can choose how I want them to work.
9:09
I can change those criteria, you can see from my ongoing sales orders, when it gets to 10, make it go red.
9:17
That’s one that I’ve customised, and that’s this one here.
9:21
So, because it’s reached 11, I forced it to show me red. So it’s again an interactive, customisable report page. I’m not going to do any more changes that.
9:33
Danusia: Ian, sorry, a question has just come in. I don’t know why the camera is not pointing this way. Someone has asked, if their role centre is, for example, purchasing, how do you change it to see other roles?
9:46
Alright, and again, that’s really easy. I said this is customisable. You can customise this entire screen to be something else, so I think I’m logged in as a business manager.
9:57
I can just come to this gear icon, I can change my settings, and instead of being, yep, I’m a business manager, I can choose, oh, show me as if I was an accountant, or show me as if I was a production project manager, or manufacturing manager at my home screen, Which change, it would change the menus that I get up at the top.
10:21
It would change these queue titles that I’m getting. It will give me different options, more relevant, whichever role, I choose there.
10:29
So, you can even customise it from scratch, You can customise when these tiles fire, and, again, they are interactive. So, those 11 sales orders.
10:38
If I click on it, it takes me to these sales orders. I can chase somebody to do something about this, or I can do something about myself. Find out why they’re not being processed? What’s happening in the warehouse? So could you filter on the paid ones only then.
Danusia: Someone has asked a question about how you can filter on Paid Posted Sales Invoices.
11:02
Ian: So whichever screen you want to go to. So if you go to your Posted Sales Invoices, I’m not quite sure if there’s a paid flag.
11:13
We’ll see.
11:16
So, back to the Posted Sales invoices.
11:20
And you can see, we’ve already got it a paid filter, which we’ve saved, where it’s not cancel, and it’s not close, or, sorry, and it is closed, that means that it’s paid. If it’s not closed, it’s still not paid. So you just need to show those columns, and you can actually see it here.
11:41
You can filter by them.
11:43
You can sort by them. It’s just, as I said, it’s just a matter of figuring out what are those filters that I need to put on my screen to show me the information I’m looking for My home screen. Beyond just these queue tiles up here telling you what’s going on.
12:01
I can embed built-in charts from within the system.
12:06
I’m looking at my top customers.
12:09
But, again, I can change this, I can choose different charts.
12:15
I can select chart, show income versus expenses, aged accounts payable, etc.
12:21
I can customise the way that I’m seeing the charts, so I can look at it by day, week, month, etc. I’m not limited to just looking at this. I can embed external reports from using something like Power BI.
12:37
I’ve got my favourite accounts I’m looking at, these are my cash accounts that I’m very interested in. So every day I get a report of how much money do we have in the bank? If your time, and we’ll get into the system, it’s on my homepage, So I’ve got these KPI type reports, as well as the list style reports of just looking at the other screens. And then they are all standard reports that exist within Business Central, is just where do you find them?
13:04
So for instance, we looked at Chart of Accounts and I showed you there were a couple of reports within the Chart of Accounts.
13:14
Sorry, this report, couple of trial balances, GL Register Report.
13:19
And over here, there’s a Trial Balance by Period Report.
13:24
But, if I go and use the Search button, I just type in Trial, and I’ll start talking Balance.
13:33
There’s a section here, Reports and Analysis, and it’s showing me the top three trial balance type reports.
13:41
But it’s telling me there are 13 different reports.
13:45
I’ve clicked on those 13, and it’s showing me all these different types of trial balances I can produce, versus budget, detailed trial balance closing trombones, trial balance for the previous year.
14:01
There’s even a thing called a vendor trial balance and customer trial balances.
14:05
And if you’re not sure, what each of these reports does, Click on it.
14:10
So when your parameters on this one, I knew it does like a date, so I’ll just give it last year to 2021, and you can preview that report. You can see what does this report actually set out?
14:28
Yeah, hopefully it doesn’t take too long. Here we go.
14:33
Oh, it gives me from our different types of customers, which posting group they belong to, for. Each customer, what’s the change during that period, and what’s the change for the financial year to date concerned. Now, I know what this report is. If it’s something that people ask before in future, oh, I know there’s a report for that.
14:52
I can print it out.
14:55
But You’ll notice again, use those filters.
15:02
I’m going to choose a different report completely, one that’s got a few more options, which is my customer item sales report.
15:11
So I’m going to see which items are selling, to which customers, and in reports, and analysis, as a custom items, customer items, several reports.
15:22
So I click on that one, and it’s going to offer to use the filters that I used last time I ran this report, or I can come down, I can change them.
15:32
Each report always generally started, always generally, ask for some options for it says what’s the start date, the end date, foreign currency, local currency. This one just asked me, do I want to new page for customer. And then it allows me to put in my filters again.
15:49
So on this one, I can choose which customers I want to filter for, which customer Posting group, or I can add even more filters.
15:58
Or which item do I want or I could add item category as a filter.
16:05
And then, again, I can preview the report and there’s not many lines on this one.
16:13
Again, it’s printed out a nice report for each customer, all the items that we sold, their quantities and values.
16:21
I’m not limited to just looking at that on screen or sending it to a printer.
Excel reporting functionality – Export to Excel
16:26
I get an option to Send to, so I can send it as a PDF document or Word document. It will look exactly the same as we just saw it now.
16:35
I can also choose to send it to Excel.
16:38
If I send it, it won’t have any layout.
16:41
It will create an Excel spreadsheet that looks very much like the report we just saw, or I can send it as data only, and it’s just going to send a table of data that Excel can use to recreate that report.
16:55
So if I choose that option and say, OK, and it’s going to create an Excel spreadsheet for me, and suddenly, I’ve got the information in Excel, I can use my Excel skills.
17:08
To turn this report into something fantastic, to send to my board of directors, to send it to my sales team, tell them what’s happening.
17:17
Initially t looks ugly, but anybody who’s familiar with using pivot tables would realize this is actually the perfect format for creating a pivot table report. I’m not going to create one now, that will take a little bit too long.
17:34
But the idea is that you’re not restricted to the system, you can send stuff to Excel.
17:42
And it’s not just those reports that you can send to Excel.
17:48
Again, this screen, which is the report in itself, where I can see all my accounts, the next change for the period, that I’ve selected, the balance.
17:58
On any of these pages, there is usually that little button up here.
18:04
Send it to Excel.
18:06
And it’s going to send these exact same rules with these exact same columns into an Excel spreadsheet.
18:13
Danusia: There’s a question – is filtering on chart of accounts available only for global dimensions, or can it be used for all dimensions?
18:23
Ian: Wow, that’s a really good question. So, it depends.
18:28
So, your global dimensions. You’re guaranteed to always get them as filters.
18:34
Then, you’ve got those next six dimensions, the named dimensions. You should be able to filter on those, depending on which version you’re using.
18:42
I’m using BC, So I’ve only set up three. So I’ll see three on my system.
18:47
But if you go beyond those six, and you can have, you know, dozens, if not hundreds, of other dimensions, those aren’t very easy to filter all.
18:59
Then you’d have to start using a thing called account schedules.
19:02
That’s where you can say, I want to use these specific dimensions as my filter criteria for That’s a different topic, your analysis by dimension, and it’s a little bit more advanced.
19:18
One of our consultants can help you get that setup, or even the Help Desk, if it’s a really quick, simple one to get at those buried dimensions that are a little bit deeper into the system.
19:31
But coming back to this one.
19:32
So I just said, open this in Excel and it’s open to that exact same trial balance with the movement for the day period selected and the current balance of the GL accounts exactly as we were seeing on the screen. Again, that’s now a record I can do something with it, you know, choose to highlight certain accounts, send it over to my financial manager, said, Well, have a look at this, please.
19:58
So, that’s the list type of reports, which you can show in Excel, show on the screen, you’ve got your KPI kind of reports which you see on your landing page.
20:12
You also get other types of reports, exception reports, when something happens.
20:20
Give me a report to tell me that it’s happened.
The Microsoft Stack
20:24
So, if you’re using Business Central, you’ve got access to other things on the Microsoft stack. One of those things is Power Automate.
20:32
It used to be called Flow, Now, it’s Power Automate Flow.
20:37
You can get to it from within Business Central. And I’ve set up a pretty simple one to show you.
20:44
So, if I just start typing in my Power Automate flow, just a few words from each one and I can manage my flows.
20:55
This takes me to exactly the same place as if I had clicked up here, to get to Microsoft Stack.
21:03
And I clicked on Power Automate Flow that way.
21:06
All this way, it kind of gets me to the same place.
21:11
I’ve created an exception report here.
21:14
Let me just show you how it works.
21:18
So, it uses a criteria that whenever records change within Business Central.
21:27
So, on the Payment Terms table in my Kronos, UK Limited Company.
21:33
When a record changes, go Fetch the record that has changed, and then based on a COD condition, If it’s anything to do with the C O D payment terms, cash on delivery, then do this.
21:49
Send this type of e-mail to somebody.
21:53
If it’s not the COD, then do this…. send a different e-mail, or possibly send the e-mail to somebody else.
22:01
So now I can start getting a message when somebody starts fiddling with the payment terms.
22:05
I get an e-mail in my inbox saying, somebody’s changed the payment terms.
22:10
And it doesn’t have to be payment terms. It could be if somebody’s change a credit limit on a customer.
22:15
For instance, I can have an exception report that tells me book somebody’s made a change that you should be aware of.
22:24
Those Power Automate flows, they’re not just triggered by something happening in your database here. You can put them on a timer.
22:31
And, you can say, at six o’clock, every morning, go and get this information, Put it in an Excel spreadsheet, and distribute it via Teams to all the team members, in the Sales Department. Things like that, it’s not even just limited to Business Central. You can have it taking information, look in a folder to see if there’s a file.
22:53
If it’s a CSV, put it into Excel formats and send it to somebody. Say, it doesn’t even have to be Business Central. It’s just another one of those Microsoft tools. But lets all the different Microsoft products speak to each other in some way.
23:07
So it’s very nice to use for exception reporting.
Danusia: Someone just said they don’t think their system has an Export To Excel button.
I can explain why.So, it used to be, it was one of the buttons up here, where you had Export to Excel.
23:32
It’s moved to this funny little icon over here. Your “sharing” button where you can open in Excel or Share to Teams. By default, it’s turned off.
23:50
You would have to ask your system admin, or your super user to give you access to export to Excel. That has to be turned on user by user.
24:00
So if you don’t have it, maybe your super user needs to turn it on for you.
24:05
And that’s all to do with GDPR legislation.
24:08
Danusia: We’ve just had another question, so, how can you create a new Power Automate flow?
24:16
Again, I’m not going to teach you how to use Power Automate if you search on the web there’s some nice Microsoft Learn training also to use Power Automate.
24:28
You can log in, it gives you an example of how you can get a daily weather forecast sent to your inbox for your area. But it gives you the opportunity to create an account to play with that.
24:40
And you do, once, you kind of have got an idea yourself, you can do it from within.
24:47
Search for Power Automate flow. And you can create new ones, but I would suggest follow one of those tutorials possibly on YouTube.
24:56
First, it kind of walks you through the process of how to set a really simple one up.
25:02
See if it works for you. If it does, by all means, have it go. You can’t break anything just by reading data. People have access to tablets made within Business Central. I think you showed just how you can get to a provider. Yep. So, if you have this, essentially, you’ve got a very limited license for Flow. It doesn’t have all the features that has got most of the features.
25:24
You can search for Power Automate Flow within Business Central. Or you can go up to your apps section. You might have to searching all apps, but you’ll also find access to Power Automate Flow.
25:39
So, there’s a couple of ways you can get to it.
25:44
So that’s the web services. So we’ve done that list, type reports, matrix type reports, we’ve done, your KPI type reports, with an exception reports. You’ve also got, you know, kind of business intelligence type reports. And, again, that’s built into the system.
Account Schedules
26:02
If you search for account schedules, and I think we have done a webinar in the past on account schedules specifically, so I’ll just very briefly talk about them. Yeah. Again, you get some built-in and these are all fully customisable. You can create your own. I just created a silly little one yesterday, which just looks at my VAT accounts. So, these are the GL accounts that is looking at some formulas in just to show that it can be done.
26:32
And I create and then all from scratch.
26:34
I can look at the overview, and I can see, based on the filters that I’ve set up here, these, all the balances of my various VAT accounts gives me an idea of what my liability is going to be as I move forward.
26:53
I’m not just limited to looking at the balance of those rows that I choose.
26:57
I can look at the movement in the period, very little movement because I haven’t posted many sales this year or last year. I can even do fancy column layouts where I fit this one as well, which shows me, 12 months, January to December, if I scroll a little bit to the right.
27:21
And again, this is a kind of a matrix or row list type report. Lo and behold, I use that button, I can export this to Excel. I can do even more with it.
27:33
Um, I can change the periods that I’m looking at, I can put in filters for my department, smart cost centres, et cetera.
27:44
I can use analysis, use it here as well, if I really, really wanted to.
27:50
So you got your more detailed, slice, and dice kind of business intelligence report looks into the system.
27:58
If you know to look for account schedules and again 100% customisable you can go to town comparing actual to budget, this year to last year, month on month movements, just looking at the balances. You can put in formulas for countless integers.
28:16
The choice is there or yours.
Custom Report Layouts – Word layout reports
28:20
There’s one kind of final type of report that’s built into the system, and that’s those customer facing documents.
28:26
So when you send a sales invoice, or a credit, or purchase order to your customers and your vendors, you can customise them yourself. They are, in effect, very simple, little reports.
28:40
This is something: Please don’t do this when you a live system, go into your test system.
28:46
Never play around if you want to.
28:48
Then, you can take your skills over to your live system.
28:52v
Don’t play in the live system. These are your customer facing documents.
28:56
But if I look for my custom record layouts, if I spell it correctly, I’ll put my custom report layouts here.
29:08
This kind of shows me a whole host of reports where I can play with the layout of those reports.
29:15
So, I’ve got sales invoices here and you’ll notice I’ve got quite a few different sales invoices because I can highlight one of them, I can make a copy of it.
29:25
If I want to fiddle with a copy and send it with online version and for any of these let’s have a look at, well yeah, put on the, we’ve got highlighted.
29:36
I can say Export this Layout, and it’s going to export it into Microsoft Word for me.
29:43
Once it is in Microsoft Word, It’s going to connect.
29:51
Once it’s within Microsoft Word, I can still playing with the layout of this report, So if I’ve got some custom graphics I want to put in, if I want to put in some boxes around some of these numbers.
30:04
And if I have the developer tab activated on my system, I can even show the XML mapping.
30:14
And that’s where these custom fields come from within Business Central, how they get pasted into the document.
30:22
And it does look horribly complicated on this side. So, maybe this is something for a super user to look at.
30:30
These are all the fields that are available to pull onto this page, to add to my reports. And as you can see, this little system that I can end up. You know, we’ve only used and full of them on this report.
30:44
And as I scroll further and further down, I’ll eventually come to the actual lines of the report, that’s this section over here.
30:53
These are all the fields that we can put into the lines of the report.
30:57
You can change it, you can make it look the way you want.
31:00
When you’re finished, you save it, and you can come back here and you can save in layout, import that layout that I’ve just changed.
31:09
And you have updated your customer facing documents yourself.
31:13
Again, you don’t have to ask us to do it for you.
31:16
You can do it yourself with all of those available fields.
31:21
That said, you still have options. If you haven’t got enough reporting already to keep you busy. You’ve got your third party add-ons. You can use a product which I really like Jet.
Danusia: I was literally going to ask you. Someone’s sent a message saying, I haven’t mentioned Jet.
Ian: Yeah. I love Jet. It’s a fantastic tool within Excel. You basically press a button or to import data into Excel from your database, and it can have really fancy calculations and great looking graphics, pivot tables, with spices and dices.
31:59
You can really make some stunning reports, where you could use a tool, Power BI from Microsoft, or even from other third party suppliers. It’s not the only one on the block.
32:11
They’re all literally dozens of third party reporting tools that you can use. We like Jet, we like Power BI.
32:17
There are others that you can use if you really, really want to.
32:21
So, KPI Type Report, so your landing page, anywhere where you see a list within Business Central, it’s a record, you can filter it.
32:31
If you can figure out your filters, turn it into a record that you can save and use again and again.
32:37
There are built-in reports, and any screen, or any built in report you can send to Excel, and you can work on it even further as a report.
32:46
From there, you can do exception type reports using Power Automate Flow. You’ve got your, B I type reports using account schedules within the system, and your third party products outside the system. Jet BI, Power BI, etcetera.
33:04
So you have, literally, everywhere you look within the system, as Danusia said, right at the beginning, they’re all reports.
33:12
That’s it. Any more questions, Danusia?
Danusia: There have been quite a few that I’ve been sharing we’ve been going along.
Ian: OK, I’m done guys, thank you very much.
33:25
Back to Danusia.
Close
33:29
Danusia: So, thank you so much everyone for attending. I hope that’s given you some ideas of the reports that you can do. As always, the webinar will be sent out and the link is also available on the website. And you can also sign up for any of our webinars. Actually, there are quite a few Jet webinars on demand if you want to watch those if that’s of interest to you. If you have any further questions you get in touch. And likewise, if you have ideas for webinars then do get in touch as well. Thank you so much for attending today.
