How to create a Dynamic Account Structure and use it within iplicit enquiries

This guide takes you through the steps to create a Dynamic Account Structure and set it as the default account structure tree within enquiries. If you already have a Dynamic Account Structure you wish to use within an enquiry, skip to step 10 of this guide; otherwise, start at step 1.

When to use this guide:

• When you want to create a custom account structure to report on groups that do not exist within your standard chart of accounts, for example, your standard chart of accounts has a sales and cost of sales account group, and you need to calculate gross profit within an enquiry using these two groups.

Steps:

Step 1: Go to Dynamic Account Structures and click “New”.

Step 2: Enter a description and code for your new structure, then click "Create". The code for the Dynamics Account Structure must be unique among all existing Dynamic Account Structures.

Step 3: Return to Dynamic Account Structures and select your new structure from the list of available Dynamic Account Structures.

Step 4: Click “Edit”, and then click “Add” on the Structure tab.

Step 5: Enter the account type, code, and description for your new group. In this example, we are creating a Gross Profit group, so the account type is Profit & Loss, as the Sales and Cost of Sales groups contain Profit & Loss accounts. This group has no parent, but if it did, you would select the parent group here as well.

Step 6: Repeat steps 4 and 5 to add the Sales and Cost of Sales groups to your Dynamic Account Structure. Both groups should have Gross Profit as the parent. Once finished, the structure should look like this:

The order in which the groups are listed above is crucial when the Dynamic Account Structure is used to perform calculations in an enquiry. In the example above, Sales will be deducted from Cost of Sales, so Sales should be the first group in the list and Cost of Sales the second. If the order in which you created the groups is incorrect, you can select a group and reorder it using the up and down arrows next to the “Add” button.

Step 7: Now select the “Accounts” tab, and you will see your newly created structure on the left-hand side and the account list on the right-hand side. Hover your mouse over a group and select the “Menu” button, which opens a list of accounts you can assign to the group.

Step 8: From the account list, you can select the accounts that belong to the chosen group and add them using the arrow boxes in the middle pane. Add your accounts and click “Apply”.

You should now see the accounts listed under the group.

Step 9: Repeat steps 6 and 7 to assign accounts to your other groups, and once finished, click “Save’. 

Note that you cannot add the same account to multiple groups. Each account can only be assigned to a single Dynamic Account Structure group.

Step 10: You can now use the Dynamic Account Structure in enquiries where the pivot table is grouped by account, such as the Profit and Loss (P/L), Trial Balance, or Balance Sheet enquiries. Open an enquiry where you want to implement the newly created Dynamic Account Structure and select the cog in the top right of the pivot table. 

Step 11: In the “Rows” section of the Pivot fields, choose the Tree dropdown and select your newly created Dynamic Account Structure. 

Step 12: You should now see your new structure in the main pivot of the enquiry along with the calculated value of the combination of your Dynamic Account Structure groups.

The “Other” line shown above (above Total) represents values for accounts not included in your Dynamic Account Structure. You can hide this line by clicking the gear icon in the top right corner of the pivot table, then select the three dots next to the Tree dropdown, and choose “Show non tree items” to disable it.

Once completed, you will notice that the “Other” line does not appear on the enquiry and only the accounts linked to your Dynamic Account Structure are visible.