Electronic Reporting (ER) Cookbook 3: Working with dates

Following the parts 1 and 2 of my hints and tips collection for Electronic reporting, let me shed some light on working with dates and date formatting.

Cut-off date

If a classic Excel transaction report must be implemented in the Electronic Reporting module, then a cut-off date is a common requirement: show all transactions up to and including a certain date, or show all transactions if the user hasn’t selected any date. Classically, a parameter in the dialog is presented to the user, this requires a User parameter of the DATE type in the format or – better – in the date model mapping (see also Electronic reporting in depth).

This parameter must be used in a Calculated field of the Record List type. Below is a snippet from one of my very first reports dated back to 2016; it extracts ledger transactions from the current company for the German GDPdU dump file:
WHERE(GLaccountEntry, AND( NOT(GLaccountEntry.AccountingCurrencyAmount=0), GLaccountEntry.'>Relations'.GeneralJournalEntry.Ledger = Ledger.'current()', GLaccountEntry.'>Relations'.GeneralJournalEntry.'>Relations'.FiscalCalendarPeriod.Type = FiscalPeriodType.Operating, GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate >= FromDate, OR(GLaccountEntry.'>Relations'.GeneralJournalEntry.AccountingDate <= ToDate, ToDate=NULLDATE())))
In hindsight, this was a terrible implementation. The WHERE operator works with in-memory lists. In essence, it loads all transactions from all companies and all periods and years into an internal XML container, and THEN starts filtering it by company and date. Not surprisingly, the performance degrades rapidly and the report execution time grows exponentially.

The FILTER is a better function as it compiles to a direct SQL statement and returns a reduced dataset; however, it has limited capabilities and does not support cross joins as above.

A mature solution would be to perform the filtering in 2 steps: one Calculated list fetches a maximally pre-filtered list from the SQL database, and subsequent Calculated field variables apply additional dynamic filters to this subset.
The below expression returns a list of all cost project transactions up to a certain date:
FILTER(ProjTransPosting, AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost, OR(ProjTransPosting.LedgerTransDate <= $ToDate, $ToDate=NULLDATE())))
where $ToDate is the user parameter.

To simplify and reuse the classic pattern WHERE LedgerTransDate <= $ToDate OR $ToDate=NULLDATE(), an interim variable (Calculated field) $ToDateOrInfinity may be declared (I could not find any other way to pass a date literal into an ER expression, that’s why the crude DATEVALUE(“31-12-2154″,”dd-MM-yyyy”)):
IF('$ToDate'>NULLDATE(), '$ToDate', DATEVALUE("31-12-2154","dd-MM-yyyy"))
and the above query reduces to just
FILTER(ProjTransPosting, AND(ProjTransPosting.PostingType=Enums.LedgerPostingType.ProjCost, ProjTransPosting.LedgerTransDate <= $ToDateOrInfinity))

This led to an issue on the UI, however. User parameters from the [default] Mapping designer propagate to the format, are merged with the user parameters defined in the Format designer and displayed together in the common dialog window. According to Maxim B. from the Microsoft R&D Center in Moscow, a user parameter is shown at the run time if 2 conditions have been met:

the visibility is not turned off;
in the mapping, the user parameter is bound to the model either directly or indirectly.

Seemingly, the above indirect usage obscures the parameter from the ER format and it disappears from the dialog. The solution was to bind $ToDate directly to an unused field in the model.

Date formatting: DATETIME to DATE

I spent quite some time trying to find an embedded function to convert a DATETIME type and bind to a DATE cell in Excel. Obviously, you can declare the format node a STRING and apply the DATETIMEFORMAT() function, but this circumvents the locale settings.

The solution was dumb, but it worked:
DATEVALUE(DATETIMEFORMAT(@.'Created date', "dd-MM-yyyy"), "dd-MM-yyyy")

First, Last date of the month

The below formulas work in model mappings. Assume the parmPeriodDate a user parameter. The first formula is easy and trivial:
DATEVALUE("01-"&DATEFORMAT(parmPeriodDate, "MM-yyyy"), "dd-MM-yyyy")

