How to Build a Crystal Report Email Based on a Database Trigger

Part 1: Setting Up the Global Configurations and Connections

Before you can use the required tools in a task, you must complete the following setup first.

Part 1A — Installing the Crystal Report Runtimes

Although available as standard within BPA Platform, the Run Crystal Report tool must be licensed for use before you can install the necessary runtimes and use the tool.

1. Launch the BPA PlatformConfigurator You use the Configurator to install third-party software that may be required by available tools. Connection to required agents are also configured here. Note that you can only launch the Configurator if logged into the machine with Administrator rights..
2. From the Features pane, expand Tool Specific.
3. If Run Crystal Report has the attention icon: , the runtimes are not installed:
   1. Click Prerequisites.
   2. From the Configuration pane, click Install for the Crystal Reports Runtime Detection panel — an internet connection is required to download and complete the install.
4. Close the Configurator when the installation has completed.

---

Part 1B — Creating the Connection to the Database

---

Part 1C — Creating the Connection to the SMTP Server

The server hosting BPA Platform must have a valid, working connection to an SMTP server or SMTP service such as Office365™ (see How to Use an Office 365 Account for Send Email (SMTP) and BPA Platform Notifications) or Gmail^® (see How to Use a Google Account for Send Email (SMTP) and BPA Platform Notifications).

Add valid recipient and sender email addresses to the Address Book.

---

Part 1D — Creating the Microsoft SQL Server Trigger Agent

Every time a account goes over their credit limit, we want the task to run. We do this with the Microsoft SQL Server Trigger tool, which comes with an Agent that monitors the database for any activity.

The Microsoft SQL Server Trigger Agent is a service that sits between BPA Platform and SQL Server. It is installed onto any SQL Server instance and is responsible for both maintaining the triggers in SQL Server and for handling the running of a task once a trigger has been fired.

When a SQL Server trigger is fired, a connection is made to the Agent service which then launches the relevant task.

BPA Platform also supports the use of triggers on a SQL Server failover cluster, enabling the firing of triggered tasks from whichever server in the cluster is active at the time. An Agent must be installed on each node, starting with the active node. However, because all are monitoring the same SQL Server instance, the BPA Platform client sees only a single Agent registration.

If you created a Microsoft SQL Server Trigger Agent during installation, you can skip this exercise. Else, do the following:

1. Launch the Configurator.
2. Expand Event Agents > Microsoft SQL Server Trigger Agent > Prerequisites.
3. Ensure both prerequisites are green. If not, follow the on-screen instructions to resolve the outstanding issues.
4. Open the Configuration node.
5. Enable Add a new SQL Server Agent Instance.
6. From the SQL Server Instance drop-down, select the instance this Agent is monitoring — typically, this should be MSSQLSERVER.
7. Click Save.

   

   Click the image to view full size.

8. Ensure Server reflects the BPA Platform machine's hostname.
9. Click Save.
10. If OLE automation has not been enabled for this SQL Server instance, click Modify, select Enable OLE Automation, and click OK.
11. Click Next.

    

    Click the image to view full size.

12. When prompted enter the Administrator password for your BPA Platform installation.

    If you see an error about the credentials being used elsewhere, close the BPA Platform client first.

13. Close the Configurator.

Although the Microsoft SQL Server Trigger tool does have a global configuration, for this exercise no further configuration is required for the Microsoft SQL Server Trigger Agent.

Part 2: Capturing and Storing the Account Reference

This task uses a task variable Variables are created and used extensively throughout and can either have fixed values or values that are dynamically populated when a task is run. Task variables are only available for the specific task they are created for. Users with Task Administrator rights can create task variables. to capture the account reference number of the account on hold. This variable is recognised and can be used by all steps in the task.

1. Create a new task named Customer Accounts on Hold.
2. Add a Description: Email customer when their account is placed on hold.
3. Go to the Design tab.

Task variables are created in the task itself, and are not available for use by other tasks.

