Use Variables in Automation

To generate reports per product

1. Use the Query Builder to create a query that returns the product IDs. See Build Queries Visually for more information.
2. Save the query and name it ProductIDQuery.tsm.
3. Use the Query Builder to create a query that returns the sales information for products and enter the following search criteria in the specified columns:

Column	Search Criteria
Product_ID	Click in the Where Condition field. Select = and then Constant. Enter :ID in the field to define the variable.
Order_Date	Click in the Where Condition field. Select the Date Range tab and then Last week.

4. Save the query and name it ProductSalesQuery.tsm.
5. Click Run Query and enter a value for the ID variable. The query results display.
6. Right-click the query results and select Send To | Data Report Designer.
7. Create the data report. See About Designing Toad Data Reports for more information.
8. Save the report and name it ProductSalesPerWeek.tdr.
9. Close the report.
10. Select Tools | Automation to create a new automation script.
11. Click the Execute Script activity to add it to the Design pane and complete the following fields:

SQL file	Select ProductIDQuery.tsm.
Variable	Enter ProductIDTable.

12. Click the Set Variable activity and complete the following fields:

Name	Enter ID.
Variable value	Enter 0. Entering a number initializes the variable, which is required before you can redefine the value to another variable.

13. Click the Loop dataset activity and enter ProductIDTable in the Data Table field. This loops activities on the content of the ProductIDQuery.tsm query.
14. Click the Set Variable activity to add a second variable and drag it into the Loop row.
15. Complete the following fields for the new Set Variable activity:

Name	Enter ID.
Variable value	Enter ProductIDTable.Product_ID. This uses the content of the Product_ID column in the ProductIDTable table as the values for the variable.

16. Click the Database Connection activity and drag it into the Loop row beneath the Set Variable activity.
17. Click the Toad Report activity and drag it into the new Database Connection activity.
18. Complete the following fields in the Toad Report activity:

Report files	Select ProductSalesPerWeek.tdr.
Export files	Select the file location and enter SalesForProduct#ID# for the file name. When the automation script runs, it replaces #ID# with the variable value. Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).

19. Click the Send Email activity and drag it beneath the Database Connection activity in the Loop row.
20. Enter the following information and then complete the remaining required fields for the Send Email activity:

To	Enter the Sales Manager's email address.
Subject	Enter Weekly sales report for product #ID#. When the automation script runs, it replaces #ID# with the variable value.
Body	Enter the following: Attached is the weekly sales report for product #ID#. See the attached file.
Add Attachments	Select the file created in the Toad Report activity and click Add.

21. Click Test to ensure that the script works. Click here to see the automation script in the Design pane.

    

    If ProductIDQuery.tsm returns five product IDs, Toad creates a report and sends an email for each one. If the first product ID is 101, the first email would have the following properties:

    Attachments	SalesForProduct101.xls
    Subject	Weekly sales report for product 101
    Body	Attached is the weekly sales report for product 101. See the attached file.

22. Save the automation script.
23. Schedule the automation script to run weekly. See Execute Automation Scripts for more information.

To generate a daily report of unbatched orders

1. Use the Query Builder to create a query that returns unbatched orders for the day. See Build Queries Visually for more information.
2. Save the query and name it UnbatchedOrders.tsm.
3. Select Tools | Automation to create a new automation script.
4. Click the Execute Script activity to add it to the Design pane and complete the following fields:

Row count variable	Enter rc.
SQL file	Select UnbatchedOrders.tsm.

5. Click the If..Condition.. activity.
6. Complete the following steps to define the first condition:
   1. Select the first condition and enter rc=0 in the Build condition expression field.
   2. Click the Send Email activity and drag it into the first condition.
   3. Complete the required Send Email activity fields and enter No unbatched orders in the Subject field.
7. Complete the following steps to define the second condition:
   1. Select the second condition and enter rc>0 in the Build condition expression field.
   2. Click the Database Connection activity and drag it into the second condition.
   3. Click the Select to File activity and drag it into the Database Connection activity in the second condition.
   4. Complete the following Select to File activity fields:

   SQL file

   Select UnbatchedOrders.tsm.

   Export file

   Click the drop-down and complete the following: Excel file—Select this option, and then select the file location and enter UnbatchedOrders_ for the file name. Use column names for header row—Select this option and select a header row color. Add Date/Time suffix to filename—Select this option and then select Date in the drop-down. This option appends the date the automation script ran to the file name.

   5. Click the Send Email activity and drag it beneath the Select to File activity.
   6. Enter the following information and then complete the remaining required fields for the Send Email activity:

   Attachments	Select the file created in the Select to File activity and click Add.
   Subject	Enter #rc# unbatched order(s). When the automation script runs, it replaces #rc# with the variable value.
   Body	Enter the following: There were #rc# unbatched orders. See the attached file for more information.

8. Click Test to ensure that the script works. Click here to see the automation script in the Design pane.

   

   If there are no unbatched orders, Toad executes the first condition and sends an email with "No unbatched orders" in the subject . If there are unbatched orders, Toad executes the second condition. If there are two unbatched orders and you test on December 10, 2009, the email would have the following properties:

   Attachments	UnbatchedOrders_2009-12-10.xls
   Subject	2 unbatched orders
   Body	There were 2 unbatched orders. See the attached file for more information.

9. Save the automation script.
10. Schedule the automation script to run daily. See Execute Automation Scripts for more information.