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.
The Format Mapping is linked with the OOTB excel format that is created for this report under Electronic Reporting.
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.
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.
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.
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.
5 thoughts on “Trial Balance with detailed transactions report in Dynamics 365 Finance”
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.
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?
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.
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.
Hello, Do you know which is the table that ER us for this particular report. I need to add some data from the orginal transaction. Thanks