1. From the Task Browser, go to the Environment tab.
2. Change Scope to be Task.
3. Right-click on Variables and select New.
4. Name the variable: AccountRef.
5. Go to the Details tab.
6. Set Type to be Variant.

   This ensures the variable can store any type of text.

7. Enable Parameter.

   This ensures the variable can store task data and make it available to other task steps.

8. From the Parameter Attributes, select In, Out.

   This allows the variable to both received inputs and product outputs.

9. From the Scope drop-down, select Task Instance.

   This ensures the variable is reset every time the task completes — we do not want to run the task again for the same account reference.

10. Click OK to save the new variable.

Part 3: Building the Task

Now we can start building the task.

Part 3A — Triggering the Task to Run

We want this task to run when an account goes on hold— we use the Microsoft SQL Server Trigger tool to do this. In this exercise, we're going to instruct the Microsoft SQL Server Trigger Agent to monitor for changes to the ACCOUNT_ON_HOLD column in the SALES_LEDGER table of the training database.

1. If you have previously closed the Customer Accounts on Hold task, reopen it and go to the Design tab.
2. Right-click in the design area and select New > Event > Microsoft SQL Server Trigger.
3. Name the step: Trigger when Account Goes On Hold.
4. Add a description: Triggers the task when an account is placed on hold.
5. Go to the Main tab.
6. The Select Server drop-down should only show one Agent. If this installation of BPA Platform had multiple Agents registered, you would need to select the one monitoring the database containing the SALES_LEDGER table.
7. In Login Name and Password, enter the SQL Server user credentials as supplied by your System Administrator — see Part 3 — Creating the Connection to the Database.
8. From the Select a Table / View pane, click Browse.
9. Expand BPAPlatformTrainingDB and select dbo.SALES_LEDGER.
10. Click OK.
11. Go to the Trigger tab.
12. Leave UPDATE as the Trigger Type and leave Fire Trigger as AFTER — This tells the Agent to only trigger the task after the trigger type has been processed for the nominated table, in this case, after a new record has been inserted.
13. Enable Run Task once for each Row in Results Set in and Inserted Table.
14. In the Map Columns to Task Parameters table, click (unmapped) for the AccountRef variable and select Inserted.ACCOUNT_REF. When the task is run, this maps the value of the ACCOUNT_REF column which triggered the task to the AccountRef variable.
15. Enable Only Fire Trigger when a selected Column is updated / inserted and enable ACCOUNT_ON_HOLD from the table below.
16. Click OK to save and close the step.

---

Part 3B — Extracting the Data

Now we want to extract the account details for the account that triggered the task from the BPA Platform training database. You do this with the Database Query (ODBC) tool.

1. If you have previously closed the Customer Accounts on Hold task re-open it.
2. Go to the Design tab.
3. Right-click in the design area and select New > Data Connectors > Database Query (ODBC).
4. Add a name for this step: DBQ - Account on Hold.
5. From the Connection drop-down, choose the Database Query (ODBC) connection created in Part 1B.
6. Go to the Query tab.

   Here, we specify the columns that contain the data we are interested in, and the exact data we want in the report.

   The extracted data forms a BPA Platform recordset which can be used by other task steps to "forward the data" on.

7. Expand the following tables and drag these columns into the Columns pane:

■

Table: SALES_LEDGER; Column: ACCOUNT_REF

■

Table: SALES_LEDGER; Column: BALANCE

■

Table: SALES_LEDGER; Column: NAME

■

Table: SALES_LEDGER; Column: CREDIT_LIMIT

■

Table: SALES_LEDGER; Column: CONTACT_NAME

■

Table: SALES_LEDGER; Column: EMAIL

Click the image to view full size.

8. Now we narrow down the extracted data by requesting only those records for the account that triggered the task:
   1. Drag ACCOUNT_REF from the Columns pane to the Criteria pane.
   2. From the Select a criteria drop-down, choose equals [C].
   3. From the Task Browser, go to the Environment tab and change Scope to Task.
   4. Expand the Task > Variables nodes and drag the AccountRef variable to the ACCOUNT_REF criteria box.

