Example 6: Sales Invoice Report

Objective

In this example we want to create the replica of the built-in Sales Invoice report. The data source, however, has been reshaped and restructured to be much more easier to use and maintain, also by end users. We are going to see some cool design techniques to achieve the same kind of layout as we are used to from the original Sales invoice report.

Final result

The template uses several conditional content segments, so the final result will vary based on the values from the data in the report DDSP file. For instance, the final document in the figure below contains the table with depreciation of prepayments and the table with information of prepaid and remaining amount. This would not show in the report, if there were no prepayment information in the DDSP file.

Design considerations

Although the report results on the figure above are not very complex, the nature of the Sales invoice report is such that it must cover many different scenarios: backorders, prepayments, taxes breakdown, totals, notes and remarks, different currencies, etc.

We must therefore design the template with following segments:

  • First page header.
  • First page footer and default footer.
  • Upper part of the invoice.
  • Invoice items with additional item information in the Description column.
  • Backorder table.
  • Charges table.
  • Sales taxes totals table.
  • Payment schedule table.
  • Depreciation of payments table.
  • Header notes
  • Packing information table.
  • Remarks.
  • Tax text.
  • Table with reamining amount due (in case of prepayment).
  • Tax totals table.
  • Totals table.
  • Concluding information.

We will now learn how we can implement very complex report templates using various techniques in Docentric AX.

Using tables to position data on the report template

Word offers very powerful support for tables and we will take advantage of that in our reports. Using tables it is very easy to control exact position of the text and its alignment and wrapping. Instead of tables we could use tabs, but since the text wraps dynamically, we could run into problems with text longer than the space between tabs.

So we will use tables in our example.

Table borders visibility

We have many options for setting table borders in the final report: we may set them to be invisible or we may set them to many other combinations (see Word documentation for details). In our report we will set the borders on the upper part of the report to be invisible. Other tables, e.g. invoice items, will have some visible borders.

Table borders are set on the HOME tab on the menu ribbon, in the Paragraph button group, as shown on the figure below.

To set the table borders visibility during design of the report, we choose the LAYOUT tab on the menu ribbon and the View Gridlines icon. If we already see the gridlines, we can skip this. It is recommended to have this enabled, so that we can always see dotted lines where the table borders are, even if we set them to be transparent when previewed or printed.

Table cell alignment

The LAYOUT tab on the menu ribbon contains the Alignment button group where we can set horizontal or vertical alignment of currently selected cell(s).

Table cell margins

We can set how much empty space we want around the cell borders. This is done by selecting cell(s) first and then choosing the LAYOUT tab on the menu ribbon and clicking the Cell Margins icon in the Alignment button group. The dialog window opens, where we can set each of the margins as shown on the figure below.

Paragraph layout adjustments

We want to control some of the paragraph attributes as well: spacing, splitting and visibility of the paragraph markers. One way to do it is to prepare or modify a style where we set paragraph attributes as needed and then just apply this style to the text. Another way is to set paragraph attributes to individual paragraphs.

Visibility of the paragraph markers

Sometimes it is easier to design a document if we know exactly where the paragraphs are. We can switch this on and off by selecting the HOME tab on the menu ribbon and then clicking on the paragraph mark icon in the Paragraph button group as shown of the figure below.

Paragraph settings -> Keep with next

If we want to make sure that no page break happens between the two paragraphs then we can choose this option.

Paragraph settings -> Spacing

Paragraph spacing can be used to set the space before and after the paragraph and the space between the lines in the paragraph itself as the figure below demonstrates.

Docentric AX highlights

Hide or show a table row

After we select the table row, we click on the DOCENTRIC AX tab on the menu ribbon and we then click on the If tagging element. Now we have to enter the expression which returns boolean value. This cen be either a data field or an XPath expression.

Hide or show an entire table

We need to select the entire table first and then click on the DOCENTRIC AX tab on the menu ribbon and we then click on the If tagging element. There we enter conditional expression as explained above.

