Electronic Reporting (ER) Cookbook

Electronic Reporting (ER) Cookbook

The Electronic Reporting module in Dynamics 365 for Operations, previously known as General Electronic Reporting (GER) was one of the coolest application inventions in Dynamics AX7. Developed apparently in my alma mater – the Dynamics development office in Moscow – this “ER” is a surprisingly powerful tool with an own in-built programming language for transformations. A consultant may configure any vendor payment format or any moderately complex GL export (such as the German GDPdU or French FEC) within days with literally no customizations, and test it ad hoc.

NACHA file format
Over the last year, I implemented the British BACS18, the Swiss DTA, the Spanish SEPA sub-format and the North American ACH outbound payment format. While the AX wiki was a good start, the best way to learn the tool is practice. Below are some transformation code snippets that may help you along the steep learning curve.

Line counters

In the payment files, there are often record numbers. The ER element “Counter” is intended to be used, but it is incremented on every occurrence in the format. I.e. the counter has a global context, and the same count may not be used more than once in a row.
A total of records in a footer section is a special case: you may enumerate the lines with a Counter, then use a function of the Group by type over the data source to get a total record count, see the screenshot.

With 2 concurrent counters you have no other choice but to use the [real numeric] journal line number, and convert it into an integer:

NUMBERFORMAT(VALUE(model.Payments.PaymentIdentifications.SourceBusinessEntityDescription), "00000000000")
Beware of the major handicap of this trick: deleting payment journal lines creates holes in the numeration.

Simple transformation formula examples

Convert an amount into an amount with fixed 2 decimal places after the comma
NUMBERFORMAT(model.Payments.InstructedAmount, "#.00")
Remove the decimal separator, produce an amount in pence / cents, implemented as a subroutine (“transformation”) with a numeric parameter
REPLACE(NUMBERFORMAT(parameter,"0.00"), ".", "", false)
Remove line breaks from the postal address, and truncate to 70 characters
LEFT(REPLACE(model.Payments.Debtor.PostalAddress.AddressLine,"\n"," ",true),70)
Remove all non-numeric characters with a regular expression
REPLACE(model.Payments.DebtorAccount.Identification.Number, "[^0-9]", "", true)

Calculate a weighted checksum

The below expression adds the 9th checksum digit to a US ABA routing number, should the user only have entered the 8 significant digits. The RIGHT(NUMBERFORMAT(10 – VALUE(), “#”), 1) is a crude implementation of modulo 10.
CASE(LEN(parameter),
9, parameter,
8, parameter&RIGHT(NUMBERFORMAT(10-VALUE(RIGHT(NUMBERFORMAT(
(VALUE(MID(parameter,1,1))+VALUE(MID(parameter,4,1)))+VALUE(MID(parameter,7,1)))*3+
(VALUE(MID(parameter,2,1))+VALUE(MID(parameter,5,1)))+VALUE(MID(parameter,8,1)))*7+
VALUE(MID(parameter,3,1))+VALUE(MID(parameter,6,1))), "#"), 1)),
"#"), 1),
RIGHT(parameter, 9))

Day number in a year (1-366)

The British BACS18 format contained a date format that really made me suffer: for the 1st of February 2016 it expected “ 16032” where 32 was the day count from the 1st of January. The below code took me a while; it used the Gregorian calendar formula and predated the fact that if you start counting months from March, the length of the months oscillates 31-30-31…
The repetitive IF(…) is an implementation of (month + 9) mod 12.
(VALUE(DATEFORMAT(parameter, "yyyy"))-
ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2,
VALUE(DATEFORMAT(parameter, "MM")) -3,
VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0)
)*365
+ROUNDDOWN(
(VALUE(DATEFORMAT(parameter, "yyyy"))-
ROUNDDOWN(IF(VALUE(DATEFORMAT(parameter, "MM")) >2,
VALUE(DATEFORMAT(parameter, "MM")) -3,
VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0)
)/4, 0)
+ROUNDDOWN(
(IF(VALUE(DATEFORMAT(parameter, "MM")) >2,
VALUE(DATEFORMAT(parameter, "MM")) -3,
VALUE(DATEFORMAT(parameter, "MM")) +9) * 306 + 5)/10, 0)
+VALUE(DATEFORMAT(parameter, "dd")) - 1
-( (VALUE(DATEFORMAT(parameter, "yyyy")) - 1) * 365 +
ROUNDDOWN( (VALUE(DATEFORMAT(parameter, "yyyy")) -1)/4, 0) +
305)

This function should work correctly for the next 83 years. We IT people think in shorter terms than the Roman Catholic Church.

Update 02.05.2017: the November 2016 update to Dynamics 365 for Operations features an undocumented function DAYOFYEAR, and the above code reduces to just
DAYOFYEAR(parameter)

Data model

To master the file format, you must first learn the data model, the ER’s configurable abstraction layer over the Dynamics database. Outbound vendor payments use the Payment model.

It provides generic payment attributes,

AttributeModel node
Payment date (user parm or journal date)model.Payments.RequestedExecutionDate
Amountmodel.Payments.InstructedAmount
ISO payment currency (journal line currency)model.Payments.Currency
Journal line number (see Line counters)model.Payments.PaymentIdentifications.SourceBusinessEntityDescription
Payment / Notemodel.Payments.PaymentsNotes
Voucher+RecId (a unique line ID)model.Payments.PaymentIdentifications.EndToEndIdentification
Date of todaymodel.CreationDateTime

our bank details,

AttributeModel node
Our name (company name)model.Payments.Debtor.Name
Our full address (company address)model.Payments.Debtor.PostalAddress.AddressLine
Name of our bankmodel.Payments.DebtorAgent.Name
Our bank account numbermodel.Payments.DebtorAccount.Identification.Number
Our IBANmodel.Payments.DebtorAccount.Identification.IBAN
Routing number of our bankmodel.Payments.DebtorAgent.RoutingNumber
BIC of our bankmodel.Payments.DebtorAgent.BICFI

and their (beneficiary) bank details:

AttributeModel node
Their name (vendor’s name)model.Payments.Creditor.Name
Their full address (vendor’s payment address)model.Payments.Creditor.PostalAddress.AddressLine
Name of their bankmodel.Payments.CreditorAgent.Name
Their bank account numbermodel.Payments.CreditorAccount.Identification.Number
Their IBANmodel.Payments.CreditorAccount.Identification.IBAN
Routing number of their bankmodel.Payments.CreditorAgent.RoutingNumber
BIC of their bankmodel.Payments.CreditorAgent.BICFI