Why is a 16,000 Record Table exceeding the 65,356 row Limit in Excel? 1

[Rob999]

Hi everyone!

I'm importing data from a SQL Server 2000 database (an OLAP cube file, specifically) into Excel 2000 that was created using the 'Get External Data' / 'New Database Query' selection off of the 'Data' menu heading. Excel 2k has 65,536 rows available, but my Fact Table - which has 15,954 records - is producing the following dialog box error message when I attempt to drill down in the resultant Pivot Table to the unique account number:

"Microsoft Excel cannot make this change because there are too many row or column items."!

It concludes with "Drag at least one row or column field off the PivotTable or to the Page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut menu." This is just telling me to reduce the returned recordset - but I should have about 50,000 rows to spare!!! In fact, since the resultset should only have about 8,000 records - it shouldn't be even that close.

I've also tried using the Tables in SQL Server 2000 as only the Data source and using Microsoft Query to make the connection and then construct an Excel Cube file - but with the same result. I've drastically curtailed the number of records (and this works correctly with the same drilldown on smaller datasets), but that will negate the purpose of the application.

I've looked in Books on Line and the MSDN Website - in addition to 2 of my books ("Unlocking OLAP with Microsoft SQL Server" - pretty good intro; and "The Definitive Guide to Excel VBA" - EXCELLENT), but to no avail. Apparently they work with much smaller datasets, or aren't giving up the 'secret'. Am I missing something?

I'd sure appreciate any help or suggestions. Thanks in Advance!

 

[xlhelp]

In a Pivot Table, the product of number of items in all column fields cannot exceed 32,768. If you have a PivotTable that contains four column fields and the fields contain 10, 15, 2, and 40 items respectively. The product of these values is 10 x 15 x 2 x 40 or 12,000. If you add one more field that contains 3 items, the product would be 36,000. And since this greater than 32,768, you get an error. Also, Excel is limited to 256 columns. As such, you may not be able to display the entire Table.

Similarly, the product of the number of items in all row fields cannot exceed 2^31 (~2.1 billion) items. Again, Excel is limited to 65,536 rows.

There is a way to optimize your Pivot Table. You can find the info at

http://www.microsoft.com

in the following article

Q211515 XL2000: Using Server Page Fields in PivotTables

 

[Rob999]

Thanks xlhelp!!!

Looks like I may have to go with Plan B - read all the items as placed by the user on the Pivot Table and send a dynamic query to the database, returning the reports that could have been completed by just grabbing those account numbers and sending them. Bummer.

I've got about 1.1 million records and there's no way I can work within Excel's Pivot Table limits as described per your reference. But now I can sleep at night again (well, I could if I didn't have to finish Plan B so soon!).

Thanks again.