Explanation to the above figure:

  1. The entire table must be included in the If tagging element
  2. Child elements under the If tagging elements show what data will be affected
  3. This shows the expression which will determine whether the contents under the If element will be included in the report or not

Hide or show the text including surrounding paragraphs

In the figure below we have selected the paragraph before the text and the paragraph mark immediatelly after the text.

Binding If element to values from different data sections

Below is an example how to create an XPath expression in the If tagging element which uses values from different data sections.

Binding Field element to XPath expression

By using XPath expression we can shape data just the way we need. In the example below, we are using IIF functions to show the data based on the contents of fields.

We can see that the expression above is somewhat complex. The other fields which come below the one, explained above, also have XPath expressions in the If tagging element to toggle their visibility. They should only be visible if they contain value, if they don’t the entire line must also be excluded. Therefore we have implemented the If tagging element on the new line marker after each field. This requires some more effort but it gets the job done.

Example where we should introduce data shaping in report DSP class

In the example, explained above, we need to collapse each line which is empty, and because we have four fields possibly empty, we have to exercise with XPath a lot. That’s why it is better to prepare this data in X++.

Some other tips and tricks for more productive report design

Cursor positioning in and out of a tagging element

When we click on a tagging element the element we can see it’s borders and a small tab which tells its type. If our element is nested, we can see borders and types of parent elements.

Explanation to the above figure:

  1. We clicked on the If tagging element which contains only a new line marker
  2. All these tagging elements are nested one within another in parent-child relationships
  3. Element tree shows us exact element where we are at the moment

When we want to enter text or another tagging element next to already existing one, we can use arrow keys to position the cursor out of the existing element. When we are out of the element, its border and tag with the type of the element disappears. In the figure below we can see on the left that cursor is in the Field tagging element and on the right the cursor is out of the Field tagging element after we have pressed right arrow key a couple of times.

Accessing the attribute of the first element in collection in XPath expression

Sometimes we want to access an attribute from the first element in the collection. This cen be done using XPath, as shown below:

SalesInvoiceLines_Tax[1]/@ShowTaxTrans

which reads the ShowTaxTrans attribute from the first SalesInvoiceLines_Tax element in the collection.

Sorting the collection

A collection is bound to the List tagging element. We can use Element tree to select a particular List element and set up its sorting.

Explanation to the above figure:

  1. Selected List element
  2. Sort dialog button
  3. Buttons to add or remove sorting field
  4. Sorting field selection and sort order

Setting up labels for multi-language reports

For one-language reports we can just use static text for data labels. For multi-language reports we must use Label tagging elements that are bound to the labels of the data fields and records from report data source. Label tagging elements can also be bound to so-called custom labels that are defined per report in the report setup data. We usually introduced custom labels for a report when we need different labels than those assigned to data fields and records included in report data source. At runtime all used Label tagging elements are replaced with the corresponding translations in the report runtime language.

Read more about multilingual reports >>

Using XPath with the Field tagging element

Field taging elements get their values from the data fields in the data source package file. One of the great features of Docentric AX is the XPath support in Field tagging elements. In this way it is possible to manipulate and shape the data in many ways.

Step by step instructions

We will create a new document and make it a Docentric AX template by clicking the Use As Template button on the DOCENTRIC AX ribbon tab. The file dialog will open where we must choose corresponding Data Source Package file (.ddsp).

Step 1: Headers and Footers

The document will have different first page header and footer, so we set this first (PAGE LAYOUT | Margins | Custom Margins | Layout | Different First Page).

First page header:

Data Tagging Element Binding Source Binding Path
Company name Field General Data CurrentCompany/@Name
Address Field General Data CurrentCompany/PrimaryPostalAddress/@Address
Logo Image General Data CurrentCompany/@Logo

First page and default footer: see Example 3 for detailed instructions.

Step 2: Upper body of the invoice template

