6 Ways to Automate Financial Reports in Dynamics 365 Business Central (Video)

During this recorded webinar, we show you six methods to perform and automate your financial reporting in Dynamics 365 Business Central.

Topics discussed during this session include:

• Stock Financial Reports – 2:35
• Account Schedules – 12:00
• Excel OData Connections – 24:20
• Popdock – 33:20
• Jet Reports – 40:45
• Power BI – 53:00

The intended audience for this video are existing Dynamics 365 Business Central users and customers evaluating Dynamics 365 Business Central.

Contact us if you have any questions about financial reporting in Dynamics 365 Business Central.

Transcript below:

Tess: Hello, everyone. And thank you for taking some time out of your schedule today for our, “Six Ways to Automate Financial Reports in Dynamics 365 Business Central.” My name is Tess, and I’m the facilitator today. Before I pass it over to our presenter, I want to remind you that if you have any questions during the webinar, please type them into the questions area in the control panel on the right-hand side of your screen. And we’ll answer them at the end of the presentation. The session will be recorded and shared with you later this week. So, I’d like to introduce our presenter today, Jack Ries, who is a solution specialist on our Dynamics 365 Business Central team at Encore. So, Jack, I’ll pass it over to you.

Jack: Awesome. Thanks, Tess. Can you see me all right?

Tess: Yep.

Jack: Awesome. Great. All right. Well, thanks, everyone for joining. Like Tess said, my name is Jack Ries, solution specialist at Encore here. And we can just hop right into the agenda for today. So, if this is all you came for, here are all six of the ways that I was going to go through today. And granted, I’m going to be doing kind of a higher level demo of each of these, it won’t be in-depth training or anything like that. But it’s just mostly to give you an idea of what options are at your disposal when it comes to helping run your financial reports. I know it can be kind of a pain point for a lot of people. We don’t like to do manual copy-paste data dumps or anything like that, or get bogged down Excel formulas in a workbook, or anything like that. So, here are just a few tools or a few ways to get out the data that you need and get them in a more automated manner so that you’re not tripping over yourself and not committing errors week after week. All right.

So, we’ll start out with one, a couple ways that we can get our financial data out of Business Central directly. So, these are just stock built-in functionality. And then later, we’ll go into how can we get that data out of Business Central and into Excel? There’s a few different ways that we could do that. And then lastly, just to give you a taste, there’s other reporting tools out there aside from Excel that you could use to export this data out or connect directly to Business Central that’ll… And I’ll just show Power BI is one. But it’s just to kind of expand your mind a little bit and let you know that you do have options as long as they have a connector to Business Central, or you can kind of connect through a web service or something like that. So, we’ll go through some examples.

So, the first one here is just using stock financial reports out of Business Central, all right? And so those you would just find running out of the Report tab or searching for a particular report. There’s plenty of objects or plenty of stock reports in older versions of NAV, and even today in Business Central that you can use. So, along the left side here, I’ve just highlighted a few of them. And I’ll walk us through a few of them within Business Central. But mainly, if you’re ever looking for any sort of devoted report, not stock or out of the box, just go to that activity. And then there should be kind of a report menu there that would help give some examples of reports that would be helpful to you. So, I’m going to kick open a Business Central environment here. So, I’m logged in. And I’m just going to go to my Chart of Accounts because that’s where I’m going to be able to find a lot of the pertinent financial data and pertinent financial reports. So, as we can see here, we have our Chart of Accounts. And I’m seeing a growing trend on this, people are kind of formatting their Chart of Accounts, like it’s its own balance sheet, right? Instead of having just strictly posting accounts here, we can see that they’re putting in a heading title up there or a beginning total. And then using this totaling field here to be able to say, all right, we want this GL Account total assets to actually be the sum of other GLs that we actually are posting to, right?

So, we can leverage this format in the Chart of Accounts, we can format it like we want caps lock, bold, things like that to make it kind of look like a balance sheet. So, we could even just export this page if we really wanted to, but there is actually a devoted report called Chart of Accounts that we could run. All right. And so I could specify a specific GL or a set of GLs if I wanted to, but I can just give this a preview here. And we can see it’ll kind of look like what we had on that first screen there. We got bold, we’ve got grouped by category. It’ll tell us what kind of posting account type it is. And then additionally, it will show us proper summations if we ever are saying, hey, that we want this GL here to sum up a particular group, right? So, that can just honestly give us a very quick balance sheet, if that’s something that we’re looking for at a moment’s notice, right? Balance at a particular date. We’re able to put in specific dates in that report dialog box that we saw just before I clicked Preview, right? So, that’s one quick one. And next, we get to have like another… There’s other totally different ways of doing trial balances, right? So, we have this detail summary one as well. So, do we want to show only accounts with balances or only accounts with activities, all accounts? Do we want to show details? So, if you’ve ever noticed, like, an AP aging report, you can also choose to display additional invoices below there, so you can kind of see what makes up this number. So, that would be the detail there.

And then all these reports should have this sort of dialog box, is kind of what I call it, that pops up before running. So, it has built-in options that can, again, help you get out the data that you need in the format that you want. And then additionally, we have a few options down here on how we want to display it. I can just send this to a printer. I could send this to Excel. I could send this to Word. I could send this as a PDF. So, like right now, if I wanted to send this report to PDF, it’s now downloaded and I can kind of see what we’re working with here. So, we’re checking out only the accounts with activity. This is kind of a good register report here just to see what activity has been going on over this date range in what GL accounts. Okay.

