Electronic reporting by example: Create a new Bank file definition

Electronic Reporting seems to be an integral part of the future of Dynamics 365 that can be used for importing and exporting data of all kinds above and beyond the capabilities of the static data entities, so it is worth finding out how this works.
In the following we will do this by example.

Online ressources

As a starting point there is standard microsoft documentation and this great blog with detaillied instructions:

• https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/general-electronic-reporting
• https://dynamicsmanuals.com/2019/09/12/introduction-to-electronic-reporting/

High level overview for creation of new Bank file

1. Extend a preexisting bank format
2. Define new export format – Constants
3. Define new export format – Data fields
4. Add transformations for correct formatting
5. Add new calculated fields
6. Calculate total fields
7. Add user input parameters
8. Add validations for readable errors
9. Activate

BACS: 1. Extend a preexisting bank format

It is always easier to start based on preexisting work instead of starting from scratch, so to create a new bank format, try to find a similar one to extend.

There is already a BACS format provided by Microsoft, however this does not conform to the current specification 18, but this will be out starting point.

BACS: 2. Define new export format – Constants

Define new file:

• Define End of Line character
• Define Constants

BACS: 3. Define new export format – Data fields

Define new file:

• Define data fields –> map to data source

• 

BACS: 4. Add transformations

If the format to be printed does not match the data source transformations are required. Examples:

• Amounts as cents i.e. decimal number format without decimal separator: 123.45 transforms to 12345
• Date as Year combined with day in year: YYddd

BACS: 5. Add new calculated fields

Define new calculated fields:

• isCredit

…click Edit formula…

Note: if fields require new data sources, then the data mapping must be extended.

BACS: 6. Calculate total fields

Define new totals:

• Count of credit line / count of debit lines
• Sum of credit amount / sum of debit amount

…click Edit group by…

BACS: 7. User input parameters

Add User input parameters to allow the user to choose options in the file generation dialog.

The parameters can be used in formulas:

BACS: 8. Add validations

Validation help to give proper error messages that tell the user what is wrong.

Note: the condition must be true so the error does not occur.

Other option: make fields mandatory, but the error message is not understandable.

BACS: 9. Final Step – Activate (Complete)

Don’t forget to “Complete” your Draft – only then the changes are available.

Files

• Specification BACS 18
• Demo File
• ER Definition XML