In TX Text Control X16, Excel compatible formulas can be used in table cells to calculate results based on values and references to other cells in the same table. More than 100 different formulas are supported and can be combined.

This feature gets even more powerful when used in combination with TXTextControl.DocumentServer.MailMerge class - TX Text Control's reporting engine to merge templates with data from various data sources.

The following screenshot shows a list of invoice items created with MailMerge:

The data for this example is a very simple XML:

<data>
    <invoice>
        <item>
            <qty>1</qty>
            <name>Road-650 Red, 44</name>
            <unit_price>419.4589</unit_price>
            <discount>0</discount>
        </item>
        <item>
            <qty>5</qty>
            <name>Road-450 Red, 52</name>
            <unit_price>809.76</unit_price>
            <discount>0</discount>
        </item>
        <item>
            <qty>1</qty>
            <name>HL Mountain Frame - Black, 38</name>
            <unit_price>714.7043</unit_price>
            <discount>12</discount>
        </item>
        <item>
            <qty>2</qty>
            <name>AWC Logo Cap</name>
            <unit_price>5.1865</unit_price>
            <discount>0</discount>
        </item>
        <item>
            <qty>10</qty>
            <name>Mountain-100 Black, 38</name>
            <unit_price>2024.994</unit_price>
            <discount>20</discount>
        </item>
        <item>
            <qty>13</qty>
            <name>Sport-100 Helmet, Blue</name>
            <unit_price>20.1865</unit_price>
            <discount>2</discount>
        </item>
    </invoice>
</data>

In the above XML source, you can see that only 4 data points are available:

• qty
• name
• unit_price
• discount

The other columns in the table are calculated based on these values.

The following screenshot shows the formula of the Line Total column in the ribbon UI. The value is calculated based on quantity multiplied the unit price minus discount.

The Total value is calculated with the following formula that creates a sum of all numeric rows above in the same column:

SUM(ABOVE)

Additionally, two values are calculated based on the existing data in the table.

• Average discount:
  SUM(ABOVE)/COUNT(ABOVE)
• Number of items:
  COUNT(ABOVE)-1

In this sample template, another new feature of MailMerge is used: Sorting. The complete list is sorted by unit price:

This sample shows the power of MailMerge in combination with Microsoft Excel compatible formulas. The major advantage of using formulas is that data, that could be calculated, must not be stored in your data source or must not be calculated in your business object. TX Text Control is doing this for you when the data is required.

Another great idea of TX Text Control X16 - stay tuned for more.