A couple other ones here that I just wanted to highlight, I won’t go through all of these. There’s just this regular trial balance one. So, you can compare whether you want to look at just a balance or a net change over time. So, if I want to go… The columns that I want to see would be the net change. And I want to compare it to, either a budget or last year’s numbers, I get to choose up there, right? I’ll take off the balances for now. So, this could be more of an income statement report where I’m just looking at changes over time. All right. And then down here, I would specify this as kind of like my period four, for the year, let’s say. So, when I do a preview, we can kind of see those are the two columns that I see, net change for this year and then the comparison to last year. Let me zoom in, so we can see that a little bit better. All right.

And so those are just some quick reports. You don’t have to do anything, you just put in the right inputs, and you’ll get output. Hopefully it works for you. They’ll come out in these sort of RDLC reports that they’re not always user-friendly. So, some of them have this print to Excel function, which can be pretty handy. So, even if I click, let’s say, send a PDF, I think it’ll also download an Excel file for me down here. Let’s just give it a second and see. Yep, great. So, now, this does give me the ability to maybe massage this or get this into the format that I need. It might not always work with us. This will be probably really good for an ad hoc report, just because, you know, there’s no logos or there’s no information. I mean, the information is on this tab over here as to who ran it and when. But there would still need to be a little bit more manual manipulation as to how are we going to get this into a nice, tidy format that we want to send out, maybe to our CFO or something like that, who wants it real nice and neat and easy to read, so. But otherwise, notice that these are just hard-coded values that the report output. And we could kind of work with this if we really needed to, but it doesn’t really help us in terms of automation, all right. But it is kind of a nice thing. If you ever have a report that you need, and basically you just wanted an Excel format to store off month over month, that print to Excel option can come in handy. All right.

And then one last thing I just wanted to show here for these stock financial reports. So, let’s go back to this trial balance one. There’s a thing in Business Central now where you can schedule reports. All right. And so I can create a schedule here to run every week and pick a start date, end date. And I can specify what report do I want, what kind of output do I want, and I can click okay. And now it’s going to run week over week and it’s going to display down here in this report inbox, down here. So, notice I created one before, and it just kind of loads there ready for me on my role center homepage, and can always take a look at these. It’ll tell me which ones I’ve looked at or haven’t looked at yet. So, just another good way of just automating reports. If I have something that I don’t want to have to go in and manually run every week, I can have it kind of do the work for me, which can be pretty helpful. So, that can be done for all or most of those out-of-the-box reports. That helps a lot. All right. So, we’re gonna head back to PowerPoint here. So, yeah, the key takeaways are, look for the report menu in any sort of module or activity, you’re going to find the pertinent reports that might be able to help you out or get you the reports you need out of the box. So, first check there, if there’s something pretty basic that can help because when they’re done the way you want, it’s pretty nifty. And the work doesn’t have to fall back on you and kind of recreate the wheel in another program.

All right. So, just some pros and cons. Again, these are totally subjective on my behalf. I do love that you can pick different report options or report filters to specify your needs. And then additionally, the way in which you can send or output them is pretty nifty as well. Scheduling tools is a pretty awesome tool that I enjoy, and I see a lot of people use. And then lastly, for cons, it’s not always friendly in Excel. So, you will see on some reports that clicking that send to and going to Excel doesn’t really come out in a very Excel-friendly format, it deals a lot with formatting, and margins look really way off, or merging cells, that doesn’t really help. And then lastly, if you can’t find a stock finance report that works for you, or maybe you find one that works but you just wanted to add an extra column or something like that, or just move things around slightly, there’s really no good user-friendly way to do that, you do need to pull in the developer to help you with that. And so that’s kind of a… It’s a not fun barrier that you kind of have to get over if you want to, in the end, get the report that you need. So, they can be really helpful if they’re there. So, first check there, if there’s something that works, otherwise, you might need to resort to something else, so. I think that’s kind of precisely why NAV and Business Central have this account schedules feature. This offers a little bit more customization and flexibility if you need to be able to group accounts or format things a certain way.

All right. So, I won’t spend too much time on this, but I’ll try to give you a high-level overview of what account schedules are. So, used to get visibility into the GL entry data that resides inside Business Central. So, what you would do is you’d specify a few things, an account schedule, which would specify the different rows. So, it would be either it could be every row is its own GL or maybe a particular row is a grouping of GL some together, something like that. And then you can specify the columns, this is kind of similar to those stock reports that we were looking at earlier, what kind of columns do you want? Do you want balance? Do you want net change? Do you want a net change compared to a different period? Do you want to know what the variance or the percent variance is there? You can specify all those things. So, you can specify, I want this set of rows to… I want to generate three reports, but the rows are gonna stay the same, but I’m just gonna have different column layouts if I want.

And then in the report, it’s gonna read from one of two places, one is just directly from the general ledger entry table. So, that’s just your list of transactions that hit the GL. It’s gonna have your GL number, posting date, document number, amount, things like that. And it’s going to sum them together to output a net change over time for a particular GL, or it’s going to come from a set of analysis view entries. And these are sort of like condensed GL entries. I’ll walk through an example, you can kind of compress them so that they are grouped by month, let’s say, or you can also specify. The more popular way that I see this used is you can specify up to four dimensions that you want to use for this particular analysis view. So, you can’t necessarily filter on all, you know, 8 or 12, or how many dimensions you have at one time. But if you want to pick a specific four, for a particular report, you can do that in the analysis view entries.

