ActiveReports 11
Mail Merge with RichText
ActiveReports 11 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Section Report Walkthroughs > Layout > Mail Merge with RichText

ActiveReports supports field merged reports using the RichText control. The RichText control can contain field place holders that can be replaced with values (merged) at run time. This walkthrough illustrates how to create a mail-merge report using the RichText control.

This walkthrough is split up into the following activities:

Note: This walkthrough uses the Northwind database. By default, in ActiveReports, the Northwind.mdb file is located at [User Documents folder]\GrapeCity Samples\ActiveReports 11\Data\NWIND.mdb.

When you complete this walkthrough you get a layout that looks similar to the following at design time and at run time.

Design-Time Layout

Run-Time Layout

To add an ActiveReport to the Visual Studio project

  1. Create a new Visual Studio project.
  2. From the Project menu, select Add New Item.
  3. In the Add New Item dialog that appears, select ActiveReports 11 Section Report (code-based) and in the Name field, rename the file as rptLetter.
  4. Click the Add button to open a new section report in the designer.

See Adding an ActiveReport to a Project for information on adding different report layouts.

To connect the report to a data source

  1. On the detail section band, click the Data Source Icon.
  2. In the Report Data Source dialog that appears, on the OLE DB tab, next to Connection String, click the Build button.
  3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button to move to the Connection tab.
  4. Click the ellipsis (...) button to browse to your database, for example the NWind.mdb sample database. Click Open once you have selected the appropriate database path.
  5. Click the Test Connection button to see if you have successfully connected to the database.
  6. Click OK to close the Data Link Properties window and return to the Report Data Source dialog. Notice that the Connection String field gets filled automatically.
  7. In the Query field on the OLE DB tab, enter the following SQL query.
    SQL Query
    Copy Code
    SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.City, Customers.Region, Customers.Country, Customers.PostalCode, Orders.OrderID, Orders.OrderDate, [Order Subtotals].Subtotal
    FROM Customers INNER JOIN ([Order Subtotals] INNER JOIN Orders ON [Order Subtotals].OrderID = Orders.OrderID) ON Customers.CustomerID = Orders.CustomerID
  8. Click OK to save the data source and return to the report design surface.

To create a layout for the report

  1. On the design surface of the report, right-click and select Insert, then Group Header/Footer to add group header and footer sections.
  2. On the design surface, select the grey area outside the report and in the Properties window, set the PrintWidth property to 6.5
  3. Select the group header and in the Properties window, set the properties as follows.
    Property Name Property Value
    DataField CustomerID
    Height 2.5
    KeepTogether True
  4. On the design surface of the report, select the group footer section and in the Properties window, set the following properties. 
    Property Name Property Value
    Height 1.1
    KeepTogether True
    NewPage After
  5. On the design surface of the report, select the detail section and in the Properties window, set the CanShrink property to True.
  6. On the design surface of the report, select the pageHeader section and in the Properties window, set the following properties.
    Property Name Property Value
    Height 0.8
    BackColor Coral
  7. From the toolbox, drag the Label control to the pageHeader section and in the Properties window, set the properties as follows.

    Label

    Property Name Property Value
    Location 0, 0 in
    Size 6.5, 0.65 in
    Text GrapeCity
    Font > Size 36
    Font > Bold True
  8. In the Report Explorer, expand the Fields node, then the Bound node. Drag the SubTotal field onto the groupHeader section and in the Properties window, set the following properties.
    Property Name Property Value
    Location 4, 0 in
    Size 1, 0.2 in
    Name txtSubtotal1
    OutputFormat Currency
    Visible False
    SummaryType SubTotal
    SummaryGroup groupHeader1
    Note: Even though txtSubtotal1 is hidden, setting its properties is important as it provides the value and the formatting that is displayed in the RichText control.
  9. From the toolbox, drag the following controls to the groupHeader section and in the Properties window, set the properties as follows.

    RichTextBox

    Property Name Property Value
    Location 0, 0 in
    Size 6.5, 2.1 in
    AutoReplaceFields True

    Label1

    Property Name Property Value
    Location 0.875, 2.25 in
    Size 1, 0.2 in
    Text Order ID
    Font > Bold True

    Label2

    Property Name Property Value
    Location 1.875, 2.25 in
    Size 1, 0.2 in
    Text Order Date
    Font > Bold True

    Label3

    Property Name Property Value
    Location 4.375, 2.25 in
    Size 1, 0.2 in
    Text Amount
    Font > Bold True
    Alignment Right
  10. In the Report Explorer, expand the Fields node, then the Bound node. Drag the following fields onto the detail section and in the Properties window, set the properties of each textbox as follows.

    TextBox1 (OrderID)

    Property Name Property Value
    Location 0.875, 0 in
    Size 1, 0.2 in

    TextBox2 (OrderDate)

    Property Name Property Value
    Location 1.875, 0 in
    Size 1, 0.2 in
    OutputFormat Date (MM/dd/yy)

    TextBox3 (Subtotal)

    Property Name Property Value
    Location 4.375, 0 in
    Size 1, 0.2 in
    OutputFormat Currency
    Alignment Right
  11. From the toolbox, drag the following controls to the groupFooter section and in the Properties window, set the properties as follows.

    Label1

    Property Name Property Value
    Location 5.15, 0.15 in
    Size 1.35, 0.2 in
    Text Best regards,
    Alignment Right

    Label2

    Property Name Property Value
    Location 5.15, 0.8 in
    Size 1.35, 0.2 in
    Text Accounts Receivable

