ER: Add charge details to the Sales invoice report

Standard sales order invoice report shows only total charge value in the totals area. One of the common requirements we get from our customers is to display charge value breakdown in the invoice by charge code. In this post we will review how to achieve that.

Interesting enough, if you open standard sales invoice report template supplied by Microsoft you will see section that shows charge amount breakdown by charge codes in it, however it works only for Finland (FI) localization and you will never see it in your report layout.

In our example, to minimize the number of changes in the report, we will repurpose existing fields from the standard format and modify only model mapping.

As usually, if you modify existing report format, you must load it from LCS or Global repository. I have loaded “Sales invoice (Excel)” already.

Modify model mapping

To open the list of existing configurations in your environment go to Organization administration > Workspaces > Electronic reporting > Reporting configurations:

Then we must create Derived model mapping. I have named mine “Invoice model mapping MarkupTrans”.

Click Designer, select Sales invoice report and open designer for it:

There is some difference in charges data selection (MarkupTrans) for the invoice and proforma. For the invoice we should select MarkupTrans related to the CustInvoiceJour and CustoInvoiceTrans records, for the proforma we should select MarkupTrans related to the Sales order header (SalesTable) and lines (SalesLine).

To handle both scenarios we must add SalesTable as table records data source to the model mapping.

Just to keep tables separately from other source types I have created container “Tables” under the root data source:

Click OK. Then we should add SalesTable into it.

On the next step we should create calculated field ($SalesInvoiceHeaderFooterTmp_SalesTable) with the formula that filters SalesTable by the same order number as we have in the invoice:

Click Edit formula and paste below:

FILTER(Tables.SalesTable, Tables.SalesTable.SalesId='$SalesInvoiceHeaderFooterTmp'.SalesId)

As I mentioned in the beginning of this post data source and model already has markup info that is used by Finland localization, so we will make some adjustments to it.

$SalesInvoiceTmp_Markup data source is bound with Charges transaction for lines(MarkupTransactionLines), at the same time fields under MarkupTransactionLines are bound in the report format with the charges details section.

We are not going to use existing $SalesInvoiceTmp_Markup calculated field and will create new one instead, because of that, we would have to bind fields under MarkupTransactionLines with new source.

Let’s create new calculated field “$SalesInvoiceTmp_MarkupTrans”

Click Edit formula button and enter below formula.

IF('$SalesInvoiceTmp'.InvoiceId = "",
LISTJOIN(ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp_SalesTable'.'<Relations'.MarkupTrans),
ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp_SalesTable'.'<Relations'.SalesLine.'<Relations'.MarkupTrans)), LISTJOIN(ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp'.'>Relations'.JournalRecId.'<Relations'.'CustInvoiceTrans.CustInvoice'.'<Relations'.MarkupTrans), ALLITEMSQUERY('$SalesInvoiceHeaderFooterTmp'.'>Relations'.JournalRecId.'<Relations'.MarkupTrans)))

As you can see from the formula, we follow different approach to select charges for actual invoice and proforma (proforma is an invoice with empty InvoiceId field). Using ALLITEMSQUERY formula we get all MarkupTrans related to SalesTable/CustInvoiceJour and SalesLine/CustoInvoiceTrans and merge them together with LISTJOIN.

Considering that header and/or lines may have same charge codes assigned we should group them together. This can be done by using Group by data source type.

We should create one called “$SalesInvoiceTmp_MarkupTransGroupby”:

Then click Edit group by button and select $SalesInvoiceTmp_MarkupTrans to add it as a source to group:

Expand the node and select fields we should use for grouping. Standard report format has 3 columns in the charge details area with the following names: Charge code, Tax code and Value.  We are going to remap all of them, therefore, we will perform grouping by MarkupCode (for Charge code) and SalesTaxGroup (for Tax code) fields.

To Sum up charge value we need to add Value field to the aggregation fields area and Select “Sum” method for it.

Save the changes and close the form. After all required details are added to the model, we can finally change mapping. Find MarkupTransactionLines under InvoiceBase node and bind it with $SalesInvoiceTmp_MarkupTransGroupby added above:

Then we should remap fields that already exist in Sales invoice report with the fields from $SalesInvoiceTmp_MarkupTransGroupby datasource:

Done. We’ve finished with the changes and can mark model as complete and mark it as default for mapping:

Setup print management

To test it we have to setup Print management to use standard Sales invoice (Excel) report

Setup electronic reporting destination

In addition, we should create electronic reporting destination for our Report format.

Run sales invoice

To check the results, I have created sales order with 2 lines for items A0001 and A0002.

Also, I added header charge for FREIGHT = 5 USD as well as line level HANDLING charge of 0.1 USD for the line 1 and 0.25 USD for the line 2. Then I posted an invoice for it, the report looks as follows:

Run proforma invoice

To check proforma layout I have modified same order to contain one more line A0001 with HANDLING charge 0.15 USD and header FREIGHT charge 3 USD. When I run Invoice proforma it looks as follows:

As you can see both Invoice and Proforma show respective charges. We have not made any changes to the report format itself and used standard layout, however, if you want to change the location of these fields or add any other details from MarkupTrans to the report layout you can do it by modifying report format.