Automate Toad Data Reports

Summary

In this tutorial you will use Automation to execute an existing Toad Data Report file and publish a customized PDF report containing query results.

In this tutorial you will learn:

How to use the Toad Report activity in Automation
How to create a Toad Data Report
How to merge multiple Toad Data Reports into one output file

You will need:

A sample database
A query or query file

Introduction

Toad Data Reports (.tdr) are Toad files that you can use to design and publish attractive reports for your query results. Toad Data Reports are created using the Data Report Wizard and are modified using the Data Report Designer. A Toad Data Report includes a query, a result set or sets, and a customized report layout that you create. The query executes when the Toad Data Report is opened. You can publish an output file (report) from the Toad Data Report file at any time.

With Automation you can create a script that opens a Toad Data Report (executing the query) and publishes an output file (report). One big advantage to using an Automation script for this task is that you can open multiple Toad Data Reports and merge them into one output file (report).

In this tutorial, you create a Toad Data Report file (.tdr) and then build a script that executes the Toad Data Report and publishes an attractive output file (report).

Create Toad Data Report

Before you can build a script, you must have an existing Toad Data Report. The input for a Data Report is a result set or a query. You can start building a Data Report from one of the following:

A result set—Right-click the data grid of a result set and select Sent To | Data Report Designer to send the result set directly to the Data Report Wizard.
A query—To start with a query, select Tools | Reports | Data Reports to open the Data Report Wizard.

For this tutorial, start with a query.

Connect to your sample database (or the database against which you want your Data Report to query).
Select Tools | Reports | Data Reports to open the Toad Data Report Wizard.
On the Welcome page, select Standard Report.
On the next page, in the Table 1 editor box, enter a query (or select a query file). Then, click the Execute statement button to preview the query results. You can also click the Add query button to add an additional query to your report, if necessary.

When you finish, click Next.
On the next page, select the columns you want to include in your report. Click Next.
On the next pages, configure grouping, select a layout, and select a report style.

Note: If you previously created a Data Report template, you can select that template on the styles page.
On the last page, enter a title for your report and click Finish.

Your report opens in the Data Report Designer where you can further customize your Data Report. See About Designing Toad Data Reports for more information.
When you finish customizing your Toad Data Report, save it. You will use this file in your Automation script.

Notes:
- Before closing the Data Report window, if you would like to publish an output file from your Toad Data Report now, click the Preview button. In the Preview toolbar, click the arrow beside the Export Document button and select an output format.
- To save your Data Report layout as a template, select Format | Save Template.
- To modify an existing Toad Data Report, open the report (.tdr) and click to open the Data Report Designer.

Create Script

Select Tools | Automation to open a new Automation window.
Click the Toad Report activity to add it to the Automation design window.
In the Activity Input tab, select the database connection you used when you created the Toad Data Report in the wizard.

Note: You can select another database. When the script executes, it uses the query contained in theToad Data Report, and the database connection you specify in the Data Report activity.
Click Add Reports and browse to and select the Toad Data Report that you just created.

Note: Click Edit Report to modify the selected Toad Data Report, if necessary.
Click in the Export file field to select a location, file name, and file type for your output file. You can select from a number of different formats, including PDF and HTML.
Click to save and run your script.
After the script executes, click the output file hyperlink in the Log to view the output file.

Use Multiple Toad Data Reports

You can instruct the script to run multiple Toad Data Reports and then publish the results into one output file. The Data Reports must be of the same database type or contain a cross-connection query.

Note: If a Toad Data Report contains a cross-connection query, the report's cross-database connection is used instead of the connection associated with the activity.

To follow along with this tutorial, you will need multiple Toad Data Report files.

Return to your Automation script.
Select the Activity Input tab of the Toad Report activity.
Click Add Reports and browse to and select another Toad Data Report, in addition to the one you previously added. You should now have at least two Data Report files listed in Report files.
Click to save and run your script.
After the script executes, click the output file hyperlink in the Log to view the output file. Notice that the script executed each Toad Data Report and added the results to the output file in the same order the reports appear in the Report files list.

Use Toad Data Reports to build attractive, customized reports for publishing query results. Then use Automation to periodically generate, publish, and distribute those reports to end-users.

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.