# 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.

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 “ 16**032**” 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,

Attribute |
Model node |

Payment date (user parm or journal date) | model.Payments.RequestedExecutionDate |

Amount | model.Payments.InstructedAmount |

ISO payment currency (journal line currency) | model.Payments.Currency |

Journal line number (see Line counters) |
model.Payments.PaymentIdentifications.SourceBusinessEntityDescription |

Payment / Note | model.Payments.PaymentsNotes |

Voucher+RecId (a unique line ID) | model.Payments.PaymentIdentifications.EndToEndIdentification |

Date of today | model.CreationDateTime |

our bank details,

Attribute |
Model node |

Our name (company name) | model.Payments.Debtor.Name |

Our full address (company address) | model.Payments.Debtor.PostalAddress.AddressLine |

Name of our bank | model.Payments.DebtorAgent.Name |

Our bank account number | model.Payments.DebtorAccount.Identification.Number |

Our IBAN | model.Payments.DebtorAccount.Identification.IBAN |

Routing number of our bank | model.Payments.DebtorAgent.RoutingNumber |

BIC of our bank | model.Payments.DebtorAgent.BICFI |

and their (beneficiary) bank details:

Attribute |
Model 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 bank | model.Payments.CreditorAgent.Name |

Their bank account number | model.Payments.CreditorAccount.Identification.Number |

Their IBAN | model.Payments.CreditorAccount.Identification.IBAN |

Routing number of their bank | model.Payments.CreditorAgent.RoutingNumber |

BIC of their bank | model.Payments.CreditorAgent.BICFI |

Do you mean ALLITEMSQUERY?

Have you also an example, how and where to use the ALLITEMSSQL-Filter ?