You’ve heard the saying, “It’s like finding a needle in a haystack.” Have you ever felt that way while trying to get the information you need from your General Ledger?
Since there are thousands of transactions in most every GL, and they are not sorted or grouped by default, it can be overwhelming to use it to obtain the numbers you need to complete a report or project.
Yet there are times when you’re asked to produce numbers, such as how much money a particular customer spent on a particular product in a given period, that might not be easily obtainable through standard system reports. What can you do?
Use Dimensions to Organize your GL
Within Microsoft Dynamics 365 Business Central, there is a powerful tool hidden behind the scenes – Dimensions. They allow you to categorize your transactions in a way that lets you filter and sort the data to produce useful results. Two of these Dimensions are Global – they live in the same dataset as your GL transactions – and the other 6 are Shortcut Dimensions.
Each Dimension, or category, has “Values” – a list of tags within the category that can be assigned to transactions to group them. There is no limit on the number of Values you can have within a Dimension.
Here’s how they work: Imagine a non-profit organization funded through donations. They might create two Dimensions – Project and Donor. From there, each transaction could be assigned Dimension Values that correspond to the particular project they’re related to, and also show who donated the funds for it.
With good planning, your organization can create Dimensions that give structure to the GL, and allow you to quickly find key datasets such as Customer Revenue by Product, Vendor Expense by Expense Type, Item Purchase Price by Vendor, and much more.
However, there is a caveat: Much of the Dimension information is set manually. That’s easily dealt with if your Dimension Values rarely change (like, for example, pre-defined sales regions, product listings in a well-established market, etc.) However, business today is more dynamic than ever, and creating new Dimension Values on the fly (as you add a new customer, a new product, or other scenarios) would give you a whole other level of flexibility.
Auto Create Dimensions (ACD) Supercharges your General Ledger
At ERP Connect Consulting, we developed the ACD Extension for Business Central to fill a real need: To reduce or even eliminate the manual work involved in creating Dimension Values so you can use them to get actionable data in seconds from your GL.
First, you must configure the data fields you want to track. You may wish to sort by Customer, Vendor, Item, or other categories (see a full listing on our website.) Assign them to a Dimension in the ACD Settings page, then let the extension work its magic.
Here’s an example. Perhaps you want to track transactions by Customer. Once ACD is configured, every time you create a new customer, their number is added as a new Dimension Value. If the customer is updated – or deleted – the appropriate Dimension Value is also updated or deleted. The customer’s Default Dimension Value is also set at creation, allowing Business Central’s standard functionality to fill in the appropriate fields for you, eliminating manual entry errors or forgetfulness.
As a Business Central user, you may remember times you had to sift through the General Ledger data by using the “Find Entries…” function. With ACD, the subledger data is at your fingertips in the GL view. Many common queries can be made by simply filtering transactions by Dimension. You’ll see firsthand the huge time savings as you find the data you need with a few clicks of the mouse.
Some competing ERP solutions have implemented similar functionality, but now it’s available in a fully-integrated and configurable solution for your favorite Microsoft ERP.
Here’s how you can combine Dimensions to make more complex queries:
Let’s say you’ve configured Business Central with Customer and Product Dimensions, and made the appropriate connections in ACD. Your favorite client, ABC Distributors, places several orders in the course of a year. You can filter by customer to see that ABC Distributors has spent a total of $133,852.12 in the last 6 months, and by adding a filter for the Product Dimension, you learn that they spent $79,907.32 on your leading product. You could eliminate the customer filter and you would find that ABC isn’t the biggest buyer of your leading product, but that XYZ Corp. actually purchased more. The best part? All these figures were easily accessible with just a few clicks in your General Ledger view.
The Auto Create Dimensions extension is easy to install and configure. You can download a PTE App file from our website, and soon you will be able to get it directly from Microsoft AppSource. Once installed, you simply generate a demo key from the configuration screen, and you are automatically given 30 days of unrestricted usage to evaluate the extension for your organization’s needs.
Would you like to learn more about this useful tool? Visit our product page, watch the following video demonstration, or read the transcript that follows.
Even better, contact us at ERP Connect Consulting today! We would love to hear from you and answer any questions you have, helping you find every “needle” you’re looking for in your General Ledger.
Demo Video and Transcript
Hello everyone! This is Ben with ERP Connect Consulting, and in today’s video, we’re going to walk you through how to use our Auto Create Dimensions extension. So, after downloading our extension from either AppSource or our website, the first thing you’re going to want to do is come up here and type Auto Create Dimensions in the search bar, and you want to select the first option, which is the Auto Create Dimension setup. What this is going to do is it’s going to bring you into our setup window and allow you to start linking master data with dimensional data. The first main purpose of doing so is you’re now going to be able to take some of this master data, like customers and vendors and things like that, and report on them from the General Ledger, which is something that you can’t do now. Every time you want to go see that data, typically you’re clicking that find entries button, and you’re going to have to drill deeper and deeper.
Now, this is going to allow you to actually see things like revenue per customer, expense per vendor, things like that directly in your General Ledger. So we’ll get there in a second, but first I want to show you how to set up the extension in order to be successful with it. So, the first thing you’ll need to do is generate the demo key up top here. That will fill out all the activation information here below and allow you to use the extension for free for 30 days, after which you can reach out to us and we can either give you a yearly license or a perpetual license that will never expire.
After you deal with those things, the first thing you want to do is come up here and activate the extension. This will now allow all of this functionality to come alive and any time you create any of these records it will also create the Dimension for you. What you’ll see here is I currently have the customer number linked to Global Dimension 1 and I have the employee number linked to Global Dimension 2. Of course, this assumes that some of the General Ledger setup that you have is already in place as well, so keep in mind Customer going into that global 1. If I come to General Ledger setup here, you’ll want to make sure again then that Customer dimension is sitting at the Global Dimension 1 code and the employee is Global Dimension 2.
If we were to link a third, we would just need to make sure that we create a third dimension, link it here, and then all that linkage in our extension will work as well. Let’s go back here and look at some of the other fields that we can link here. Again, we’ve got posting groups, we’ve got Customer states, Item categories, Locations, Vendors, Salesperson, Employee, which I already have mapped so a lot of great functionality here that we can link and then of course the last thing you want to make sure is that up in the dimensions here, again, just out-of-the-box Business Central functionality that this customer has been created, linked to Global 1 and then everything should work accordingly.
If I come up here to my customer and I look at my customer list it’s going to end at 2600. I’m about to go create a customer in Business Central for the first time and we should see that dimension value auto create and it will auto assign a default at the customer level. So, let’s jump in transactionally to what’s next after we’ve got the setup complete. We’ll come back here to the home screen and click on customers. I’m going to create 2700 here. I’ll just call it Ben’s Test Customer and because of the template we had a few of these other things should be filled out like the customer posting group and the payment terms code. So, I’m just going to go ahead here and add an address.
Dallas, Texas, 75206. All right, we’ve got now everything we need to be successful with this customer moving forward and if I click this Customer Default Dimensions you’ll also notice the Dimension code was automatically created. We did not have that before as we just saw. It’s automatically putting my name in there that I just gave to the customer and it’s creating a customer number C02700. It’s creating the same customer number in the Default Dimensions and the Dimension Value in order to keep things very simple, very easy, and very searchable when we go over to our General Ledger entries. So, let’s keep that in mind as we go to the next phase, which is going to be creating a sales invoice. So now that the customer is completely set up, we can come back home and go to our sales invoices. I’m going to create the sales invoice here.
We’ll use that customer that we just created and I’m going to sell them the Auto Create Dimensions extension. We’ll sell it to them on a yearly basis, and you’ll notice here that the customer code was automatically updated. Again, that’s just out-of-the-box Business Central functionality of using Default Dimensions at a master record level, like a customer, and we also see that customer code down here in the invoice details. So, this to me indicates we have everything we need now to go and post this. If I go ahead and post this, I should now see in my General Ledger entries if I come over here and do a filter on my customer; let’s try 2700. I can now see in my General Ledger entries that I have $500 of revenue and $500 of AR with this customer 2700.
If I click on it, I can see who that customer is. So, let’s go create a few more just to show you some other things, and how this can be very beneficial to you. We are just going to create one more invoice. We’ll come down here. We’ll sell that same product just so we can get a thousand dollars with this customer and then we’ll go ahead and make a payment next. Again, 2700 there, 2700 here. You can see how this is making the speed of the invoicing quicker as well because we don’t have to add all of those things in and I’m just going to create a manual payment real quick and a general journal. So, for that customer 2700 we’ll come in here, we’ll just create a quick manual payment into Chase Bank and we’ll go ahead and post this.
And what I want to show now is that if I come into the customer again, we’ll see that I’ve got a balance of $500, I’ve got a balance due of $500, I’ve got sales of $1000 and in this case, I must not have clicked payment. That’s okay. Payment zero, but we know that we have a $500 payment in there somewhere. So now if I come back to my General Ledger entries, what I’m going to see if I filter on the customer again and we can create saved views here if you’re looking for that. Now we can see, okay, I’ve got two product revenue lines, $500 and $500 for a total of $1000. I’ve got three AR lines, a negative and a positive right here. We got our credit and our debit and then we have a $500 balance in AR because we did just create a payment.
And now I can also see with that customer, we have a $500 payment to the Chase checking account. So again, we’re in our General Ledger entries, as you can see up here. And now, I’m able to do something that I have not been able to do before, which is report at a customer level from my General Ledger entries. So now I don’t have to go to “Find entries…” I don’t have to click on the posted customer ledger entries or anything like that in order to see them. I guess that one was the payment. This one might be the invoice. So, I don’t have to click into the invoice to see who that customer was. Again, I can see that directly on my General Ledger entries which is awesome functionality and helps people save a lot of time, both with queries like I’m doing here as well as on reporting, depending on which reporting tool you are using, but that Dimension should be available whether you’re using account schedules or any third party application out there.
So that’s a little bit about our Auto Create Dimensions extension. We showed a lot of functionality that can be very useful and time-saving in Business Central. If there’s anything else that you’d like to see within this extension, we’d love to hear your feedback. If there are any additional questions on Business Central in general, or this extension, please feel free to leave them in the comments below, or reach out to us at any time. We look forward to hearing from you, and look forward to publishing our next video soon. Thanks, everybody!