Feb 8, 2018 11:24:00 AM by DaleB
This tutorial applies to Toad Data Point 4.3 or later.
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:
You will need:
Watch these videos for more information on how to use the Toad Pivot Grid:
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.
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,
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
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.
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.
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.
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.
Click in the Pivot Grid toolbar.
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.
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.
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.
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.
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.
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.
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 %."
With conditional formatting, your pivot grid might look something like this.
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.
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:
For this tutorial, let's export to an Excel pivot table.
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.
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.
Click Run 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.
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.
See Schedule Your Script to learn how to schedule the Automation script.
How to get the most out of Toad Data Point
Toad Data Point, is a powerful tool that will help you access and prepare data for faster business insights. Toad Data Point enables business or data analysts to seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data sources with near-zero transition times. Users can connect, query and prepare data for faster business insights.
Webinar series: Data Preparation Made Easy: Toad Data Point Webcast Series
Video: Top 5 reasons to buy Toad Data Point Professional Edition, a solution for simplifying data access, integration, and provisioning.
Case study #1: Dell: Enterprise financial group solves data prep challenge.
Case study #2: Opening doors and creating opportunities with data insights.
If you have any questions, please post questions to the Toad Data Point forum on Toad World.
Written by DaleB