D365: Management Reporter – How to build the Trial balance report

The trial balance is an internal report run at the end of an accounting period, listing the ending balance in each account. The report is primarily used to ensure that the total of all debits equals the total of all credits, which means that there are no unbalanced journal entries in the accounting system that would make it impossible to generate accurate financial statements.

Sometimes you may have the necessity to build trial balance over the several fiscal years. If you try to do this within D365 you will notice the following error message.

In order to build 2016 and 2017 year-end trial balance, we will use Management Reporter. This functionality allows you to create your own report.

Walkthrough:

Navigate to General ledger > Inquiries and reports > Financial report

Click New.

Click Open ReportDesigner.application.

Row definition

Go to the row definition and create a new one.

Click New button.

Click Edit and select Insert Rows from Dimensions. This will insert all accounts for us.

Make sure that MainAccount is selected.

Click OK.

Column definition

Go to the column definition and create a new one.

Click New button.

Create the following column definition:

ACCT and DESC column types in the Column A and B mean that we will display Account code and Account description in Trial balance report.

CALC column type in the Column F and J mean that we will use formula for sum of debits and credits. Formula will be: Debit column + Credit column. Formula provided in the Formula row.

CALC column type in the Column G and L mean that we will use formula for the Closing balance calculation. Formula will be: Opening balance column + Debit column + Credit column. Formula provided in the Formula row.

DR column restrictions in the Column D and I mean that we will display only debit transactions for the Debit columns.

CR column restrictions in the Column E and J mean that we will display only credit transactions for the Debit columns.

NP Print control in the Column E and I means that this column will not be printed on the screen.

Imagine that our base date will be 12/31/2016

Base – 1 in the Fiscal year mean that we will select data for the previous year. It will be 12/31/2015

@Fiscal year in the Head mean that taken year will depend on the Base date.

In order to display the Fiscal year value over four columns, populate Spread from and Spread to column values.

Report definition

Create a new report definition.

Click New button.

Select Company name @ANY. Trial report might be run for every company.

Select Detail level Financial & Account. We are not going to deep dive into transactional level of our report.

Select Include all reporting currencies flag.

Select Base year and Base period. In our case Trial balance will be built for 2016 (Base -1) and 2017.

Select Row and Column in the Building blocks section.

Switch to the Output and Distribution tab.

Specify Output name. This name will be displayed in the report.

Switch to the Headers and Footers tab.

Populate Headers and Footers. With the configuration we are going to display report name and the company in the header section. Generated date in the footer section.

Switch to the Settings tab.

Uncheck the following checkboxes:

Display blanks for zero amount (with this being unchecked, we will display 0.00 if there zero amount in the column)

Display rows with no amounts (with this being unchecked, we will not display rows if there zero amount in the column)

Change Display negative number (with this being changed, negative numbers will not be displayed in brackets).

Once you are done, click Generate button for the highlighted Report definition:

Wait until Processing completes.

Check the result:

Now if you go back into Dynamics 365 for Finance and Operation and open Financial reports, you will be able to find the created report.

Summary:

We have used Management Reporter for Microsoft Dynamics for Finance and Operations to create, financial report. We have designed Trial balance financial report based on ledger accounts for two fiscal years without drilling down to transaction-level detail.