Okay. So, let’s hop in just real quick and see what those look like. So, let’s scroll to the top here, we’ll go to my account schedules. And so they have some out-of-the-box account schedules that we can take a look at. So, you can ignore the ones that begin with an I, but here, it’s like they have their own balance sheet. They have an income statement, you know, cash flow, things like that. These are all the account schedules that we see here. So, if I open up balance, so again, this is an account schedule that the system generates. So, based on your GL, if you add your own account categories and stuff, you can actually run a function to update this out-of-the-box account schedule. Otherwise, they’re asking, do you want to save off a copy of your own and leave this one so that you can manipulate it all you want? So, I’m going to say I do not want to make a copy. I’m not going to really make too many changes today, to kind of want to show you what’s going on under the hood a little bit.

So, here’s the description. So, this is kind of what we see in the row by row. And then we can see what GL accounts are we totaling, is there a formula here? So, like, for instance, this formula is saying, sum together all of the previous rows above it, P0002 to P0006. So, that’d be these rows here. So, whatever values that get calculated for these rows above it, sum those together and output those here, okay? You can also, you know, decide, do you want to do balances or net change? Do you want to show this row all the time, or only if it has a value? I think that’s pretty helpful, you don’t always want to see a bunch of zeros if you got a big Chart of Accounts and maybe not a lot of activity within them over a given period, right?

Additionally, you can specify, do you want to bold this particular row, italicize it, underline it? There’s also one for double underline. Do I want to start a new page on this row, if I want to separate, you know, two distinct categories? Things like that. So, those specify the rows there. I’ll print one later and we’ll come back to this, and you can kind of see how those two things map together, okay? I’m gonna go back a level. And over here, we have the default column layout. And so right now this unbalanced one. I would imagine that this is just a balance as of a particular date. So, again, the columns can be totally different things, I could very easily run my balance sheet GLs and look at net changes over time. And that would be this one right below it. But the default one I’m specifying here is a balance column. So, if I go to my column layout, let’s see, I think I have two or three of these here. But right now, this is just saying, we’re just going to display one column, it’s going to be the balance as of a particular date, whatever, when you specify in the dates based on my GL entries, and I always want to show this column right?

There’s another one called net change, pretty basic one is just I’m going to give it a range of dates. And it’s going to tell me what’s been the net change over time, right? And then to add a little more flair, there’s another one here that we could put in called periods. So, I could say, I would specify a period in my… Before I run the report, I’d be able to put in a date filter and say I want to run for a particular period. And then this would calculate what was the balance as of that date for that period. And then also, it’s going to calculate backwards and say, what was the balance one period ago, and then two periods ago? So, these are going to be three distinct columns that I’m going to see. Again, my account schedules are my rows, and then this column layout are the different columns that I see, okay?

And then coming back here, notice that if the analysis view name is blank, it’ll likely just run from the general ledger entry table. Otherwise, we can specify a particular analysis view here. So, I’m going to go show you kind of what I’m talking about here. Let’s see, on analysis view, analysis view is here. All right. So, I’ve got two of them here. There’s a general ledger one, I can’t remember if I made this a long time ago, or if this one came out of the box, but all it’s doing is it’s taking every GL entry and compressing them per day. So, if I have 10 transactions to my checking GL account on a given day, the analysis view table will only have one entry for that GL on that given day. All right. And then additionally, I’ve done one here, where it’s just the date compression is per month. So, it helps when you’re running reports, if you’re looking over a large range of transactions or a large range of GLs.

It’s nice if you have this work being done behind the scenes ahead of time where it’s compressing it. If you only need to look at it on a month-by-month basis, it’s kind of doing a lot of the work for you such that reporting is super quick, and you doesn’t need to look over thousands of transactions per day. It’s already done behind the scenes there. And so I can kind of show you… So, let’s see, it looks like I have 4,377 GL entries. And then for this monthly one, analysis view entries… So, there’s a table here. Okay. Great. And I only want to see though, analysis view called month. Okay. So, these are all the entries in that analysis view that’s compressed monthly. And if I were to, let’s just open this in Excel and see how many entries we have. So, remember we had like 4,300-something. If we compress it monthly, we only have about 188 entries. So, again, it’s every GL. And we can kind of see over here, it’s compressing them. So, notice we have the 60,700 account, they’re just being compressed on the first of each month over here, okay? So, again, that can really help and speed up things with report running.

All right. So, I’ll print one here, just one quick account schedule, so we can kind of see what’s going on. I will use that general ledger one, that should be fine. So, if I print this, it gives me the options here. So, it says what account schedule and what column do you want? So, again, I could mix and match and say I want the net change, but I want the account schedule that has balance, but I’ll just do balance, balance for now. And then again, we have all those same send-to and preview options that we had for the stock reports, which are pretty handy. So, Ill’ just do a quick preview here. Let’s see what we got. So, it’s pretty basic, I’m not going to lie. It is in landscape mode by default, which some people really like, some people really don’t like. And just remember when we were looking at that balance account schedule, really the only row information that we’re going to see here is the description field. All right. And then over here, this is…remember the column was dictated by the column layout, just balance. So, this is just balance as of a certain date. All right. And we have all information up here. So, the presentation of it may not be there, there are a few kind of tricky ways if you want to get at better information or even export it to Excel. So, I could come here and look at an overview. And then I think they even do have an option here to export to Excel. So, let’s create a new document. And I think this one even shows the row number which can be a little helpful. Let me pull this over.