Your query should now look like:

Click the image to view full size.

14. To ensure the query works as intended, click (Execute). You are prompted for an account reference number in lieu of the Microsoft SQL Server Trigger step not populating the variable — enter ASL001 and click OK.

    A single record is returned:

    

15. Click OK to save the Database Query (ODBC) step.

---

Part 3C — Populating the Report

Next, we use the results from the Database Query (ODBC) step to populate a Crystal Report:

1. For this exercise, we have created a template. Download and save it to the server hosting BPA Platform.
2. If you have previously closed the Customer Accounts on Hold task re-open it.
3. Go to the Design tab.
4. Right-click in the design area and select New > Format > Run Crystal Report.
5. Enable Use a recordset and select the previous Database Query (ODBC) step from the Input Recordset drop-down.
6. Go to the Report tab.
7. Click Import. Locate and open the supplied Crystal Report template.
8. When a Crystal Report template is created, you add placeholders for data to be mapped to. The supplied template requires the ACCOUNT_REF column to be mapped. You do this as follows:
   1. From the Mapped Report Parameters pane, click Add.
   2. Ensure Report Parameter is Account Ref — this is determined by the template.
   3. Enable Discrete value.
   4. From the Task Browser, go to the Environment tab and change Scope to be This Step.
   5. Expand RecordSource and drag ACCOUNT_REF to Value 1.

      

      Click the image to view full size.

   6. Click OK to save the mapping.
9. Now, we must provide the SQL Server credentials so the Run Crystal Report step can access the data correctly:
   1. From the Database Tables Logon Information pane, highlight SALES_LEDGER.
   2. Click Edit.
   3. Typically, you should create a separate user solely for this purpose so that access to important tables is restricted. For this exercise, we'll just use credentials (Username and Password) set up for SQL Server.
10. Click OK to save the Run Crystal Report step.

---

Part 3D — Emailing the Report

The final step of the task is to email the populated report to the customer. We use the Send Email (SMTP) tool to do this.

1. If you have previously closed the Customer Accounts on Hold task re-open it.
2. Go to the Design tab.
3. Right-click in the design area and select New > Output > Send Email (SMTP).
4. Name this step: Email Report to Customer.
5. Enable Read documents from a source.
6. From the Document Source drop-down, select Reports from Run Crystal Report (HTML 4.0).
7. Go to the Connection tab and select the SMTP server connection created in Part 1C — Creating the Connection to the SMTP Server.
8. Go to the Main tab.
9. Click From and add a valid sender email address.
10. Click To then New.
11. From the Task Browser, go to the Environment tab and change Scope to be This Step.
12. Expand the Send Email (SMTP) > DocumentSource > Recordset nodes.
13. Drag CONTACT_NAME to the Name field and E_MAIL to Address:

Click the image to view full size.

14. Click OK to return to the main Send Email (SMTP) interface.
15. Add a meaningful Subject for this email.
16. Ensure Document Output in the Message Contents pane is enabled. This sends the output from the Run Crystal Report step embedded as the email body rather than as an attachment.
17. Click OK to save the Send Email (SMTP) step.

---

Part 3E — Linking the Steps

To ensure BPA Platform runs the steps in the correct order, the individual task steps must be linked. You do this by clicking a step to display the step controls — click and hold and draw the connector to its successor.

If required, you can add a label to the connection. Double-click the connection line and add the label, up to a maximum of 100 characters. Note that this is a pure-text field so variables, formulas, functions, and Task Browser properties cannot be used in this area.

Connect the steps as follows:

Part 4: Testing the Task

You'll need to manipulate the data in the training database in order for the task to automatically trigger and email a valid address:

►

For a record in the SALES_LEDGER table, adjust the value in the EMAIL column to a valid email address recognised by the SMTP server setup in Part 1C — Creating the Connection to the SMTP Server.

►

For the same record, change the value in ACCOUNT_ON_HOLD from 0 to 1 — the Microsoft SQL Server Trigger step will detect the update and trigger the task to run. Ensure a valid and complete email is received.