Toad Data Point 6.1.2

Chat now with support

Create Amazon Redshift Connections Create ODBC Connections Create Oracle Connections Create Snowflake Connections Create SQL Server Connections Create SharePoint Connections Understand Toad Connections Troubleshoot SQL Server Connections

Understand

Determine the Best Method to Locate Objects Object Explorer Filter Databases and Objects Understand a Table Using the Database Explorer Search for Objects Create an Database Diagram to Use as a Query Template Browse Data in Related Tables

Query

Tutorial: Build a Query Visually Create Cross-Connection Queries Reverse Engineer Queries Build a Subquery Edit SQL Editor Shortcut Keys View Result Sets Compare Differences in Data Export Data with One Click Export Share Excel Linked Queries

Report

Tutorial: Create a Toad Data Report Manage Reports

About Managing Reports DB2 Reports MySQL Reports ODBC Reports Oracle Reports SQL Server Reports

Publish and Share Reports

Automate

Getting Started with Automation Tutorial: Automate Excel Reports Automate Tasks Use Database Automation Activities Use File Automation Activities Use System Automation Activities Use Variables In Automation Execute/Schedule Automation Scripts

Considerations and Limitations when Working with Pivot Grids

Review the following considerations and limitations when working with Toad Pivot Grids.

General

Consideration/Limitation	Description
Large result set may cause delay or reduced performance in Pivot Grid	If your result set contains a large number of rows (one million or more), you may experience a delay when sending all rows to the Pivot Grid. In addition, you may experience reduced performance when working in the Pivot Grid. For best results, work with result sets of fewer than one million rows. Or, for larger result sets, do not read all rows. In addition, try the following workaround. Workaround: If your result set contains a large number of rows, build the pivot grid on a sampling of rows. Then use an Automation script to pivot all rows. Execute a query in the Query Builder or Editor, but do not read all rows. Send the sampling of rows to the Pivot Grid and create your pivot grid document. Create an Automation script using the Toad Pivot Grid activity with your pivot grid document as input. Run the Automation script to read all rows and refresh the pivot grid.
Cannot change connection for cross-connection query	If the underlying query in a Pivot Grid document is a cross-connection query, you cannot right-click the document tab to change the connection. This feature is disabled.

Consideration/Limitation

Description

Large result set may cause delay or reduced performance in Pivot Grid

If your result set contains a large number of rows (one million or more), you may experience a delay when sending all rows to the Pivot Grid. In addition, you may experience reduced performance when working in the Pivot Grid.

For best results, work with result sets of fewer than one million rows. Or, for larger result sets, do not read all rows. In addition, try the following workaround.

Workaround: If your result set contains a large number of rows, build the pivot grid on a sampling of rows. Then use an Automation script to pivot all rows.

Execute a query in the Query Builder or Editor, but do not read all rows.
Send the sampling of rows to the Pivot Grid and create your pivot grid document.
Create an Automation script using the Toad Pivot Grid activity with your pivot grid document as input.
Run the Automation script to read all rows and refresh the pivot grid.

Cannot change connection for cross-connection query

If the underlying query in a Pivot Grid document is a cross-connection query, you cannot right-click the document tab to change the connection. This feature is disabled.

Exporting to Excel

Consideration/Limitation	Description
Distinct Count summary type	Exporting to Excel Pivot: Distinct Count is not supported as a summary type in an Excel pivot table. When exporting to Excel using the Excel Pivot action, the Distinct Count summary type is converted to Count .
The Excel pivot table does not support all pivot grid functions	Exporting to Excel Pivot: Some functions supported by the pivot grid expression editor (calculated field) are not supported in an Excel pivot table. For calculated fields that include a function not supported by the Excel pivot table, the field is not exported when you export to an Excel pivot table (Excel Pivot) and Toad displays an informative error message.
Same field in both Row Area and Column Area	Exporting to Excel Pivot: In an Excel pivot table, the same field cannot be included in both the Row Area and the Column Area. If a Toad pivot grid contains the same field in both the Row and Column Areas, Toad displays an informative message and removes one of the fields in the exported Excel pivot table file.
Group By \| On First Letter	Exporting to Excel Pivot: Grouping by first letter is not supported in an Excel pivot table. If you attempt to export a pivot grid containing a grouping by first letter to an Excel pivot table, Toad displays a informative warning message.
Group By \| week type	Exporting to Excel Pivot: The following data groupings are not supported in an Excel pivot table. If you attempt to export a pivot grid containing such groupings, Toad displays an informative warning message. Weekday Week of Month Week of Year
Summary types	Exporting to Excel Pivot: The following Toad summary types may be interpreted differently in Excel: Average, Max, Min, StdDev, StdDevp, Var, and Varp.
Conditional formatting rule using a custom expression	For custom rules created using a custom expression, some functions may not be supported in Excel.
Conditional formatting rules created in Toad Data Point 4.2 or earlier	Conditional formatting rules created and saved in earlier versions of Toad are not supported when exporting to Excel using the current release of Toad. Workaround: Recreate the conditional formatting rules using the current release of Toad.
Microsoft Excel worksheet row and column limits	You may be unable to export to Excel if your pivot grid exceeds the Excel maximum row or column limits. See Microsoft Excel specifications and limits for more information.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Toad Data Point 6.1.2 - User Guide

Considerations and Limitations when Working with Pivot Grids

General

Exporting to Excel