To add fields to the RichText control

  1. Double-click the RichTextBox control box and delete the default text.
  2. Right-click the box and choose Insert Fields.
  3. In the Insert Field dialog that appears, enter Date and click OK.
  4. Place the cursor in front of the text [!Date] that appears in the RichText control, and add spaces until the text is at the right edge of the control (but not overlapping to the next line).
  5. Place the cursor at the end of the text, and press the Enter key to move to the next line.
  6. Insert each of the following fields using the Insert Field dialog (see design time image above for fields arrangement):
    • CompanyName
    • ContactName
    • Address
    • City
    • Region
    • Country
    • PostalCode
    • SubTotal 
  7. Add the following text to the RichText control box after all of the fields.
    Paste into the RichText control
    Copy Code
    Dear [!ContactName], 
                
    Thank you for your business. Below is a list of your orders for the past year with a total of [!SubTotal]. 
    Please take this opportunity to review each order and total for accuracy. Call us at 1-800-DNT-CALL with 
    any questions or concerns.
    
  8. Arrange the text and fields within the control as you would in any text editor.

To use the FetchData event to conditionally format data

To write the code in Visual Basic

  1. At the top left of the code view for the report, click the drop-down arrow and select (rptLetter Events).
  2. At the top right of the code window, click the drop-down arrow and select FetchData. This creates an event-handling method for the report's FetchData event.
  3. Add code to the handler to add a comma and a space if there is a Region value for the customer's address.

The following example shows what the code for the method looks like.

Visual Basic.NET code. Paste JUST ABOVE the FetchData event.
Copy Code
Dim region As String
Visual Basic.NET code. Paste INSIDE the FetchData event.
Copy Code
'If there is no region for the customer, display nothing
If Fields("Region").Value Is System.DBNull.Value Then
    region = ""
Else
'If there is a region, add a comma and a space
    region = ", " + Fields("Region").Value
End If

To write the code in C#

  1. Back in design view, click in the gray area below the report to select it.
  2. Click the events icon in the Properties window to display available events for the report.
  3. Double-click FetchData. This creates an event-handling method for the report's FetchData event.
  4. Add code to the handler to add a comma and a space if there is a Region value for the customer's address.

The following example shows what the code for the method looks like.

C# code. Paste JUST ABOVE the FetchData event.
Copy Code
string region;
C# code. Paste INSIDE the FetchData event.
Copy Code
if(Fields["Region"].Value is System.DBNull)
  region = "";
else
  region = ", " + Fields["Region"].Value.ToString();

To add code to update RichText fields with the current date and conditional values

  1. Double-click in the group header section of the report to create an event-handling method for the group header's Format event.
  2. Add code to the handler to:
    • Replace the Date field in the RichText control with the current system date
    • Replace the Region field with the conditional value created in the FetchData event

To write the code in Visual Basic.NET

Visual Basic.NET code. Paste INSIDE the Group Header Format event.
Copy Code
'Use the current date in the letter
Me.RichTextBox1.ReplaceField("Date", System.DateTime.Today.Date.ToShortDateString())
'Use the value returned by the FetchData event
Me.RichTextBox1.ReplaceField("Region", region)

To write the code in C#

C# code. Paste INSIDE the Group Header Format event.
Copy Code
//Use the current date in the letter
this.richTextBox1.ReplaceField("Date", System.DateTime.Today.Date.ToShortDateString());
//Use the value returned by the FetchData event
this.richTextBox1.ReplaceField("Region", region);

To add code to send the group subtotal value to the RichText field

To write the code in Visual Basic.NET

  1. Right-click in any section of the design window of rptLetter, and click on View Code to display the code view for the report.
  2. At the top left of the code view for rptLetter, click the drop-down arrow and select GroupHeader1.
  3. At the top right of the code window, click the drop-down arrow and select BeforePrint. This creates an event-handling method for rptLetter's GroupHeader1_BeforePrint event.
    Note: We use the BeforePrint event instead of the Format event to get the final value of the subtotal field just prior to printing. For more information on section event usage, see the Section Events topic.
  4. Add code to the handler to replace the value of the Subtotal field in the RichText control with the value of the hidden textbox in the group header.
    Visual Basic.NET code. Paste INSIDE the Group Header BeforePrint event.
    Copy Code
    'Use the value from the hidden group subtotal field
    Me.RichTextBox1.ReplaceField("SubTotal", Me.txtSubtotal1.Text)
    

To write the code in C#

  1. Back in design view, click the group header section to select it.
  2. Click the events icon in the Properties window to display available events for the group header.
  3. Double-click BeforePrint. This creates an event-handling method for the report's BeforePrint event.
    Add code to the handler to replace the value of the Subtotal field in the RichText control with the value of the hidden textbox in the group header.
    The following example shows what the code for the method looks like.
    C# code. Paste INSIDE the Group Header BeforePrint event.
    Copy Code
    //Use the value from the hidden group subtotal field
    this.richTextBox1.ReplaceField("SubTotal", this.txtSubtotal1.Text);
    

To view the report

OR