Technical debt of customised SSRS Invoices

This chapter concerns Dynamics users who utilised SSRS in the past, or intend to further use their SSRS invoices with the Dynamity Group’s SSRS-to-ZUGFeRD convertor.

In these installations, we often see the SSRS Report Data Provider (RDP) customised to a certain extent to make the printed invoice show information that the standard invoice layout did not expose conveniently. For example, the abovementioned markup/charge details was a common requirement, there the charges started appearing among the invoice lines and not in a separate section as intended by Microsoft; namely, the typical legacy approach was to inject additional records into SalesInvoiceTmp that behaved like invoice lines from the SSRS report’s perspective. This made it easy for the report designer to render the information in the same repeating line section as normal sales lines. Such customisations could also carry other display-only information that was useful on the printed invoice.

This creates a problem when the same SalesInvoiceTmp dataset is reused as the source for ZUGFeRD/Factur-X:

• SSRS asks essentially: “What should I display on the printed invoice?”
• ZUGFeRD/ER asks: “What are the actual commercial invoice lines and their tax/amount semantics?”

With the RDP customised, those are not necessarily the same. Consequently, a record that was perfectly useful as a display-only pseudo-line in an SSRS report can look like a malformed invoice line to CII/ZUGFeRD. It may have an amount or description but lack the quantity, item, tax code or other information expected for a genuine IncludedSupplyChainTradeLineItem.

To resolve this abiguity, the SalesInvoiceTmp data had to be analysed first, the malformed or unintended records identified, and then…

1. either the upstream/customised D365 logic corrected,
2. or the rogue SalesInvoiceTmp filtered out so that ZUGFeRD receives a clean dataset,
3. or the ER binding made more tolerant to these additional records.

The options 2 and 3 are elaborated below on two real-world examples.

Case 2: remove the pseudo-lines from the lines list. In the “Invoice model mapping” there is already a filter of invoice lines to separate true invoice lines from markup records. The latter bear no quantity:

$SalesInvoiceTmp_Lines as WHERE('$SalesInvoiceTmp', '$SalesInvoiceTmp'.Qty <> 0)

This is exactly the place where you can start adding criteria to filter out other pseudo-lines.

Case 3: the developers added records to SalesInvoiceTmp representing order-level surcharges. Unlike the previous cases, we decided not to remove them from SalesInvoiceTmp. They were intended to remain visible in the electronic invoice as IncludedSupplyChainTradeLineItem lines.

The problem was that these pseudo-lines did not contain the same tax information as regular invoice lines. In particular, the expected TaxWriteCode was not available to the ER data source, resulting in an empty value being passed to the tax-category mapping.

As a solution, where no Print code/tax code was available, the tax category was derived from the first relevant tax transaction, based on the assumption that the invoice contains either zero-rated tax or the applicable standard/reduced German VAT rates, but not a mixture of zero and taxable rates.

A second adjustment was required because the surcharge records were effectively represented twice in the generated XML: once as invoice lines and again as charges. The pseudo-charge lines therefore had to be excluded from the charge calculation so that ChargeTotalAmount remained zero.

Here again, the collection $SumMCRMarkupCalculatedAmount2 was leveraged. When looping through invoice lines and encountering a line representing a charge, the charge amount was recoded to get subtracted at the end. This was achieved with the following dirty trick:
IF(@.TaxWriteCode="",model.InvoiceBase.’$FirstTaxTrans’.’$TaxWriteCode2Category’&

LEFT(TEXT(model.InvoiceBase.’$SumMCRMarkupCalculatedAmount2′.Collect(@.LineBase.Amount)*0),0),

@.’$TaxWriteCode2Category’)

The amount of the charge has nothing to do with the VAT category, but at that tag the charge amount was evaluated, added to the collection, then reduced to nil with LEFT(TEXT(…),0) = “” .