So, this one might be a little more helpful. Remember, that’s go to the overview. And then you can export to Excel. All right. So, again, even this is just so more of a data dump. So, it would be nice if you could kind of perfect that output. And if you tried the print…this is kind of what I was talking about before. If you try to print an account schedule, so let’s come back here. And if I wanted to send this to an Excel document, this is definitely where kind of that murky layout comes into play, or it’s not real fun to work with. So, you can kind of see here, I got rid of the grid lines, that’s fine, but it’s not really nice and tidy like that last one that we saw where we exported from the overview, okay? So, again, some of these are merge cells, and it’s just not fun to work with. So, just a heads up, based on whether you’re using a stock report or account schedule and want to export it to Excel or something to play with it, it’s not always going to be nice and tidy. All right.

Let’s head back here. So, pros, yep, you can use it directly inside Business Central, you can schedule them, you can customize rows and columns, which might not be there for those out-of-the-box reports. And then with analysis views, you can pick kind of maybe those other dimensions that maybe aren’t in your top four, but you definitely need to be able to run reports on, you can do them with analysis views. Then cons, yeah, it only shows that description row. So, if you really needed to, you could put the GL-column or something…or excuse me, GL-description if you needed to see that on the printed report. Yeah, modifying ad hoc doesn’t work too well. It’s tricky to export into Excel, I showed you that one way where it’s kind of clean. But most of the time, if you want to print that account schedule report to Excel, it’s not very fun. And then similarly, if you want to add a logo or you need something just a little bit differently, even changing it from landscape to portrait might be a better fit for you guys, you might either need consultants or a developer to help you with that.

All right. So, now we’ll get into some stuff. We’ll kick it out to Excel. So, how can we do that? So, one way to do that is you can use your OData feed from Business Central, and connect that to Excel. And then from there, that will pull in your list of whether it’s transactions or your chart of accounts. It’s just basically going to pull as a live query into Excel that you can then run tools and Excel off of, maybe like a pivot table, or formulas, or things like that. And the beauty of it is it’s not a copy, paste. You can always at a moment, click Refresh, and you’ll get the latest data. And that way if you have everything else kind of formatted or set up the way you want, really all you got to do is open up that Excel workbook and have it Refresh, and now your report is ready to be printed, which is pretty nice. All right.

So, I’ll show you real quickly how to do that. So, talk to your partner about web services because if you need something like this they’ll be able to help you. So, by default, there’s a lot of endpoints that you can either export data from, or actually, these are endpoints that you can import data from if you are integrating with other programs or ISVs. So, notice that a lot of these queries here, these are just all out of the box, for the most part. You can create your own queries, I will say that. So, perhaps the table that you want isn’t here, or maybe you say, hey, I only want four columns from this huge data set, I don’t have to load this whole data set every time. You can create a data set that’s pre-filtered, or has the right columns at a time, which can help. So, I won’t cover that today, but it’s just something to put in the back of your head.

So, for instance, like my GL entry table, I believe that’s here. Let me see here, general ledger entry table. Yep. So, this would be a table that will show me all my general ledger entries, which is pretty nifty. And so I can connect OData, my OData URL to this. So, let’s see. I’m just going to copy this here. I’m going to go over to Excel. And I can go to my data tab up here. And that says I want to get data from an online…other source. And I can pull in this OData feed here. All right. And I put in that URL there, I click okay. And since I logged in earlier, it normally would ask me for a login, but I’ve already used my credentials once so it knows to kind of store them again. Otherwise, the thing that I would need to do, and notice it on that screen there, it did ask for a login as well. And so saying, because I didn’t put any credentials then I can’t get to that info. So, that basically would happen in Excel, if I didn’t put in the right credentials, it would have not allowed me to get in. But anyway, this is where I would put in my username. So, right now I’m just admin in this kind of mock environment. I put in admin as my username, and then I would need to generate this web service key down here. And this would be, I believe, it’s unique per user. And so this is what I’d copy and paste, and only I can see this, if some other user came in, these would all be just asterisks across the board.

So, coming back to Excel here, so now I’m connected. So, notice it says, hey, does this look right? Is there a quick preview of it? I can transform this data. And I’ll just show you that real quick. Otherwise, I could have just quickly loaded it there. So, it’s a big advantage if you know…this would be, you know, Power Query editor, you can see that up here, or you can create your own columns kind of like in… This kind of looks similar if you’ve ever used Power BI DAX or something like that. This would be a way where you could strip away all the things that you don’t need.

So, I’ll just show you real quick, like, so right now, this is my query number one. And these are just going to be a set of steps that I can do to manipulate this. So, for instance, I don’t need job number here, I’m going to right-click, and I’m going to remove that column, okay? That’s just one less column, that’s going to actually make it to Excel. So, that’s probably going to reduce load time, which is going to be really helpful. Maybe I don’t like the column ordering, and I want to put the description before the GL account, I can do that. And notice that it’s kind of recording all these actions over here, so that when it gets the source, the next time it loads, it’s just gonna walk through those actions until it ultimately gets to the last output that I want. And then I’ll click Save and Load and then that’s what loads into Excel.

