Trial Balance with detailed transactions report in Dynamics 365 Finance

One of the common requirements that we face from Accounting people is “How do I get a  detailed Trial balance for a month for auditing purposes?” The answer earlier was to use the OOTB Trial balance that can provide drill down capabilities. While that was workable, users would had to drill down on each account/dimension combination individually, to review the transactions for a particular month. Workable, but a little tedious.

With PU37 (currently under preview), a new report, Trial balance with transactional detail, coupled with formatting capabilities of Electronic reporting is being introduced. I have enabled this feature after PU37 is installed, using the feature management.

The options that you see on the dialog that has the parameters, looks similar to the Trial Balance inquiry that is present. The only 2 additional columns would be the Show transactions option and the Format mapping. 

DTB1

 

The Format Mapping  is linked with the OOTB excel format that is created for this report under Electronic Reporting.

DTB2

The excel format that is provided looks as below. The fact that Microsoft has provided it via Electronic reporting, it would mean that they would allow the customers and partners to be able to modify the layout according to the user preferences.

DTB3

 

Now that we have reviewed some of the key components of the report, let us review how the report works and how it looks like after running for a month. Note that, the date range that you can provide is only 31 days, unlike the normal Trial balance which could be executed/run for an entire fiscal/financial year. This makes sense as there could be huge no. of transactions and the report generation might slow down.

Below is  a payment journal that is open for the date 28th July 2020.

DTB4

When the report is executed for the month of July (1st of July to 31st of July), you can see the transactions being generated appropriately based on the parameter that is chosen to see All transactions that includes both Pending and Posted transactions. Note that, since this is using Electronic reporting configuration with an excel format, the report that is generated would be downloaded as an excel, instead of generating the report as it usually opens for an SSRS  based report. Pending transactions are denoted with ** as highlighted below and the document in this case shows the Journal batch number and since this is a payment journal, it shows the Payment reference as well.

DTB5

This kind of reporting would be pretty useful during the month end closing process to identify if there are any pending transactions that are to be posted and gives enough details what journals are pending for each main account.

 

 

 

 

 

4 thoughts on “Trial Balance with detailed transactions report in Dynamics 365 Finance

  1. Pankaj Singhal says:

    Hi Vamsi,
    I was trying to enable this option. I could import the electronic report but I am not able to run the report. Any additional steps that you can share would be very helpful.

    Like

    1. Vamsi Pranith says:

      Hi Pankaj,

      Are you able to see that there is a format once you have imported the electronic reporting configuration? Are you facing an error when you are trying to run the report?

      Like

      1. Pankaj Singhal says:

        Hi Vamsi,
        I can see the following two lines in the Reporting Configurations:

        * Trial balance detail data mapping
        * Trial balance with transactional detail (excel)
        When I try to run the 2nd option, I get the error message Table: 0 does not exist.

        Just wanted to add that I am new to this and just trying my way around electronic reporting (unsuccessfully 😊). If you can point me to basics of electronic reporting would be helpful.

        Best Regards,

        Pankaj Singhal

        Like

      2. Vamsi Pranith says:

        No worries, can you please paste the whole configuration for the electronic reporting configuration for this? There are 3 components of Electronic reporting 1- Data model, 2 – Model mapping (Trail balance). The first component data model is important as it sets the baseline of columns etc., in the ER configuration. Please check if there is a top above these 2.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s