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.
9, parameter,
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"))-
VALUE(DATEFORMAT(parameter, "MM")) -3,
VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0)
(VALUE(DATEFORMAT(parameter, "yyyy"))-
VALUE(DATEFORMAT(parameter, "MM")) -3,
VALUE(DATEFORMAT(parameter, "MM")) +9)/10, 0)
)/4, 0)
(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) +

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

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

Minimalistic EU VAT Configuration in Dynamics AX

Minimalistic EU VAT Configuration in Dynamics AX


Let me present my 4th iteration of the EU VAT setup in Dynamics. The below concise VAT configuration in Dynamics 365 for Finance has been tested over 3 years of my operations. It has been constantly updated with the changes in taxation. It covers intra-community import, export and import of services within the European Union and beyond, domestic supplies, travel within the EU and abroad.

Before we begin with the setup let me explain some background facts and assumptions:

    • Any export of goods or services in or outside of the European Community is tax-free, but reported
    • An export of services is special: in accordance with Directive 2008/8/EC any service rendered for customers abroad is subject to reverse charge
    • In the case of an intra-community (IC) delivery, goods and services are presented separately on the EU Sales List
    • On the contrary, goods and services delivered in the home country are reported together and should not be distinguished
    • Intra-community deliveries are reported separately from the foreign trade with the so-called 3rd countries (i.e. countries which are not the 28 members of the EU, e.g. Norway or Switzerland). IC trade appears of the EU Sales list, while 3rd country trade does not, but it is reported to the INTRASTAT instead.
    • Most of the countries apply a full rate and several reduced rates. The reduced rates are there mostly for the basic consumer services and goods (Austria: 10%). They normally do not affect enterprises, until the employees start reporting travel expenses.
    • A semi-reduced “hotel” rate of 13% stands out in Austria, but also in Switzerland and France for accommodation. Update 2018: 13% were reverted back to 10% in Austria, but I am going to keep it in my scheme.
    • A grocery may have separate tax codes for an IC acquisition of Polish potatoes (self-assessed 10%) or French wines (self-assessed 13%). However, the reduced-% goods and services are rarely imported by manufacturing or professional service businesses, with the exception of foreign books and printed media (self-assessed 10%).
    • In Switzerland, the VAT receivable from investments is reported separately from the VAT received from current assets or services (de-ch: “Vorsteuer auf Investitionen und übrigem Betriebsaufwand” vs “Vorsteuer auf Material- und Dienstleistungsaufwand“) despite the same rate.
      Since 2019, a similar aspect has become valid in Austria too. The mandatory Chamber of Commerce contribution (de-at: “Kammerumlage 1” or KU1) is evaluated at 0,29% of the total VAT receivable excluding any investments i.e. assets acquired either domestically or abroad.
      The reduced VAT (foodstuffs, pharmaceuticals) may be neglected: milk is unlikely to become a long-term asset, unless condensed 😉
    • From the system point of view, there is a difference between a “zero rate” and “no rate”. A tax code with a 0,00 rate still logs the tax base for reporting, as long as a record in the tax Values table exists.
    • From the fiscal standpoint, there is a difference between a “zero” an “no” rate too. For example, in the UK public transportation is taxable at 0% which is considered a tax rate of its own. In France, 0% is applied to newspapers. To enable a zero rate, just open the Values table in connection with the tax code, let the system create a record, leave the Value = 0,00000 and save the record.
    • It is a good practice to have a valid tax code, i.e. a valid Customer/Item group combination in every business case whenever it is reported on the tax declaration or not. This is enforced by keeping the tax parameter Check VAT groups active. For example, on a business trip from the UK to Hungary the VAT may theoretically be recovered, but hardly any company does it. The tax codes TF and NR below help stay compliant to the Check VAT groups setting.

VAT codes (en-us: Sales tax codes)

