Toad for SQL Server 8.1 - Installation Guide

Automate Toad Pivot Grids

Summary

This tutorial demonstrates how to create a Toad Pivot Grid file and how to use Automation to refresh the pivot grid and generate a report.

In this tutorial you will learn:

• How to create a Toad Pivot Grid file
• How to use the Pivot Grid activity in Automation

You will need:

• A sample database
• A query to use for creating a pivot grid

Introduction

A pivot grid is a great way to display a summary of your data. Pivot grids are also great to use as reports. You can create a pivot grid in Toad and save it (with the query) as a Toad Pivot Grid file. You can also export the pivot grid to an Excel pivot table or an Excel grid, among other options.

In this tutorial you will create a pivot grid in Toad and then build an Automation script to refresh the pivot grid and generate an Excel report. The query provided in this tutorial is designed to be used with the Toad Sample Database (available with Toad Data Point). To follow along with a different sample database, use a query in which the result set can be summarized in a pivot grid.

Create a Toad Pivot Grid File

1. To get started, connect to your sample database.
2. Select Tools | Editor to open a SQL Editor window. Or select Tools | Query Builder to use the Query Builder.

3. Enter your query in the Editor or Query Builder and execute it. To follow along using the Toad Sample Database, enter the following query:

   SELECT ORDERS.ORDER_DATE, ORDERS.ORDER_ID, ORDER_ITEM.QUANTITY, ITEM.RETAIL_PRICE, CONTACT.FIRST_NAME, CONTACT.LAST_NAME, REGION.REGION_NAME

   FROM ORDERS ORDERS, ORDER_ITEM ORDER_ITEM, ITEM ITEM, CONTACT CONTACT, REGION REGION, ADDRESS ADDRESS

   WHERE ORDERS.ORDER_ID = ORDER_ITEM.ORDER_ID

   AND ORDER_ITEM.ITEM_ID = ITEM.ITEM_ID

   AND ORDERS.CONTACT_ID = CONTACT.CONTACT_ID

   AND CONTACT.ADDRESS_ID = ADDRESS.ADDRESS_ID

   AND ADDRESS.REGION_ID = REGION.REGION_ID

4. After the query executes, select the Pivot & Chart tab of the Results pane. Let’s build a quick pivot grid to preview the data before sending it to the Pivot Grid tool.

   For more information about how to send data to the Pivot Grid tool or the Pivot & Chart tab, see Pivot and Chart Data.

5. Build the preview pivot grid by dragging fields from the Pivot Grid field list to the grid or to the Areas below the field list.

   1. Drag ORDER_DATE from the field list to the Column Area.
   2. Now drag REGION_NAME to the Row Area.
   3. Finally, drag QUANTITY to the Data Area.

   Your pivot grid should look similar to the following screen shot. To display the bar graph, select Bar from the Type drop-down list. Click and clear Chart Selection Only to display all data.

   

   The Pivot & Chart tab of the Editor allows you to preview your data while you are working on a query. To save the pivot grid, you must send it to the Pivot Grid tool. This allows you to save the query and pivot layout as a Toad Pivot Grid file which can be used in an Automation script.

6. When you are satisfied that your query and result set produce the kind of pivoted data you want, right-click the pivot grid and select Move to Document.

   When Toad prompts you to save the data with the query, select Yes. The query, data, and pivot grid layout are sent to the Pivot Grid tool which opens in a new window.

   Tip: To start with a clean pivot grid, send only the query to the Pivot Grid tool. To do this, right-click the grid in the Result Sets tab and select Send To | Pivot Grid.

7. The Pivot Grid window is similar to the Pivot & Chart tab. By default, the right pane contains a field list and a group of pivot grid destinations. The layout of this pane is similar to Excel and you can customize it by clicking .
8. You can edit the query at any time by clicking Edit Query in the upper-right corner. Use this dialog to modify the query or preview data.
9. Let's start working with this pivot grid. Let’s add a calculated column.

   1. Click in the Pivot Grid toolbar.

      

   2. In the Calculations dialog, enter "Total_Price" in the name field. Select Fields in the lower-left pane to display a list of columns in the right pane. Double-click QUANTITY to add it to the Formula field. Then click the X operator. Then double-click RETAIL_PRICE to create the formula “QUANTITY * RETAIL_PRICE.”
   3. The Calculations dialog provides a very useful feature that allows you to save an expression (formula) for reuse in other calculated columns. To use this feature, click Add As Saved Expression. The expression is added to the Saved Expressions list.
   4. Click OK to save your work and close the dialog.
10. Remove the QUANTITY field from the Data Area by dragging it back to the fields list. Now drag the Total_Price calculated field you just created to the Data Area. The result is a pivot grid showing total sales per region for each month.

11. Let's make sure the sales values display as currency. Right-click the data area in the pivot grid and select Value Field Settings. In the dialog click Number Format. Select Currency and click OK.

    

12. One very convenient feature of the pivot grid is the way it groups dates. To demonstrate this, right-click the ORDER_DATE field in the Column Area and select Group By. From the list, select Year. Then add the date column a second time and select Group By | Quarter.

    

    To make your report more meaningful, customize the field names. Right-click the second ORDER_DATE field and select Field Settings. Change the name to "ORDER_DATE (Quarter)." Repeat this process with the first field and change the name to "ORDER_DATE (Year)."

    Review your pivot grid and notice how columns are now grouped by year and sub-grouped by quarter, as in the following example.

    

    Tip: You can rename fields in the Data Area also. Right-click a data field or value and select Value Field Settings.

