PivotTable too big??

I need help understanding why when I create certain pivottables using
external data through our SQL server and use just a few fields that MS
provides an error stating that my pivottable is too big and that I've exceed
65,536 "items". Here is the catch, I know for a fact that data-wise, that I
won't consume more that 10,000 lines. Is there some hidden placeholders
shall we call them that causes my pivottable to be larger than it appears?
Knowing there is a 65536 line limit is there any work around without going to
Access? Thanks in advance!

Pivot tables are not constrained to 65,536. I have done them up to 650,000
records so I don't think that is your issue. Are you using MS Query to return
the results into a sheet which you intend to then pivot off of, or are you
selecting get external data when you are configuring your pivot table. The
first method will cause problems as the sheet is tied to that 65,536 limit.
The only limit that I know of for pivot tables is that it does not like any
one dimension to be too flat. By that I mean If you have too many unique
items such as part numbers or such then the pivot will not be able to deal
with that. That limit is somewhere around 8,000 unique items.
--
HTH...

Jim Thomlinson


"James" wrote:

> I need help understanding why when I create certain pivottables using
> external data through our SQL server and use just a few fields that MS
> provides an error stating that my pivottable is too big and that I've exceed
> 65,536 "items". Here is the catch, I know for a fact that data-wise, that I
> won't consume more that 10,000 lines. Is there some hidden placeholders
> shall we call them that causes my pivottable to be larger than it appears?
> Knowing there is a 65536 line limit is there any work around without going to
> Access? Thanks in advance!

The following MSKB articles outline PivotTable limitations for different
versions of Excel:

XL2000: Limits of PivotTables in Excel
http://support.microsoft.com/default.aspx?id=211517

Description of the limits of PivotTable reports in Excel
http://support.microsoft.com/default.aspx?id=820742


James wrote:
> I need help understanding why when I create certain pivottables using
> external data through our SQL server and use just a few fields that MS
> provides an error stating that my pivottable is too big and that I've exceed
> 65,536 "items". Here is the catch, I know for a fact that data-wise, that I
> won't consume more that 10,000 lines. Is there some hidden placeholders
> shall we call them that causes my pivottable to be larger than it appears?
> Knowing there is a 65536 line limit is there any work around without going to
> Access? Thanks in advance!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Using Excel 2003, I get external data from the SQL server and then arrange
just the fields that I need to get the table established. Then I go into
each field and set the total to none so I get rid of all of the extra rows.
Then I'll expand (+) a few of the fields to see what I need and then add the
remaining fields for the report. Total fields will be between 6 to 8 at
most. Nothing huge. However, undoubtedly Excel will tell me the pivot table
has exceeded 65,536 "items" (not rows). By the way, I also have SP1 for
Office installed in XP Pro SP2 environment. Thanks for your previous quick
response!

"Jim Thomlinson" wrote:

> Pivot tables are not constrained to 65,536. I have done them up to 650,000
> records so I don't think that is your issue. Are you using MS Query to return
> the results into a sheet which you intend to then pivot off of, or are you
> selecting get external data when you are configuring your pivot table. The
> first method will cause problems as the sheet is tied to that 65,536 limit.
> The only limit that I know of for pivot tables is that it does not like any
> one dimension to be too flat. By that I mean If you have too many unique
> items such as part numbers or such then the pivot will not be able to deal
> with that. That limit is somewhere around 8,000 unique items.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "James" wrote:
>
> > I need help understanding why when I create certain pivottables using
> > external data through our SQL server and use just a few fields that MS
> > provides an error stating that my pivottable is too big and that I've exceed
> > 65,536 "items". Here is the catch, I know for a fact that data-wise, that I
> > won't consume more that 10,000 lines. Is there some hidden placeholders
> > shall we call them that causes my pivottable to be larger than it appears?
> > Knowing there is a 65536 line limit is there any work around without going to
> > Access? Thanks in advance!

When it says items, that leads me to believe that one of your dimensions is
too flat. A dimension contains members. A member is a unique "bucket" within
the dimension that aggregates all instances of that member. For example how
many unique part numbers or dates or ???'s do you have? The pivot is a way of
aggregating a large amount of data into a small number of members "unique
buckets". I think you might be asking for too many buckets... If that is the
case then you are hooped... You need to figure a way of decreasing the number
of members. That not the greatest of explanations. Debra is a godess at this
stuff. Maybe she can elaborate in a more coherent fasion.
--
HTH...

Jim Thomlinson


"James" wrote:

> Using Excel 2003, I get external data from the SQL server and then arrange
> just the fields that I need to get the table established. Then I go into
> each field and set the total to none so I get rid of all of the extra rows.
> Then I'll expand (+) a few of the fields to see what I need and then add the
> remaining fields for the report. Total fields will be between 6 to 8 at
> most. Nothing huge. However, undoubtedly Excel will tell me the pivot table
> has exceeded 65,536 "items" (not rows). By the way, I also have SP1 for
> Office installed in XP Pro SP2 environment. Thanks for your previous quick
> response!
>
> "Jim Thomlinson" wrote:
>
> > Pivot tables are not constrained to 65,536. I have done them up to 650,000
> > records so I don't think that is your issue. Are you using MS Query to return
> > the results into a sheet which you intend to then pivot off of, or are you
> > selecting get external data when you are configuring your pivot table. The
> > first method will cause problems as the sheet is tied to that 65,536 limit.
> > The only limit that I know of for pivot tables is that it does not like any
> > one dimension to be too flat. By that I mean If you have too many unique
> > items such as part numbers or such then the pivot will not be able to deal
> > with that. That limit is somewhere around 8,000 unique items.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "James" wrote:
> >
> > > I need help understanding why when I create certain pivottables using
> > > external data through our SQL server and use just a few fields that MS
> > > provides an error stating that my pivottable is too big and that I've exceed
> > > 65,536 "items". Here is the catch, I know for a fact that data-wise, that I
> > > won't consume more that 10,000 lines. Is there some hidden placeholders
> > > shall we call them that causes my pivottable to be larger than it appears?
> > > Knowing there is a 65536 line limit is there any work around without going to
> > > Access? Thanks in advance!