Here is a report that uses pivot grids.

For this report I export the data to a specific area on another worksheet. I define the pivot grid to take the data from that area.

Exporting Data

In this use case you can configure Toad to clear the data first before inserting new data. You do need to create an excel pivot grid and use this as a template. But you do not need to make a copy in your automation script. But do keep a backup copy just in case. In the automation designer use the Select to File activity. In the options choose to clear the data and then export to the named worksheet ‘data’.

Configuring Toad

You will need to set Excel to refresh the grid upon open. There is a setting in every version of Excel. Graphs can be done in the same manner.

Pivot Table Options

So now our script will select data and export to a named worksheet in our Excel Template. The template contains a predefined pivot grid. We set the option to refresh the pivot grid when the user opens the excel file. Pivot grids for very useful for summarizing data and can be automated in Toad for Data Point.

About the Author

Debbie Peabody

Debbie Peabody is a Software Development Team Lead. She joined Quest in 1997 coding for Quest’s SQL Tuning product SQLab. She brings many years of experience of development on database tool products and co-designed Toad Data Point.

Start the discussion at