Chat now with support
Chat with Support

Toad Data Studio 1.1 - Installation Guide

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.

  1. Execute a query in the Query Builder or Editor, but do not read all rows.
  2. Send the sampling of rows to the Pivot Grid and create your pivot grid document.
  3. Create an Automation script using the Toad Pivot Grid activity with your pivot grid document as input.
  4. 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.
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.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating