Reporting with Popdock: on Multiple Dimensions in D365 Business Central
Our customer wants to move their ERP/Accounting software to D365 Business Central.
Problem: The legacy ERP displays G/L Accounts with multiple segments. Business Central only allows one segment. The legacy segments need to be converted to Dimensions. In this way they
squeeze their multi-segment account number into one account segment, yet still retain the categorization that the additional segments had provided in the old system.
For Instance, here is a snapshot of a section of their existing Chart of Accounts. Note the multiple columns comprising the full G/L Account #.
D365 Business Central (or BC for short) uses Dimensions in place of additional segments.
I created 4 Dimensions in BC to map to their 3 segments. Actually, segment 1 (Department & Location) was split into 2 Dimension.
Having already set up the 4 Dimensions: "Locale" for "Location", which is a reserved word in BC; Stream for Industry; Client and Department. Potential Values were assigned to each of these Dimensions, in the setup screens.
An example of setting the Values for the Department Dimension.
In order to assign Dimensions to G/L Accounts, we first need to declare them in the General Ledger Setup page. Note that BC only allows reporting on 2 Dimensions, unless a custom report is created, by a NAV/BC developer. BC calls the first 2 Dimensions "Global". They are repeated again for Shortcut Dimension 1 and Shortcut Dimension 2.
Below you see assigning all 4 Dimensions to a range of G/L Accounts. No default Value is assigned; however all 4 must be given values during any Journal Entry line, else the line won't post. That is assured by the statement "Code Mandatory".
Here is the error message when posting - if any of the 4 Dimensions were not given a value.
Here would be a typical Journal Entry, showing the Values of the Dimensions being set. Notice that BC shows the Value Name along with the Code.
Once we have made many G/L Journal Entries, we would like to get a report. However, we require to show all 4 segments. BC's standard General Ledger Entry page only shows the 2 Global Dimension. it also shows only the Codes, not the named Values. There is a column in this report which a NAV or BC Developer can use to extract all 4 Dimensions. Not an easy report to write.
With Popdock.com a report showing all 4 Dimensions can easily be created. With an additional tweak, the Dimension Value Codes can be displayed as Dimension Value Names.
The Popdock report of G/L Entries can be filtered and exported to Excel, where the end-user/accountant can analyze the data by Dimension. (in this case I inserted an additional column (Net), which was Credits minus Debits.
Here is the exported range of data, converted to an Excel Table, for easy analysis
In Excel - create Pivot Tables or Charts any way you want (oops, I forgot to change "Row Labels" to "G/L Accounts").
There you have it: Analytical Accounting with D365 Business Central, Popdock, and Excel.