ActiveReports 14
ActiveReports 14 User Guide / Samples and Walkthroughs / Walkthroughs / Section Report Walkthroughs / Layout / Mail Merge with RichText
In This Topic
    Mail Merge with RichText
    In This Topic

    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. The NWIND.mdb file can be downloaded from GitHub: ..\Samples14\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 14 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 Quick Start 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

    • Click the preview tab to view the report at design time.

    OR