The second formula is crazy. It needs a declaration of the system class DateTimeUtil nearby in the model mapping data source section.
DATEVALUE(DATETIMEFORMAT(ADDDAYS(DATETODATETIME(DATEVALUE("01-"& DATETIMEFORMAT(DateTimeUtil.addMonths(DATETODATETIME(parmPeriodDate),1),"MM-yyyy"), "dd-MM-yyyy")),-1),"dd-MM-yyyy"),"dd-MM-yyyy")
It takes the parameter (e.g. 15.02.2023), adds a month (15.03.2023), makes the 1st of that month (01.03.2023), and subtracts 1 day (28.02.2023).

Date formatting: Show an empty date in Excel as blank

An empty date in the format data source is printed in Excel as 02.01.1900. This is not good and very distracting. Again, an obvious solution is DATEFORMAT() in a string type cell, but it just doesn’t feel right.
An elegant approach is a generic Transformation in the Format designer: Formula transformation NoNullDate =
IF(parameter<=NULLDATE()+1, "", DATEFORMAT(parameter, "dd-MM-yyyy"))
This can be re-used and quickly applied to every node:

Electronic reporting blog series

Italian eInvoicing FatturaPA: Part 2

You thought this was over? Not at all, the implementation of the FatturaPA format in D365FO harbors many pearls : )

DatiBollo, ImportoBollo

The “Bollo” or the Import duty seems to be a self-assessed Italian charge on certain deliveries. The user should be able to apply stamp duties to a subset of outbound invoices at will. The total stamp duty load is deducted by the Italian authorities periodically by harvesting the electronic invoices. In case the stamp duty is applicable, the tag should say “si” and should give the [positive] amount: 2.00.

The standard D365FO electronic format assumes the stamp duty is represented by a miscellaneous charge to the sales order. These charges trigger the XML element; otherwise the miscellaneous charge is exported as a regular invoice line.
The respective charge code can be configured with a transit debit and a transit credit account and added to any sales line manually or automatically as a auto-charge:

The system relies on the Spanish parameter Stamp duty to identify the dedicated miscellaneous charges code representing this duty. Unfortunately, this Spanish parameter is unavailable in Italy by definition, check the button Electronic document properties in the legal entities form:

Moreover, in the mapping between the database and the format the charges code was missing, and the duty only took sales order header level charges into accounts. A custom mapping needs to be derived from the Customer invoice model – Model mapping Customer invoice to fix this. This mapping must be declared Default for model mapping = Yes.
In the format the parametrized query $StampDutyCharges can be extended by a hardcoded check if the charge code begins with “Bollo”, then both the header-level and line-level charges can be merged into a single list:
IF(ISEMPTY('Stamp duty'), LISTJOIN(WHERE(@.InvoiceBase.MarkupTransaction, LEFT(@.InvoiceBase.MarkupTransaction.Code,5)="Bollo"), WHERE(@.LineItem.MarkupTransaction, LEFT(@.LineItem.MarkupTransaction.Code,5)="Bollo")), WHERE(@.InvoiceBase.MarkupTransaction, @.InvoiceBase.MarkupTransaction.Code='Stamp duty'.Value))

RiferimentoNormativo

The RiferimentoNormativo delivers the legal justification for tax exempt invoices and refers to the Italian tax codex, e.g. <RiferimentoNormativo. This tag was completely missing in the standard format. The tax code description is a good place to store the justification, the Customer invoice model has a node to store this data, but in the standard mapping the tax code description was not bound to the model. The derived mapping needs to be adjusted at the node CustInvoiceJour/<Relations/TaxTrans :
@.'>Relations'.TaxTable.TaxName

A new conditional XML element shall be added to the format configuration:
XMLHeader/p:FatturaElettronica/FatturaElettronicaBody/DatiBeniServizi/DatiRiepilogo/RiferimentoNormativo:
@.lines.TaxDescription

The Enabled-condition for the tag shall trigger the XML element only if the delivery is tax exempt, and this correlates with the “Natura” tag:
@.lines.'$IsVisibleNatura'

