Toad for SQL Server 8.1 - Installation Guide

Pivot and Chart Data

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:

• Pivot and Chart Data
• Specify Value and Field Settings
• Define Conditional Formatting
• Edit Underlying Query
• Export Data

There are two ways you can pivot and chart data in Toad:

• Use the Pivot & Chart tab in the SQL Editor or Query Builder. This allows you to preview pivoted data as you work on a query.
• Send data to the Pivot Grid tool from a data grid or the Object Explorer. Use this method when you want to save the pivot grid.

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

1. Use one of the following methods 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.

   • Right-click data in a data grid and select Sent To | Pivot Grid.
   • Right-click an object in the Object Explorer and select Pivot Grid.

2. 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: If you add multiple fields to the area, group subtotals display. For multiple fields, field order determines grid layout. You can limit which data values display by clicking beside the field name.	Displays columns along the X axis. You can limit which data values display by clicking beside the field name.
   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: If you add multiple fields to the area, group subtotals display. For multiple fields, field order determines grid layout. You can limit which data values display by clicking beside the field name. You can display only a selected number of top rows. Right-click the header of the first column, select Top N and set the desired value. Only the top n rows in Data Area will be displayed.	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 to select which values to include in the grid. Filter fields do not display in the pivot grid.	You can use the Filter Area to limit the data in the pivot grid. Drag a field to the Filter Area. Then click to select which values to include in the grid. Filter fields do not display in the pivot grid.

3. Defer Layout Update—Select this option to move fields to Areas without applying the changes to the pivot grid immediately. Then click Update at any time to apply the changes to the pivot grid. Clear this option to allow Toad to automatically apply changes as you build the grid.

4. Change Summary Type. You can change the way values are summarized (or aggregated).

   • For the Data Area, right-click a field value or field name and select Summarize Values By. Then select a summary function applicable to that field.

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

5. Group columns/rows. To group columns or rows in the pivot grid, right-click a column or row header and select Group By.
   • For Date columns—One very convenient feature of the pivot grid is the way it groups dates. To use this feature, add a date field to the Column Area. Right-click the date field and select Group By and then select a date component, such as Quarter. Add the field again and select another component, such as Month. Columns are now grouped by quarter and sub-grouped by month.

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

7. Rename a field. To rename a field, right-click the field and select Field Settings or Value Field Settings and enter a custom name.
8. Remove a field. To remove a field from the pivot grid, right-click the field name in the Area below the field list and select Remove Field or drag the field back to the field list.

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

10. Create a prefilter. You can add a prefilter to your pivot grid to filter the data available to pivot.

    1. Right-click the pivot grid and select Show Prefilter.
    2. In the Prefilter editor, click to add a condition. Build the first filter condition by selecting a column, an operator, and a value.
    3. To add a second condition, click .
    4. To use a different Boolean operation to join the two conditions, click And and select a different operator.

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

11. Create a Calculated Field. Click to create a calculated field. Once created, the new field is added to the field list.
    1. In the expressions editor, enter a name and build an expression to be used to calculate the field.
    2. Select calculation mode:

       • 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

    3. Select items in the left pane to display options in the right pane. Double-click an option or an operator to add it to the editor.
    4. To save your expression for reuse in other calculated fields, click Add As Saved Expression. The expression is added to the Saved Expressions list.
    5. To edit the calculated field, right-click the field name and select Edit Expression.
    6. To delete a calculated field, click to open the expression editor. Select the field and click Delete Calculation.
12. Modify chart. To change the type of chart, select an option in the Type field in the chart pane.
    • Click to specify chart options.
    • To chart partial data, use CTRL+Click to select sections of the pivot grid. Then click and select Chart Selection Only.
13. Save your work—Pivot & Chart tab
    • In the SQL Editor, be sure to select Toad Editor File (.tef) in the Save As dialog to ensure the pivot grid is saved along with your query.
    • In the Query Builder, you can save your work as a Toad Query Builder file (.tsm).
14. Save your work—Pivot Grid document
    • In the Pivot Grid tool, select File | Save File to save your work as a Toad Pivot Grid document (.tpg). When Toad prompts you to save the data with the Query, select one of the following:
      • Yes—Saves the data with the query. When you open the file again, you must manually refresh the query (F5) to access the latest data.
      • No—The query is executed the next time the file is opened.

15. Toolbar. (Pivot grid document) Use the Pivot Grid toolbar to export the pivot grid, export the data, or the file to Intelligence Central.

    	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
    	Publish the Toad Pivot Grid file to Intelligence Central
    	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: Show totals for single nesting values. Default is False Show subtotals for single nesting values. Default is False

To refresh the pivot grid

• In a pivot grid document, press F5 to refresh the pivot grid and reflect the current data from the source database.
  • If you saved the data when you saved the document, press F5 after opening a saved Toad Pivot Grid document to refresh the pivot grid data.
  • If you did not save the data with the document, the query is executed when you open a saved Toad Pivot Grid document.

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

• Double-click a value in the pivot grid to drill down and view details of the underlying data.

Specify Value and Field Settings

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.

Define Conditional Formatting

You can create and apply conditional formatting rules to format pivot grid cells and cell contents.

• To create conditional formatting, right-click a pivot grid cell and select Format Rules. See Define Conditional Formatting for more information about the available options.

The conditional formatting you create in the pivot grid is included when you export to an Excel pivot table (Excel Pivot).

Edit Underlying Query

(Pivot grid document) You can edit the underlying query and apply the changes to the pivot grid.

1. Click Edit Query. Modify the query in the editor pane. To preview the data, click .
2. Click OK to save your changes and execute the query. Your changes are automatically applied to the pivot grid.
3. If changes were not applied to the field list, press F5 to refresh.

Export Data

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

• CSV File— To export the pivot grid to a .csv file, select CSV File from the Export Pivot drop-down menu. Only the data in the pivot grid is exported. See Specify Excel Export Options for more information about export options.
• HTML File—To export the pivot grid as an HTML file, select HTML File from the Export Pivot drop-down menu. Only the data in the pivot grid is exported. To include the chart, use File | Print.
• PDF File— To export the pivot grid as a PDF file, select PDF File from the Export Pivot drop-down menu. Only the data in the pivot grid is exported. To include the chart, use File | Print.

Print or Export Document with Chart

(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

1. Select File | Print or click .

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

3. In the Print Preview window, customize your document.
4. Select File | Print.

To export as PDF or other document

1. To create a document, such as PDF, Text file, RTF, or HTML, select File | Print.
2. To include the chart, click Yes when prompted.
3. In the Preview window, select File | Export Document. Select a file format from the list.

Automate Pivot and Chart

You can automate the task of refreshing a Toad Pivot Grid document and then exporting the results to Excel. To do this, create an Automation script containing a Pivot Grid activity. See the Toad Pivot Grid activity description in Use Database Automation Activities for more information.

Troubleshooting Pivot Grids

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. See Export Data with One Click Export for more information.

Related Topics

Specify Value and Field Settings

Define Conditional Formatting