So, it’s a little bit of a learning curve, if you want to try to trim away some of that excess fat before it gets into Excel. Otherwise, what you would have seen is just… So, like right down here, you can see running background query. So, let’s give it a second to load. There it is. So, now it’s an Excel table within Excel, right? So, up here, table name, Query 1. So, what’s cool about using table names is you don’t have to define ranges, you don’t have to say, oh, let me rearrange this from A1 to D200, or something, or oh, since I loaded, we’ve had 10 transactions that would need to be, you know, 210. So, if it’s a table, there’s a lot of powerful Excel tools such as pivot tables that I could use. So, I could say run a pivot table off of this, okay? And if I came over here, I could say, let’s see. Let’s just do GL account, number as my rows, let’s see, amount as my values. Maybe I even put, let’s see, posting date as my columns. And so it knows to group them.

So, this is just Excel functionality reading this table over here. So, I can kind of quickly come up with the pseudo period report with my financial data, you know, I could pull in my account name over there if I want. And then from there, it’s just a matter of, you know, how well can you like maneuver a pivot table to look the way you want. If I want to show us in a table form, let’s get rid of subtotals, stuff like that, right? So, notice that if I were to… I can come over here, and I can either right-click in this table, and I hit Refresh to get the latest data, or come up here to data and hit Refresh All. And that’s going to get the latest data, and then subsequently, it’s going to update this table for me. So, if I have this page, which is basically just reading from the foundational query, in the other tab, I can open up this doc, look at the latest totals, because I think there’s… In a Property here, I can say…let’s see, well, somewhere on here, I won’t go into it. But there’s a way where you can have this load upon opening this file. So, that way, you can be sure that every time you load this, you’re getting the latest data. So, that’s the direct Excel connection.

I think that’s kind of all I wanted to say about that, it’s just a much better option than having to copy and paste your entries into Excel, which I’ve seen people do, which is definitely not preferred. That’s why you’re just getting in live data. So, notice, you could pull in another query and you want to join them together. So, if I had, like… I was kind of looking for my chart of account information because you have account categories or account subcategories, those would be perfect fields for a pivot table like this. And you could group them by assets or liabilities, stuff like that, okay? All right. I haven’t seen this used a lot. So, definitely talk to a consultant or myself, and we’d be happy to help you out. And it’s definitely…I just know that there’s plenty of people in the financial world who know Excel. And so maybe this would be a really good fit for them.

So, like I said, provides a live refresh. You’re able to leverage Excel. You can customize the appearance, which just kind of means for me, like, you could move that pivot table around, put a logo in, put other formulas in there to say, what’s today’s date. And then when you print it, it has all the information that you need. The negatives there, yeah, like I said, I don’t see a lot of people using this. So, you might be running into problems on your own, and there might not be a good community of people that could help you out. It can be a lot of work on the front end, like I said, to strip away all the fields and tables and make sure all the table joins correctly. And not everyone knows Power Query or pivot table, or at least are not experts enough to really get the output that they want. So, once you can get it set up, you should be good to go, kind of like with most reporting, right? If you have the setups, and then from there, you automate, which is really helpful.

All right. Next, this is a tool that I stumbled upon called Popdock, we do have a couple clients using this one. So, this is kind of a similar feature. This is, you can kind of create your own data sets, kind of like what we saw on the Web services, but this one’s a lot more user-friendly. And then additionally, it does have its own Excel add-in which kind of similar to what we just did, pull in the data that we want as an Excel table. So, I’ll walk through that demonstration a little later. Again, I’m not necessarily the spokesperson or the resident expert. So, if we have any people from Popdock watching or listening, you know, I apologize if I haven’t… There’s plenty of other features that I don’t cover. These are the ones that were pertinent to me for this presentation, all right? And so, like I said, it’s an extension on a Business Central, and it provides real-time reporting. They call it a self-service BI tool. So, you can actually connect to a lot of different connectors. A lot of different other programs just beyond, so Business Central is right here, and this is listed as one of the popular connectors. It can also be a way to get data out of plenty of other integrations. So, if you see in Dynamics GP, Payroll NOW, and stuff like that, they work with a lot of these other things as well.

All right. And so let me just log in real quick back to my Business Central. So, this is an extension within Business Central as well. And I can see this new menu option I have up here, Popdock, and this sort of module within Business Central, the biggest advantage is that it can show you this kind of quick out-of-the-box reports. So, if I just wanted to see a quick balance sheet, this was kind of a preset view that came out of the box, like I said, and that you can run this report, export it to Excel, things like that. There was another one. So, notice it applies to not only finance but a few other places as well. So, if I want to look at an income statement, let’s see, I can pick what columns I want to see. So, if I want to put in net change over there, let’s see, those are zeros. So, maybe I pick specific filters, date is between, let’s say… I think my sample data goes back last year, so let’s do this, maybe May 1st, or something like that. Let’s see if we get some dollar values. There we go. So, notice these here. If there’s some out-of-the-box reports that work for you here, great, export those to Excel and have fun with them.

So, that’s not necessarily the most powerful feature that Popdock has. So, I can go online… Let me just log in here real quick and create my own data set. So, I talked about that before. So, if I go to connectors here, I have my one connector to Business Central. And from there, here are some of those default lists that I was showing you, here’s the balance sheet, there’s probably an income statement down here. Yep. And I’ve created my own down here called Jack Test. So, you can create your own custom list. I’ll kind of show you what goes into that. I said I’m putting in the finance group. And this is going to combine data from a couple different sources. One is the GL entry table and another one is the chart of accounts table. And so I’ve kind of told them how those look. So, if you look at the GL entry list, there’s a GL account number that can then map to the chart of accounts. And maybe now I can pull in. So, if I go to my fields, I can pull in things like account category, and anything else from the Chart of Accounts table.