13. Filtering the Data. You may want to filter the data displayed in the pivot grid. You can filter by the field values in any field in your result set.

    1. Place your cursor over the REGION_NAME field (Row Area) and click . Select only Central and Southern. Click OK. Only data for the Central and Southern regions is displayed. The filter icon now displays in the field name to indicate that a filter is applied.
    2. Click again and click the radio button and then select a region. The radio button allows you to ensure that only one value is selected at a time.
    3. Click , click the radio button again to deselect it, and then select Show All.
    4. Now remove the REGION_NAME field from the Row Area by dragging it back to the field list. Drag the LAST_NAME field to the Row Area. Your pivot now displays total price per customer for each quarter.
    5. You can also filter by a field not displayed in the pivoted data. Drag the REGION_NAME field to the Filter Area (upper-left corner of pivot grid). Click in the REGION_NAME field and select only the West region. The grid now displays data for only those customers located in the West.

    To create a more complex filter based on more than one field, you can use a pre-filter. To do this, right-click the pivot grid and select Show Prefilter. After it is created, the pre-filter is displayed along the bottom of the pivot grid.

14. Inspecting the Data. In the pivot grid, locate the value (cell) for the customer named Austin in 2009, quarter 1. Double-click the value. This action drills down to the data behind the value and in this case reveals multiple first names. So, drag the FIRST_NAME field from the field list to the Row Area, making sure to place it below the LAST_NAME field (or to the right of it in the pivot grid). Field order determines grid layout.

    

    Tip: Right-click the pivot grid and select Show Column Totals to add a Grand Total column. Right-click the Grand Total column header to rename it.

15. A very useful feature of the pivot grid is the ability to add a duplicate field with a different aggregate type allowing you to display several different aggregates in one grid.
    1. If you have been following along, your grid Data Area contains the Total_Price field with the Sum aggregate type (which is the default) applied. Drag another Total_Price field to the Data Area.

    2. Right-click the duplicate field and select Value Field Settings. In the Summarize Values By tab, select Count (deselect Sum). In the Custom Name field, enter “Number of Line Items” to distinguish this aggregate.

       

    3. Now add the QUANTITY field to the Data Area. Right-click this field and select Value Field Settings. Select Sum as the aggregate type. Change the name to “Total Items.” In the Show Values As tab, select No Calculation.

    4. Add the QUANTITY field again using the same value field settings, except in the Show Values As tab, select % of Column Grand Total. Name this field "Total Items %."

       

    5. The duplicate QUANTITY fields are used to demonstrate how duplicate fields allow you to view the same value, but using a different measurement. Remove the QUANTITY fields by dragging them back to the field list.
16. Let's add some conditional formatting.
    1. Right-click a data value in the pivot grid and select Format Rules | Manage Rules.
    2. Click New Rule. Select the Format only top or bottom ranked values rule type. Then select Top and enter "25." Select % of column values.
    3. Click Format. Select the Fill tab and choose a light green fill. Click OK.

    With conditional formatting, your pivot grid might look something like this.

    

17. Now let's review the chart, a great way to visualize your pivoted data. Click to show the chart pane if it is not displayed.
    1. In the Type field, browse different chart types and then select one.

    2. Click the arrow beside and select Chart Selection Only. Now select some cells in the pivot grid (use CTRL+Click to select multiple areas).

       The chart displays only the data in the selected cells. This is a great way to visualize a subset of data from the pivot grid.

18. Before creating the Automation script, let's test the Excel output. You can export the pivot grid to a few different formats, including an Excel pivot table and an Excel grid. Note the following differences:

    • Excel pivot table—In this format, the exported Excel file contains an Excel pivot table, as well as a sheet containing the underlying result set.
    • Excel grid—In this format, the exported Excel file contains the summarized data as a data set. Only the data in the pivot grid is exported. This format presents full rows of data wherever the pivot grid grouped data into merged cells.

    For this tutorial, let's export to an Excel pivot table.

    1. Click and select Excel Pivot. In the Export dialog, select a file name and location. To learn more about the options available in the Export dialog, see Specify Excel Export Options. Click OK to begin exporting.
    2. Open the exported Excel file when prompted. Review the pivot table. The second sheet contains the underlying data. At this point, you can return to the Toad pivot grid window and modify the pivot grid or the query and re-export until you are satisfied with the results.

19. After you have configured the Toad pivot grid to your satisfaction, save the file as a Toad Pivot Grid file (.tpg). You will use this file in your Automation script.

    When Toad prompts you to save the data with the query, select Yes to save the data. When you open the file again, you must manually refresh the query to access the latest data. To force a refresh upon opening the file, select No.

Create Script

1. Select Tools | Automation to open a new Automation window.
2. Click the Toad Pivot Grid activity to add it to the Automation design window.
3. In the Activity Input tab, click in the Pivot file field and browse to and select the Toad Pivot Grid file you just created.

4. In the Export to File section, for this tutorial select Excel Pivot as the export type. This option allows you to export both the pivot grid and the underlying data to an Excel pivot table file. The exported file includes an Excel pivot table and the result set. Then enter a file name and location for your export file.

   Click Export options to customize the Excel file. See Specify Excel Export Options for more information about selecting Excel file options.

   Tip: You can export to other destinations and file types. See Use Database Automation Activities for more information about other actions available with the Toad Pivot Grid Automation activity.

5. Click to save and run your script.

   The script opens the existing Toad Pivot Grid file, executes the query, and exports the data to the Excel pivot table file.

6. After the script executes, review the Log. Click the hyperlink to view the Excel output file.

Use Toad Pivot Grid files as a convenient way to keep your SQL query with your pivot grid. And then use Automation to automatically refresh your pivot grid and generate a report.

Schedule A Script

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

Related Topics

Getting Started with Automation

Pivot and Chart Data