Introduction

Once we've got a request from a user to help him to build certain invoice template in SDL Trados Business Manager. Among complexities which this user met, we can point out to the following:

• Under tax name, different texts should be printed depending on applied VAT and customer country (see below);
• Payment details must be printed to the left from invoice totals, which seemed complex because of horizontal bands which forms a structure of a report in TBM;
• In the totals part, final amount should be printed both in customer's currency and user's local currency, for tax purposes.

Requirements to conditional text which depends on customer country and tax

A: The client is a non-taxable person: 

1: The client is a Business entity and has a VAT number, then print this in VAT note:

The services are taxable in the country where the recipient is established (Reverse charge cross-border supply of services to a customer in another EC country (Article 44 of Council Directive 2006/112/EC))

Applicable when the client is a translation agency and my services are intermediary (I don’t serve the end clients), VAT should be charged to the end clients only by the translation agency, and I shouldn’t add any taxes to my invoices. In this case, the tax rate is 0%

2: If the client is a Business entity outside the European Union and doesn’t have a VAT number, then print this in VAT note:

The services are not taxable because the recipient is not established in the EU (Exempt cross-border supply of services to a customer in a non-EC country (Article 44 of Council Directive 2006/112/EC))

B: The client is a taxable person:

1: The client is a Business entity within the EU and has a VAT number (it’s the end-client for me), then print this in VAT note:

2: The client is an individual and he/she is my end client

In both cases I should charge my default tax (e.g. 17%) and I need to mention this:

17% VAT is applicable under the Luxembourgish VAT Law of 12 Feb. 1979, as amended thereafter

Invoice design

Invoice should be designed as shown in the attached file (which also includes few comments explaining what user wishes to see in the resulting document).DOCX

Solution

Since attached design differs from all built-in designs, we can duplicate any built-in template, for example last one - Invoice v7.

In the designer, first of all let's adapt invoice header, which is simplest task.

1. Select whole report (click outside of paper, for example) and change default Font to Calibri, size 12. This will set default font to all labels in the report.
2. While working on the template, if particular label use non-default font, click on the green box near Font property of that label and select Reset, so this element will start using default font select for the whole report:
   
3. To put all customer's and user's details inside single blue frame, put Panel element onto the designer surface, and configure its border:
   
   
4. Then, simply put all labels containing customer's and user's name, address, VAT, etc., inside this panel.
5. Modify table header design: set background and foreground colors, set grey borders. To display currency name in the last column, bind it to Currency.Name field and set format string to Job Total ({0}):
   
6. Modify InvoiceRow detail band under Invoice rows (Rows to print) details report to display information required by user:
   1. Purchase order will be linked to Rows to print.Job.PO number field
   2. Job/project number - Rows to print.Job.Client ref number
   3. Job completed - Rows to print.Job.Date delivered
      
      
   4. Job description - Description
   5. Project manager - Rows to print.Job.PM.Full name
   6. Service - Work type.Name
   7. Units - Unit.Name
   8. Quantity - Quantity
   9. Unit price - Price
   10. Job total - Amount

For quantity, price and amount, use this format string: {0:###,###,###,###,##0.00###}. This means that value will be presented as grouped number, with minimum 2 decimals, and maximum 5 decimals if they present in a number. To understand format string better, you can search in Internet about formatting numbers in .NET Framework.

You can download final template, import it (clone predefined template, open in in the designer, right click outside paper and select Import) and click on elements to view fields which they are bound to in the Field list panel. 

Header and table contents are ready. Next part is invoice totals and payment information which must be printed to the left of totals.

Report designer has a feature which allows to print band contents across next bands. However, this feature is available only for Group header bands. However, at this point, after table, we can only display group footer. To overcome this, we should add nested detail report linked with Rows to print table again. This time, there is no need to display any data from this table, we only need to get a structure Report header - Details - Sub-bands - Report footer. We have to decrease height of the Details band to zero, so it doesn't occupy space, because it will be printed for each invoice row again. But since its height is zero, it won't be visible in a generated document.

So, we add new nested report, link it with Rows to print table, add Header band to this report and put payment information into this band:

Then, open quick properties of the header band and check Print across bands checkbox:

From now, contents of this band will be merged with next bands (so you have to remember that it won't be a good practice to put any contents of the following bands right below contents of this band, because in this case text could be overlapped.

Since we created new nested report, we have to move the whole invoice totals group from first Detail report (where they were created in original built-in template) to the new one. To do this:

1. Add report footer to the new detail report.
2. Create necessary number of sub-bands (for each tax, discount, charged expenses, etc.)
3. For each sub-band, set Visibility property as it was set in original template and apply same conditional formatting rules. For example, sub-band for tax 1 is not visible by default, and Tax1 rule is applied in the Formatting rules property of this band:
   
4. To get details of rules, click Edit rule sheet button. List of all formatting rules created in the report will be opened. In this editor, you can select any rule to view its expression and behavior. For example, if you select Tax1 rule, you will see that it sets Visibility property to false with the following expression: !IsNull([Tax 1]). In human words, if Tax 1 of an invoice is not empty, element to which this rule is applied will become visible. To get more information about formatting rules, please check this article:
   https://community.sdl.com/product-groups/translationproductivity/business-manager/w/wiki/4962/formatting-rules
5. Based on user's requirements, we need to create few labels with different texts and show/hide these labels based on some conditions. To solve this task, the following rules were created (all three rules set Visibility to true of an attached element):
   1. Name:
      ruleTaxNoteVat
      Condition:
      [Tax 1].[Rate]>0
   2. Name:
      ruleTaxNoteZeroEU
      Condition:
      [Tax 1].[Rate]=0 And Not IsNullOrEmpty([Customer].[VAT])
   3. Name:
      ruleTaxNoteZeroOutEU
      Condition:
      [Tax 1].[Rate]=0 And IsNullOrEmpty([Customer].[VAT])
6. To better solve this task, we can create boolean custom field and call it 'EU', for example, which will allow to distinguish EU and non-EU customers. Then, this field could be used in our expressions. For simplicity, our rules rely on tax 1 rate and presence of VAT number for a customer.
7. Create three labels with VAT notes, set their visibility to false and attach corresponding formatting rules to each of these labels.
8. On the designer surface, place all three labels one above other (so they get overlapped):
   
   Since only one label will be made visible by conditional rules, in a generated report overlapping issue won't appear.

Final task is to display currency rate and amount in base currency in the last row of totals section. For this, we create a label and apply Mail merge feature, by including multiple fields in one label:

Since text on the screenshot isn't visible, the whole contents of the left label looks like this:

Exchange rate ([Currency.Name]>[User.Base currency.Name]): [Invoice.Currency rate!n4]
([Date!d] ECB)

Here you can see that we access user settings (base currency name) and apply different formatting (for example, n4 to currency rate) in one label.

That's all. The task is solved - another complex template has been implemented using invoice designer. Final result can be downloaded here:

Invoice v7 Lux.zip