So, I have this sort of query ready to go. And then with that, I can go to my Excel add-in, and actually, I’m just going to open up a new sheet here. And notice I have this insert Popdock data as an add-in. All right. So, if I log in, let me just log in, ries@encore.com. And then let me just get my password real quick. Oops. It’s gonna let me pull into Excel here. Let’s see. We got the finance group, I can pull in any of those out-of-the-box reports plus that custom report. So, if I want to pull in Jack Test here, so I just kind of stripped down the chart of accounts a little bit…excuse me, the GL entry list. So, I have a list of GL transactions, their posting date, what’s the GL account name, what’s the entry number, what’s the amount, things like that, those are all really helpful things for me to be able to report on.

And I can just come over here and click a button and say I want to pull this into Excel, insert data. So, it’s loading, and boom, there you go. Again, this is also an Excel table up here. So, I can do a lot of all the same things that I can do, like I did that with that last OData entry. So, I can turn this into a pivot table. So, I can kind of like what I wanted to do before, account category, what’s my balance by account category? And then columns can be over a certain period, stuff like that, right? So, it’s just another good way to get the data dump into… Let’s see, where was that sheet? Here, get the data dump into Excel. And that could be all you need is if you were able to kind of configure that query to get what you want at a time, then great, now, it’s an Excel for you. Or if you just need a data dump, and then leverage Excel tools, maybe we turn this into a dashboard, you can definitely do that.

All right. The other thing… I’ll get to that in the next slide here. So, pros, definitely user-friendly way to customize reports, you can pick your own columns, you can put in your own filters, you can join tables. So, that’s why I kind of say it’s a little bit better than OData because you’re kind of limited by a Web service, which doesn’t always give you all the columns that you need. The cons are, it’s not free. I’ll get to pricing in a second. And report joins, it’s just a tabular structure. So, if you need a little more customization or just a little more complexity reports, it might not be able to do it. But I think for a lot of financial reporting, it should be able to do what you need. And so, pricing here on Popdock, let’s go straight to the website. So, let’s see, five app users $99 a month, limit of 1,000 different queries. And then, as you can see, that shouldn’t be a problem, you get the Excel add-in. There’s a mobile client that you could use to read reports, just from your phones, you don’t have to be logged into the program that you’re using. So, there’s plenty more to Popdock. So, if this kind of piques your interest, be sure to reach out to us and we’ll be able to help kind of set you up with them. All right.

The next one is definitely a tool that I use quite a lot, Jet Reports. So, I could probably give a whole presentation on Jet Reports just for how extensively I’ve used them, or how many times I’ve given trainings to client stuff, but it’s definitely a great tool. So, it is a product from insightsoftware, you can provide reporting inside of Excel or on a Remote Hub. Again, if there’s anyone from Jet listening, this is just a very high-level presentation or demo on what it can do. So, apologies if we don’t get to all of its capabilities. And they do tout that you won’t require programming skills to create and distribute reports, which is pretty nifty. So, I do want to… I will go back, sorry, I do want to give one comparison here. So, on their website, they have a few different products. So, you might have heard of Jet but maybe you’ve heard something different whether it’s Jet Basics or Jet Express. So, this is kind of the free version where you do get financial data and table builder, but it’s only for NAV and GP, not for Business Central, okay? But for Business Central, you do get this product called Jet Reports, kind of that middle tier. Most of that reporting is done out of Excel using formulas, and it’s pretty awesome. Let’s see.

And then lastly, what you might have heard of is something called Jet Analytics or Jet Enterprise. And that’s definitely your full analytics BI solution, you know, provides a data warehouse. And so I won’t go into that today. But that’s kind of that next level, if you really need some serious reporting data and distribution, that’d be the way to go. But right now, for today, we’ll just talk about Jet Reports, okay? So, just trying to clear up any confusion. So, within Excel, actually, I’m going to open up a new Excel session here, start clean. So, what I’ve done, ahead of time, is set up my Jet add-on. So, at the top here, you might have seen this before, there’s a Jet add-on. And I’ve connected to my data source, and then also my company. And I can toggle between companies pretty easily up here, okay?

So, I’m going to show just a couple of quick tools. These are kind of the more basic ones. So, one is this table builder. This is going to kind of mimic what we’ve seen today. So, I can build a query on the fly from my GL entry table. So, let’s see, I think it’s G/L. Yep. GL entry table. So, that’s the table I want to pull data from. And now I get to pick what fields from this table do I really care about? So, I’ll look at, you know, posting date would be one. Maybe the GL account number would definitely be one that I want to look at, you know, document number. Sure. Definitely the amount. And then in the past, this wasn’t available, but in Business Central… Well, down here, there’s a flow field for… I can also pull in the GL account name, right?