The upper part of the invoice looks like if it consisted of two columns with several rows in each of them. Each of this rows contain a label and a value from the data source. We are going to create a table with 4 columns to achieve this layout on the template. Leftmost two columns will contain labels and values on the left side and rightmost two columns will contain labels and values on the right side of the upper body.

The cell where the name and the address of the recepient is displayed, will consist of vertically merged cells to provide enough space for the data. Then we will add labels and data fields into those cells, so that we get the result as on the figure below:

Left side of the upper body:

Data Tagging Element Binding Source Binding Path
Invoice recipient’s name Field Main Data SalesInvoiceHeader/@InvoicingName
Invoice recipient’s address Field Main Data SalesInvoiceHeader/@InvoicingAddress
Contact Field Main Data SalesInvoiceHeader/@ContactPersonName
VAT Field Main Data SalesInvoiceHeader/@VatNum
Packing duty license number Label Main Data SalesInvoiceHeader/TaxLicenseNum/@SYS58658
Packing duty license number Field Main Data SalesInvoiceHeader/@TaxLicenseNum
List code Label Main Data SalesInvoiceHeader/Listcode/@SYS21837
List code Field Main Data SalesInvoiceHeader/Listcode/@Text

Conditional content:

Data Conditional Expression
VAT fixed-text label SalesInvoiceHeaderExt/Flags/@PrintFreeTextInvoiceVATNum
SalesInvoiceHeader/@VatNum data field SalesInvoiceHeaderExt/Flags/@PrintFreeTextInvoiceVATNum
ListCode custom label (data-source(‘Parameters’)/@SalesInvoiceDS_CountryRegionISOCode = ‘FI’ or
data-source(‘Parameters’)/@SalesInvoiceDS_CountryRegionISOCode = ‘SE’) and
(SalesInvoiceHeader/Listcode/@Name = ‘TriangularEUTrade’ or
SalesInvoiceHeader/Listcode/@Name = ‘TriangularProductionOnToll’)
SalesInvoiceHeader/Listcode/@Text data field (data-source(‘Parameters’)/@SalesInvoiceDS_CountryRegionISOCode = ‘FI’ or
data-source(‘Parameters’)/@SalesInvoiceDS_CountryRegionISOCode = ‘SE’) and
(SalesInvoiceHeader/Listcode/@Name = ‘TriangularEUTrade’ or
SalesInvoiceHeader/Listcode/@Name = ‘TriangularProductionOnToll’)

Right side of the upper body:

Data Tagging Element Binding Source Binding Path
Telephone Label General Data CurrentCompany/Phone/@SYS7869
Telephone Field General Data CurrentCompany/@Phone
Fax Label General Data CurrentCompany/Telefax/@SYS7888
Fax Field General Data CurrentCompany/@Telefax
IBAN Label General Data CurrentCompany/BankAccount/IBAN/@SYS71686
IBAN Field General Data CurrentCompany/BankAccount/@IBAN
Tax registration number Label General Data CurrentCompany/RegistrationNumbers/CoRegNum/@SYS969
Tax registration number Field General Data CurrentCompany/RegistrationNumbers/@CoRegNum
VAT Field General Data CurrentCompany/SalesTax/@VATNum
Document title Field Parameters @DocumentTitle
Reference number Field General Data SalesInvoiceHeader/@InvoiceReferenceNumber
Number Field Main Data SalesInvoiceHeaderExt/@InvoiceId
Invoice date Field Main Data SalesInvoiceHeader/@InvoiceDate
Sales order Label Main Data SalesInvoiceHeader/SalesId/@SYS9694
Sales order Field Main Data SalesInvoiceHeader/@SalesId
Requisition Field Main Data SalesInvoiceHeader/@PurchaseOrder
Your reference Field Main Data SalesInvoiceHeader/@CustomerRef
Our reference Field Main Data SalesInvoiceHeader/@SalesAdministrator
Payment Label Main Data SalesInvoiceHeader/PaymentCondition/@SYS828
Payment Field Main Data SalesInvoiceHeader/@PaymentCondition
Invoice account Label Main Data SalesInvoiceHeader/InvoiceAccount/@SYS25708
Invoice account Field Main Data SalesInvoiceHeader/@InvoiceAccount
Payment reference Label Main Data SalesInvoiceHeader/PaymentReference/@SYS22514
Payment reference Field Main Data SalesInvoiceHeader/@PaymentReference
Payment ID Label Main Data SalesInvoiceHeader/PaymId/@SYS11443
Payment ID Field Main Data SalesInvoiceHeader/@PaymId

