Use Toad pivot grids to reorganize and summarize data to create meaningful reports. You can sort and aggregate data independent of the original data layout. This is useful for creating financial and statistical reports. For example, a bookstore owner can analyze sales by time periods (quarterly, monthly, etc.) for each customer or each genre, or can identify the top 10 customers or authors.
See Automate Toad Pivot Grids for a tutorial of this feature.
This topic covers the following pivot grid tasks:
There are two ways you can pivot and chart data in Toad:
Caution: Adding a large number of columns or rows to a pivot grid can adversely affect Toad's performance because of the computations required to generate the pivot grid.
To pivot and chart data
Execute a statement in the Editor or Query Builder to generate results. Then select the Pivot & Chart tab in the Results pane.
Tip: To save a pivot grid built in the Pivot & Chart tab, send it to the Pivot Grid tool. Right-click the pivot grid and select Move to Document. The pivot grid opens in a new window with the layout preserved.
To build the pivot grid, drag a field from the field list to a destination in the pivot grid or to one of the Areas below the field list. Review the following to understand how each Area controls how data is displayed.
Area | Impact on Pivot Table | Impact on Chart |
---|---|---|
Column Area |
Displays columns across the top of the grid for each field value. Consider the following when adding fields to the column area:
|
Displays columns along the X axis. You can limit which data values display by clicking |
Row Area |
Displays rows down the left side of the grid for each field value. Consider the following when adding fields to the row area:
|
Displays rows in the legend. |
Data Area |
Displays summary values for each column/row. The data in the Data Area summarizes the underlying source data. To change the way values are summarized, right-click the grid and select Summarize Values By. To add grand totals, right-click the grid and select Show Row Totals or Show Column Totals. |
The summary values are displayed in the chart. To change the chart format, select from the Type drop-down list. |
Filter Area |
You can use the Filter Area to limit the data in the pivot grid. Drag a field to the Filter Area. Then click |
You can use the Filter Area to limit the data in the pivot grid. Drag a field to the Filter Area. Then click |
Change Summary Type. You can change the way values are summarized (or aggregated).
For column or row group subtotals, right-click the subtotal field name and select Field Settings. Then select a different summary function under Subtotals.
Group subtotals automatically display when you add multiple fields to a Row or Column Area.
Add Grand Totals. To add row or column grand totals, right-click the pivot grid and select Show Row Totals or Show Column Totals.
You can rename a total by right-clicking Grand Total and selecting Rename Grand Total.
Create field filter. To limit which values display for a field, click beside the field name and select the values to include. You can do this in the pivot grid or in the Areas below the field list. (Place your cursor over a field name to display the filter icon.)
For fields in the Data Area, the filter dialog displays a histogram to easily visualize your data values. Sliders provide a convenient way to select data for filtering.
When filtering on fields used outside the Data Area, click the radio button in the filter dialog to limit the filter to only one value at a time.
Create a prefilter. You can add a prefilter to your pivot grid to filter the data available to pivot.
Click Apply to preview the filter. Click OK to save the filter. The filter text displays in a box below the pivot grid. Click Edit Prefilter to modify the filter.
Caution: If you click the Close button (X) in the filter text box, the filter is deleted.
For detailed instructions that apply to this filter editor, see Filter Data in Transform and Cleanse.
Default - An unbound expression is calculated against each data source record, and then the resulting values are summarized
Summary - An unbound expression is calculated against summary values
Aggregate - An unbound expression is calculated using aggregates
Toolbar. (Pivot grid document) Use the Pivot Grid toolbar to export the pivot grid, export the data, or
Refresh the pivot grid | |
Click the arrow to select from different export actions. This button retains the last action selected. See Export Data for more information | |
Click the arrow to select the publishing action.
| |
Save pivoted data to a new or existing table in Local Storage. See Save Data to Local Storage. | |
Print the pivot grid and chart. To export as a document, select File | Export Document in the Preview window | |
Show/hide chart | |
Create a calculated field and add it to the field list | |
Set Pivot Grid properties:
| |
Rearrange pivot data |
Wizard bar. (Pivot grid document) Use the Wizard bar to export or publish pivoted data or publish the Toad document.
Refresh the pivot grid from source data | |
Export to Excel or other file format. The export action initiated is determined by the currently-selected action in the Export Pivot menu | |
Save pivoted data to a new or existing table in Local Storage. See Save Data to Local Storage. | |
Save your work as a Toad Pivot Grid file (.tpg) and add it to the currently-open project in the Project Manager. See Project Manager. | |
Publish the Toad Pivot Grid file to Intelligence Central | |
Publish the pivoted data as a dataset to Intelligence Central. See Publish to Toad Intelligence Central. |
To refresh the pivot grid
To change the connection
In a pivot grid document, right-click the document tab and select Change Connection.
Note: When the underlying query is a cross-connection query, this feature is not enabled.
To drill down to data details
You can specify how values are displayed in the pivot grid. You can change the way values are summarized (aggregated), specify a format for numbers, and display values as percentages. See Specify Value and Field Settings for more information.
You can create and apply conditional formatting rules to format pivot grid cells and cell contents.
The conditional formatting you create in the pivot grid is included when you export to an Excel pivot table (Excel Pivot).
(Pivot grid document) You can edit the underlying query and apply the changes to the pivot grid.
(Pivot grid document) To export the pivot grid, click the arrow beside and select one of the following export options.
Excel Pivot—To export the pivot grid and the result set to an interactive Excel pivot table, select Excel Pivot from the Export Pivot drop-down menu. The exported file includes a pivot table as well as a sheet containing the underlying result set. See Specify Excel Export Options for more information about export options for the Excel file.
Review Considerations and Limitations when Working with Pivot Grids before exporting to Excel.
Excel Grid—To export the summarized data to Excel as a data set, select Excel Grid from the Export Pivot drop-down menu. Only the data in the pivot grid is exported. When merged cells exist in the pivot grid, the exported file includes full rows of data wherever the pivot grid grouped data into merged cells.
See Specify Excel Export Options for more information about export options for the Excel file.
(Pivot grid document) Use these methods to create or print a simple report that includes the pivot grid (pivoted data only) and the chart.
To print a pivot grid and chart
To include the chart, click Yes when prompted.
Note: If the Include chart prompt does not display, go to Tools | Options | Environment | Confirmations and click Clear Ignored Confirmations to reset the prompt.
To export as PDF or other document
You can automate the task of refreshing a Toad Pivot Grid document and then exporting the results to Excel
To troubleshoot Toad Pivot Grid files, see Considerations and Limitations when Working with Pivot Grids.
Tip: To send data from a result set directly to an Excel pivot table, right-click the data grid and select Quick Export | Excel Instance Pivot Table. Export Data with One Click Export
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center