MailMerge: Dynamic, Custom SUM Fields in Merge Blocks

Text Control's Reporting engine is very easy to use and most typical applications are covered by the integrated reporting functionality. Thus, a typical report with nested merge blocks and other advanced reporting features can be merged with a single line of code - simply by calling the Merge method.

Generally, we recommend to separate data concerns from the visualization (the template). In other words: The template doesn't contain any business logic, but only the formatting and style of the report. Read details here: Advantages of using business objects with Text Control Reporting.

Anyway, sometimes, applications require to retreat from these principles. Thanks to the powerful API and MailMerge events, the whole merge process can be customized, if required.

In this sample, a SUM field, that contains the totaled values of a specific field in a merge block, should be calculated dynamically during the merge process.

At the end of a merge block, a field with the custom name SUM: + TotaledField is inserted. In our custom code, we will search for these fields starting with SUM:. This field should total up all fields with the name LineTotal that are listed in the same merge block.

This field name doesn't exist in our used data source. By default, those fields are removed automatically when there is no matching data column in the data source. Therefore, we have to set the RemoveEmptyFields property of our used MailMerge component to false:

Two events are required to update the custom SUM field. First, we need to do something that is non-obvious. By default, the field functionality of successfully merged fields are removed in the final document. To avoid that, the field value must be set in the FieldMerged event explicitly. To keep the same data, we simply pass the merge data to the field again:

private void mailMerge1_FieldMerged(object sender,
    TXTextControl.DocumentServer.MailMerge.FieldMergedEventArgs e)
{
    e.MergedField = e.MergedField;
}

In the DataRowMergedEvent, we use two loops to find the custom SUM fields and to total up the specified field values for the SUM field. The DataRowMergedEvent event is fired after a complete data row has been succcessfully merged. The data of the complete merged data row is available as a byte[] array in form of a TX Text Control Internal Format document. A temporary ServerTextControl is used to load this data row document and to manipulate the fields.

The custom SUM field is then populated with the calculated value and the complete data row document is loaded back to the merge process.

private void mailMerge1_DataRowMerged(object sender,
    TXTextControl.DocumentServer.MailMerge.DataRowMergedEventArgs e)
{
    byte[] data = null;

    // create a temporary ServerTextControl to work on the block data
    using (ServerTextControl tx = new ServerTextControl())
    {
        tx.Create();

        // load the block data into the temporary ServerTextControl
        tx.Load(e.MergedRow, BinaryStreamType.InternalUnicodeFormat);

        // loop #1 to find all SUM fields
        foreach (ApplicationField sumField in tx.ApplicationFields)
        {
            if (sumField.TypeName != "MERGEFIELD")
                continue;

            MergeField mf = new MergeField(sumField);

            // if SUM field found, loop through all fields again total them up
            if (mf.Name.StartsWith("SUM:") == true)
            {
                decimal fSum = 0.0M;

                // loop #2
                foreach (ApplicationField appField in tx.ApplicationFields)
                {
                    if (appField.TypeName != "MERGEFIELD")
                        continue;

                    MergeField mfa = new MergeField(appField);

                    if (mfa.Name == mf.Name.Substring(4))
                    {
                        fSum += Decimal.Parse(mfa.Text,
                            NumberStyles.AllowCurrencySymbol |
                            NumberStyles.AllowDecimalPoint |
                            NumberStyles.AllowThousands,
                            new CultureInfo("en-US"));
                    }
                }

                // set the total number
                mf.Text = fSum.ToString();
            }
        }

        // save the complete block to a byte[] array
        tx.Save(out data, BinaryStreamType.InternalUnicodeFormat);
    }

    // load back to the byte[] array to the MailMerge process
    e.MergedRow = data;
}

The resulting merged template shows the dynamic SUM field with the calculated value:

You can try this on your own by downloading the sample project and template. At least, a trial version of TX Text Control .NET for Windows Forms is required.