Conditional content:

Data Conditional Expression
The entire table row containing ‘Reference number’ fixed-text label and SalesInvoiceHeader/@InvoiceReferenceNumber data field SalesInvoiceHeaderExt/Flags/@CustInvoiceRefNum_FI
The entire table row containing ‘Payment reference’ custom label and SalesInvoiceHeader/@PaymentReference data field SalesInvoiceHeaderExt/Misc/GiroType/@Name = ‘BelSMS101’ or
SalesInvoiceHeaderExt/Misc/GiroType/@Name = ‘BelSMS102’
The entire table row containing ‘Payment ID’ custom label and SalesInvoiceHeader/@PaymId data field SalesInvoiceHeaderExt/Flags/@IsPaymIdVisible

Invoice items table:

Invoice items table lists invoice items, but we must apply condition, to get only those items which we want in this table (items collection includes other lines, e.g. prepayments, which we don’t want to appear in this table).

Data Tagging Element Binding Source Binding Path
Item Number Label Main Data SalesInvoiceLines/ItemId/@SYS12836
Description Label (fixed text) “Description”
Quantity Label Main Data SalesInvoiceLines/Qty/@SYS14578
Unit Label Main Data SalesInvoiceLines/SalesUnitTxt/@SYS190813
Unit price Label (fixed text) “Unit price”
Discount percent Label Main Data SalesInvoiceLines/DiscPercent/@SYS7813
Discount Label Main Data SalesInvoiceLines/DiscountAmount/@SYS11829
Amount Label Main Data SalesInvoiceLines/LineAmountInclTax/@SYS6928
Lines collection List Main Data SalesInvoiceLines
Line header Field Main Data SalesInvoiceLines/@LineHeader
ItemId Field Main Data SalesInvoiceLines/@ItemId
Name Field Main Data SalesInvoiceLines/@Name
ExternalItem Field Main Data SalesInvoiceLines/@ExternalItem
InventDimPrint Field Main Data SalesInvoiceLines/@InventDimPrint
Notes Field Main Data SalesInvoiceLines/@Notes
PackingSlip Field Main Data SalesInvoiceLines/PackingSlip/@PackingSlip
Qty Field Main Data SalesInvoiceLines/@Qty
U Field Main Data SalesInvoiceLines/@SalesUnitTxt
SalesPrice Field Main Data SalesInvoiceLines/@SalesPrice
DiscP Field Main Data SalesInvoiceLines/@DiscPercent
DiscAm Field Main Data SalesInvoiceLines/@DiscountAmount
Amount Field Main Data SalesInvoiceLines/@LineAmountInclTax

Conditional content:

Data Conditional Expression
Entire sales items table count(SalesInvoiceLines[@LineHeader != ” or
@ExternalItemId != ” or InventDim/@InventDimPrint != ” or
@Notes != ” or PackingSlip/@PackingSlip != ”]) > 0
LineHeader field @LineHeader != ”
Additional item information @ExternalItemId != ” or InventDim/@InventDimPrint != ” or
@Notes != ” or PackingSlip/@PackingSlip != ”
Additional item information –
new line marker after the first line
(@ExternalItemId != ” or InventDim/@InventDimPrint != ”) and
(@Notes != ” or PackingSlip/@PackingSlip != ”)
Additional item information –
new line marker after the second line
@Notes != ” and PackingSlip/@PackingSlip != ”

Resources

Data source (DDSP file) Template

IN THIS ARTICLE