Let’s get right to it! We have all this financial data and going through your ERP system. Sure, there are requirements to have it, but to improve business, we need to interpret and analyze this data in order to make decisions to achieve our goals as well as set our future targets.
For those of you who have been following along with our Dimensions series, it finally comes full circle to see a bunch of the benefits come to fruition. For other who are just jumping in to see some of the reporting uses using Dimensions, check out the other 2 articles to make sure you have things set up the right way to use this functionality efficiently.
The major benefit of attaching Dimensions to your financial transactions, is that this information follows that data and, in the end, allows you to report by these categorizations. Let’s look at 3 key reporting capabilities using Dimensions: Chart of Accounts filters, Account Schedules and Analysis Views (Analysis by Dimensions). Note: This is reporting within Business Central and we will release Power Bi reporting help separately to this.
We have released a blog previously around using the Chart of Accounts for reporting, since Business Central has great filtering capabilities. Particularly since Saved Views have been introduced. So how would this relate to Dimensions, rather than focusing on Date Filters. Why not use both?
Before we use an example, make sure you add the ‘Net Change’ and ‘Balance at Date’ columns to your Personalization. They are often not there by default.
Throughout this, I will use Department as my Dimension example. Basically everyone uses it! On the Chart of Accounts, you can filter by a single Dimension or multiple Dimensions (by using ‘|’ symbol). With this we could combine that with a date filter and a filter to narrow down the accounts we see. Just with those 3 filters, we can generate an income statement or report with our expense accounts for each Department or group of Departments. Sure, this info is helpful for analysis of what happened in the period. The bigger benefits though, come from being able to use this information to make business decisions/changes to achieve goals, as well as budget/forecast by Department moving forward. Being able to do this at a Departmental level compared to just at a company level gives you the granularity needed to be more detailed and efficient. Here’s this example below:
Using this I get an Income Statement by G/L for the from the start of March until today:
You can take that a step further and add in a specific Dimension too:
The next Business Central reporting tool is Account Schedules. When you run the default Balance Sheet or Income Statement, these are the reports that are in behind that functionality. Yet there is a bunch more that can be done with them. It also makes it easy to update the layout of those default reports. Now you can see the Income Statement account Net Changes for the particular period, for this particular department, just using the Chart of Account. You can always use the ‘Open in Excel’ function to take this data externally and do what you need with it!
The cool thing about Account Schedules, is you can take them a step further and add multiple columns. You can do this by editing the Column Layout. In this example below, I am using a Net Change formula and have set column 1 to pull G/L Net Changes and column 2 to pull Budgeted Net Changes.
I can then take this a step further and filter by Date and Departments whilst having a side by side comparable column layout.
As always, I can then take this data to Excel right from this page and use it in other ways too. This is a very effective way to do budget vs actuals comparable reporting. As long as your Budget Entries have the Dimension and Date data entered appropriately, these are very simple to generate.
The other out-of-box reporting tool I would suggest (and my personal favourite), is Analysis Views. You can create the same kind of report as mentioned above. Except instead of seeing one set of numbers, you can see it in a Matrix View. With this, we can have G/L Accounts as the rows and each Department as a column, as well as a column for the total. In essence, we can see the Income Statement or Expense analysis by Departments, whilst being able to see how much each Department contributed to the company total for the particular account. This will all make sense when you look at the matrix below! It is worth mentioning, this use of the Dimension restrictions mentioned in the previous part of this series plays a key role here. If you have financial transactions that are not forced to have a Department (my example!) attached to them, then you could have values for all Departments and when you add them up, it doesn’t equal the total. This means you have extra costs or income that you can’t associate with a Department. That will take away from the value of this information and impair the use of this report for decision making. I would suggest checking out that post here.
Analysis Views are also a great tool for viewing year by year data. In your matrix setup, you can set the rows to G/L Accounts and the columns to Period. Then you can view by year and set your date filter to include 2-3 years. This will then give you year by year data for the G/L Accounts you include.
Both Account Schedules and Analysis Views are relatively user friendly and intuitive reporting tools. They have some cool features like easy toggles for showing the opposite sign on Income Statement Accounts, so income shows as a positive even though it is a credit entry and easy settings screens to enter dates and set you matrix details.
This granular reporting that you can get using out-of-box functionality is definitely the biggest benefit of Dimensions. There is perquisite setup and requirements needed to get the most benefit out of this, but to have reporting of this detail without using a supplemental reporting tool makes getting information to help with your decision making easier. I still vouch strongly for Power BI, however, I like to use this for many other uses rather than running these sort of reports! Follow along for an upcoming post on how to get started with Power Bi if it is not something you are using yet. It can add a huge amount of value to the reporting we have covered here!
Learn how financial data is segmented and tracked in Business Central and how to access powerful reporting features with Power BI. Watch our 20 minute video demo now >>