And I can move these around if I want to decide what order they are. So, if I wanted something else from the chart of accounts table, let’s say, I can create that link. So, excuse me, I believe it’s called G/L, GL account. So, normally, it’s called Chart of Account but the table is actually called GL account. So, I’ll create that link here, where… Let’s see the account or the number, the GL account number in the GL account table equals in the GL entry table, this is called I believe the GL account number. So, now it knows for every instance in the GL entry table, if we see this GL account, we can map and pull in from this GL account table, things like… So, let’s go into here. The biggest ones that I like are account category or subcategory, so I’ll pull that in with the variant, okay.

And then, additionally, I can also add a filter to say, you know, when I run this, I want to pick my date. So, I could do 01/01/2020, and then all the way through 06 /01/2020. Okay. And then I want to be able to change this every time I run the report, this isn’t a hard-coded report. So, now, what spits out is a series of kind of inputs, and I like to call this kind of the guts of the report. But really, what you would do is you’d run this report, and I get this popup over here to say, “Hey, are you sure this is the posting date range you want?” And I’d say, “Yes.” All right. So, it’s gonna run this report and the output, again, it’s going to be pretty similar, it’s going to be just a data dump of my GL entries. And again, I can put a pivot table off of this or link formulas to read off of this table, no problem. It’s just, again, we’re trying to get away from manual copy, paste, or having these workbooks with eight different tabs along the way, if you can combine queries into as few sheets as possible, you’re gonna be in a much better spot.

So, similarly, to get the latest data, all we need to do is to rerun this report. All right. And so, that’s one way we get out our financial data. If I go back to my design here, let’s see, I’m actually gonna delete this tab. Another one is through this thing called the GL Function. And so here, I can return, I’m actually going to go back to my Business Central environment, so to make sure I get the right data here. So, if I’m going to go to my general ledger entries, great. Looks like a lot of stuff happened 3/1 to 2/1, okay. All right. So, I’m gonna go back here and say, I want to pull in, you can specify either a budget entry, a balance, or net change from your GL entry table, credits, debits only. So, I’m just gonna do the full balance. And then you get to specify what GL account or GL accounts you want to deal with. So, the one I saw was 60,700. Let’s go back. And then over what start date? I want to make sure I get the right entries here. Yeah, we’ll say 1/1 to 3/1. Great. I can also put in any other filtering information, dimensions. Maybe I specify a specific company here. We don’t worry about that. But the cool part is what comes out, it’s a formula up here.

So, this GL formula, notice my syntax got outputted here. So, I want the balance from this GL account over this date range. And notice, it just basically summed up these three entries for me. Oops, yeah. So, these were, I’m guessing were all 1,000. Yep. So, what it did was it summed up these three entries, it gave me the net change over that period of time. And then from there… So, let’s see. You can leverage Excel pretty easily using these formulas so that you can quickly create a balance sheet and income statement, there’s this tool called Fast Financials that can also give you that very quickly, that will leverage those GL formula. So, if I said, I just want to see these first assets in here, I can drag those onto my page. And it’s going to add in for me a start date, end date field that I can manipulate and change, or any sort of dimension values that I want to be able to filter on before I run the report. Just give it a second here.

It’s always a little slow the first time and then it kicks into gear. Okay, we’ll close this. So, I mean, this is a quick balance sheet. I mean, I could have gotten a lot further. So, notice now when I click Run here, I can pick a lot of… This kind of just set this up for me, it knows what company I’m coming from. I can pick a start date, end date, I can even make it dynamic based on what’s today’s date. So, if I wanted the end date here to be equals today, boom, 6/8/2021. So, now when I run this, it’s going to have that filled in for me, which is pretty nice. So, I might not even need to have this popped up here when I can run it. And then really all the output here, these are just values, I can drill down into this. So, actually, this provides a direct link back into Business Central to show me here are GL entries that we sum together to come up with this value. So, that’s pretty slick, that drill-down capability.

There’s plenty more to it, that I could show you. One other great thing with Jet is that they do provide a lot of sample reports to kind of give you a flavor or a template to use. So, if I said, yeah, we’re using Business Central. And let’s shorten that. We have Jet reports, great. Let’s collapse, collapse. I don’t care about that report. And then I want to look at all your finance reports that are out-of-the-box. So, you can sign up and look at these, it’s all free. So, it says View and Download the sample report. And actually, when you do that, you don’t just get this one, you actually get the whole collection. So, here’s just a folder of these Jet sample reports that you get. So, if I go to Reports here, boom, these are all the ones that you get. And if we open them up, they will be kind of live and you might need to kind of remap them a little bit to make sure they work.

So, like down here, what have we got? Income statement year to date versus last year to date, you know, let’s just open this up and see what that looks like. So, they give you an about just to help you configure it. These dashboards are kind of out-of-the-box here. These are reading formulas, and they have hard-coded values on the side to help you out. So, this is just to kind of give you an idea of what, you know… We’re all trying to figure out what information do we need to be able to see to better help run our business, right? And let’s not let our reporting limit ourselves to that, right? We might have been doing some things the same way for a really long time, but there’s a lot of cool reporting tools out there that could help leverage that, okay? So, that’s Jet.

