Toad Data Point Automation Series: #4 – Excel Pivot Grids

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.

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’.

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.

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.