Tax codeNameRate ATExample
vatFDomestic VAT, full20%Sales or purchase of regular goods and services, e.g. raw materials, office consumables, car expenses (electric cars or ‘fiscal cars’ only)
vatADomestic VAT from assets, full20%Investments in long-term assets, i.e. machines, office equipment, electric car fleet
vatHReduced “hotel” rate13%This reduced rate applies to accommodation, but not restaurant bills
vatRDomestic VAT, reduced10%Public transportation, taxi, and basic foodstuffs, including restaurant bills with non-alcoholic beverages, books and newspapers
TFTax free
Out of scope
0%Exempt international air and sea transportation, or services delivered by “non-genuine” tax exempt suppliers such as insurances, postal services (de-at: “unechte Steuerbefreiung“), but also the City tax (de-at: “Tourismusabgabe“, “Ortstaxe“) component on hotel bills. The base on purchases may still be reported to the authorities.
NRNot recoverableOn a travel abroad, services consumed by the employee are taxable per se, and within the European Community the tax may even be recovered, but very few companies do so. The code is not reported to the authorities. Running expenses from traditional personal cars with internal combustion engines fall into this category too.
euFIC export
IC acquisition
Delivery of goods to another country of the European Community is tax free, but the value of the goods is reported to the tax authority.
On a regular import of goods from another member of the European Community the full domestic tax is levied; the tax is self-assessed with a zero effective tax load (what you pay is what you get recovered). This is achieved with the Use tax (en-us) i.e. Import VAT / purchase VAT (en-gb) setting in the VAT group.
euAIC asset export
IC asset acq.
It is highly likely, that the investments into long-term assets (machinery, other equipment) go into another EU country. Less probably but also possible, is that used assets are sold into an EU country.
euRReduced IC export
IC acquisition
Delivery of goods to another country of the European Community is tax free, but the value of the goods is reported to the tax authority.
On goods that would be subject to the reduced tax rate domestically (e.g. books), the same reduced tax rate is applied when procuring such goods from another member of the EU; the tax is self-assessed with a zero effective tax load and the Use tax setting in the VAT group.
euSIC services export
IC services import
Delivery of services in another country of the European Community is tax free but reported.
The buyer, however, must obey the reverse charge principle. The setup for the reverse charge and the zero tax impact is similar to the above IC goods acquisition.
3rdFExport 3rd county
Import 3rd country
The export of goods is tax free in most of the countries, but the value has to be reported.
Tangible goods imported into the EU from a 3rd country is subject to an import tax, whose base is hard to calculate because it includes the portion of insurance and freight up until the border. Normally the import tax is calculated and paid by the customs broker; in the exotic import tax self-assessment mode (de-at: “Einfuhrumsatzsteuer geschuldet”) this 3rdF tax code may be used to post the tax.
3rdSServices export 3rd
Services import 3rd
If the place of supply of services is outside of the EC, the export is tax-free but still have to be reported (as taxable elsewhere under the reverse charge regime).
The procurement of services outside of the EC is subject to a self-assessed reverse charge.

VAT groups (en-us: Sales tax groups)

In every business case in Dynamics 365 for Finance, the tax code is deducted by the system from an intersection between the customer/supplier VAT group and the product Item VAT group. Aside of the basic “F”, “S”, “TF” item VAT groups I recommend “H”, “Food” and “PubT” for the travel expense reporting. The reason to separate the latter 2 groups is my reverence to companies recovering foreign taxes: tax rates for these categories vary across Europe.

The group AP-NR is used in the Travel and Expense module to have a formal tax code TF even if the VAT is not recoverable, and satisfy the Check VAT groups validation in the tax module.

Tax codes to be marked Exempt in the VAT group configuration are stroke through in the table below, and those marked as a Use tax are in italic:

Customer or supplier VAT groupItem VAT group “F” (full)Item VAT group “A” (assets)Item VAT group “S“ (services)Item VAT group “TF“ (tax free)Item VAT group “H” (hotel)Item VAT group “Food“Item VAT group “PubT“ (public transp.)
AP-EUeuFeuAeuSTF euR 
AP-3RD3rdF 3rdSTF   
AR-EUeuFeuAeuSTF euR 
AR-3RD3rdF 3rdSTF   


The above configuration is going to work well in the D-A-CH countries, in Scandinavia, and even in Spain. Yet it doesn’t account for the “VAT on payment” aka Conditional tax common in France (de: “IST-Besteuerung“), and it will probably not work for countries with inflated reporting requirements (Italy).

This blog is followed by Part 2 – selection of the VAT reporting codes for the Austrian VAT declaration.