Let’s see, let’s quickly go to the pros and cons. So, we talked about all the pros, flexible reporting. Excel users, a lot of functionality is already in Excel, so you can leverage that. And you won’t have to learn a whole lot more. Provides real-time data, user-friendly. So, it does cost money to… And then the only other thing I could say, just from personal experience is complex report structures, kind of like with any other program, you take a long time to load. And then maybe that’s where you’d want to move in more into like a data warehouse or an analytics atmosphere. Okay. So, again, just bare bones just to get an idea for kind of users or price. If you talk to us, or if you talk to Jet, I know they’re pretty flexible in how their pricings work, but just to get an idea. If you’re connecting to one source, and you have one person creating and distributing reports, and then maybe, you know, five people who need to be able to rerun those reports, not change their makeup, but just rerun them to get the latest data, that’s about, you know, somewhere between $203 a month, just for that kind of package. And then there’s add-ons to that, or they can work with you if you’re a special customer, whatever. So, don’t take my word for it. If you’re really interested in Jet Reports, you can reach out to us or reach out to Jet and we’d be happy to help you out.

And then the last one, which we’re going to touch on. So, this is Power BI, I’ll say this once, it’s not admittedly a really good financial reporting system. It’s mostly used for dashboards, and you know, KPIs and stuff like that. But it’s Microsoft’s data visualization software, it’s the leader of the pack. It is growing rapidly. It does provide a direct connection in to Business Central. And, so again, this is to expand your mind a little bit and just let you know there’s other reporting tools out there that can hook back into Business Central if you have your own preferred ones. So, try to take a look and see if that exists for you. So, if I were to open up a Power BI, desk for desktop session here, I’ll just show you real quick. It’s gonna look and feel a lot like that Excel, like that Excel OData, Power Query stuff that we looked at. So, if I want to get data here, let’s see. Yep, give it a sec to load. There we go. So, these are all the different ways I can connect. I can import an Excel table, a text file, but I’m going to look for this direct connection in Business Central. I don’t need to put in the OData URL. This actually knows better and will pick out the Web service or the right table for me. So, when I do that, it’s going to give me a list of all those Web services that we saw earlier. First, it’s going to let me pick what environment I want to deal with. And then similarly, you either load it straight away or transform the data in Power Query, kind of like what we did in Excel. So, I won’t go through a full example, it’s going to look and feel a lot of the same way that we did before.

So, if I expand this Cronus USA here, we should see things like ledger entry tables, chart of accounts, there we go. All right. So, once we load in the right data, let me just show you real quick what that would look like in the sake of time. So, I kind of just quickly loaded up a Power BI project where we have the right data in there, and, you know, we can put in visualizations, and things like that. There’s one add-in called Zebra BI, that I’ve heard is a really strong add-on to Power BI, that is really helpful with visualizing financial data. So, just real quick, let’s see if we can get a look at that. So, while this loads, actually… Okay, so you can see here, pretty basic chart of accounts, I don’t have much of imagination here is I just put it in a table format. But you can put these slicers in that will update your values on the left over here. So, notice the amounts change when I switch the posting date on a slider. That’s pretty cool. You can do that in Excel as well. And then, let’s see, if I want to get something from AppSource here. Like I said, one that I’ve kind of kept hearing about is this Zebra BI. Let’s see if this will load. Yep, Zebra BI tables, pull that in. Actually, I already have it here. Yep. So, if I go over here, click on the Zebra BI visualization, I’ll need to kind of reformat this stuff, but just kind of give you an idea that there’s other tools out there that might be able to help as well.

So, let me go to their website real quick, Zebra BI, and it can load actually an interactive BI table for us here. So, let’s see, you know, if you really want to get really complex, you got to kind of know what you’re looking at, right? So, delta versus prior year, delta versus prior year percentage, percent of revenue, this is kind of just an example of what you could pull into your Power BI as long as you have the data set up correctly. And then in terms of pricing, search for our blog, Power BI Free vs. Pro vs. Premium, it’ll give you an idea of the different licenses and what you can do within Power BI, and how much they cost. And then lastly, Zebra BI does have its own way to calculate your price. You can drag, just only because I mentioned it, I figured it’d be helpful just to let you know, kind of give you an idea of what we’re working with, so. So, lastly, let’s go back to here. So, again, great visualization tool. It’s mostly better for the operational reporting, but there’s a lot of other add-ons out there that can help as well.

So, it’s all I have for now. Thank you. Just in under the hour, we might have time for a few questions. So, Tess, you know, how are we doing with time and questions?

Tess: Yeah. Thanks, Jack. We’re doing good on time. We have a few more minutes. So, if anyone has any questions, please submit them into the questions area. We do have one that has come in, and we’ll start off with that. So, you’ve been showing us financial data, can we also get operational data using these tools as well?

Jack: Yeah, great question. So, yes, you can, is the short answer. So, wherever we have those Web services, there were other sort of tables that you could pull in like customer data, or even customer ledger entry information or item ledger information that could also help if you want to know what’s inventory that we have on hand, what’s our turnover, things like that. So, for a lot of these tools that I’ve shown you, you’re not limited to just financial data you could look at all other parts of your business within Business Central as well. Great question.

Tess: Awesome. The next question is, where do we go if we want to learn more? So, you showed some websites today, but kind of how do we get in touch? Is the question.

Jack: Yeah, I’d say first reach out to Encore, I believe. Let’s see, on this next slide here so contact us at encore@encorebusiness.com. We’ll help reroute you to someone whether it’s in sales or a consultant that can help you out as well. You know, we do work with these vendors like Jet or Popdock and pretty closely so we can kind of work together to help get you the best support and help your need, and give you the right tools to help you succeed.

Tess: Awesome. Well, we’re at the hour and no other questions have come in. So, thank you Jack for the information today, and thank you, everyone, for joining us. Have a great rest of your day.