Credit notes in FatturaPA and ESTEROMETRO

The worst comes at the end. It turned out that credit notes ( = TD04) must have the positive sign throughout the XML file. This requires massive changes in the DettaglioLinee section
ABS(@.LineBase.Quantity)
ABS(IF(@.LineBase.Quantity<0, -(1)*@.LineBase.'$FinalUnitPrice', @.LineBase.'$FinalUnitPrice'))
DatiRiepilogo
ABS(@.aggregated.Amount_Sum)
and DatiPagamento sections:
ABS(@.DueAmount)
Similar amendments but just at one place should be made in the ESTEROMETRO (which is a kind of an EU Sales list report for Italy) electronic formats, such as the “Report customer invoices IT” and “Report vendor invoices IT”.

E-invoice and configurable business documents blog series

Electronic Reporting (ER) Cookbook 2: new tips from the kitchen

Since my last blog in 2017 a few things have changed. The developers in Moscow have been extending the module massively, and they seem to have fully embraced the Continuous delivery approach. Every new version or hotfix bears surprises, both pleasant and unpleasant. Here is a small personal excerpt:

1. 1. Configurations may be declared dependent on a particular application version or even hotfix. Should the developer of a configuration have failed to declare a dependency, the following message may appear on an attempt to import the configuration: “Method parmXXX not found in class ERxxx”, for example “Method parmERTextFormatExcelTemplate not found in class ERTextFormatExcelFileComponent”. It means the configuration definition cannot be de-serialized from an XML file in this old application version.
  2. From version 7.3 onward, parts of the application logic of the ER module had been extracted to the Microsoft.Dynamics365.LocalizationFramework .NET assembly for better performance. Now not only the configuration relies on a particular application version, but the application too demands a certain binary hotfix. For example, the SEPA camt.054 bank statement requires application hotfixes KB4096419, KB4092831, KB4089190, KB4077379, while the designer UI will not start without the ER cumulative update KB4090174 i.e. binary version 7.0.4709.41183
  3. From version 7.3 it had become possible to create inbound electronic formats i.e. those reading files instead of writing them. In 7.2 you could configure the same, but it was simply failing to work properly. It was not giving you any errors but failing to traverse records in the CSV file.
  4. Since version 7.2 you may define a Sharepoint folder destination (Organization administration > Electronic reporting > Electronic reporting destination). Consider the following scenario: the electronic report runs in a batch and saves files in a Sharepoint folder. A client-side process synchronizes the folder with the local network, and a local daemon picks up the files to push them through a legacy banking software or a tax authority middleware.
  5. When reading or writing data in D365FO, the ER is able to scan 1:n and n:1 table and entity relations for foreign keys. However, your new tables and entities are not going to appear under the Relations node of the data model mapping in the Designer, as the ER module maintains its own list of table relations in the database. Even worse, it may import and run a custom configuration with no errors but silently skip the custom table on reading and writing. Apply Organization administration > Electronic reporting > Rebuild table references to make your custom tables available.
  6. When developing or customizing configurations, it is not necessary to Complete the versions every time to test them. The ‘integration point’ e.g. a payment journal may execute the latest Draft version of the format too. Use the button Advanced settings / User parameters / Run settings, then flip the Run draft slider that appears.
  7. After upgrading to 7.3 you may find yourself unable to edit configurations anymore. The reason may be the new parameter Enable design mode available at the Electronic reporting workspace, form Electronic reporting parameters.
  8. It seems that they’ve protected configurations made by Microsoft from editing in one of the recent updates. Instead, you are supposed to always derive a configuration from the base one, and assign it a custom Provider. This corresponds to the extension paradigm as opposed to ‘overlayering’ of a configuration. Now, what are you going to do with your existing customized formats? Create an own provider (Organization administration > Electronic reporting > Configuration provider table), give it a name and a web address of your organization. Make this provider Active at the ER workspace. Export your configuration into an XML file, then delete it from the list of solutions in ER. Replace the XML tag with your provider, then re-import the configuration from the file. The system creates a new Draft version you are now able to edit.