pivot table won't calculate

[gtgtgt]

A pivot table in Excel won't allow me to add more fields or move a field from
the column area to the row area. When I drag a new field or attempt to move
the field, the Excel status bar displays the progress bar and a message
"calculating pivot table report" but after about 1 or 2 seconds, it stops
w/out an error message.

The pivot table is based on an Access db table, which has 177,000 records
and 11 fields. The pt field list displays 10 of those fields. The table
itself only uses 4.

The workbook is about 12 mb, and when I query the cache.usedMemory property,
it returns 22,004,989 (I assume bytes).

I'm using Excel 2003 w/ SP2.

Has anyone experienced this problem before? Is there a solution?

Thanks,

gt

 

[rdwj]

have you changed anything in the data area - in particular have you changed
anything in the headers? What happens if you do not make any changes but just
do a "refresh data"?

What is the size of the Internal memory of your pc? This should not be a
problem.....

Alternatively, create a new pivot table and see what happens....

 

[gtgtgt]

Hi rdwj -

The column area consists of dates, grouped by month. Refresh data works fine
- data is refreshed. I even tried requerying and eliminating 6 of the fields,
w/ no improvement.

My box has 2 gigs of ram.

Thanks,
gt

 

[Jim Thomlinson]

A pivot table only allows up to 8,000 unique members in any one dimension. So
for instance you can only have up to 8,000 unique dates. You could have
1,000,000 records which is fine so long as you don't have more than 8,000
unique dates. Once you go past 8,000 things break down. That is one
possibility.

177,000 records is likely not enough to cause a problem in XL2003 unless one
or more of the fields is a text field with long character strings. I have
taken Pivot tables as high as 3 million records with no problem.

 

[gtgtgt]

Hi Jim:

Thanks for posting. I thought that might be the case, but as it turns out, I
only have about 180 unique dates.

Here's another behavior that might lead to the cause. The dates in the
column area are grouped by month. I currently have Jan - July selected. When
I click on the dates dropdown and select august, I get the same behavior. The
status bar displays the message 'calculating the pivot table report', the
progress bar goes about half way... and that's all. The August field doesn't
show. If I uncheck January and then check August, hunky-dory. Puzzling.

Thanks,
gt

 

[Jim Thomlinson]

What happens if you remove the dates from the column???

 

[gtgtgt]

That was an interesting exercise. I tried dragging the date button off the
piv tbl, and Excel exhibited the same behavior (progress bar goes halfway,
the message "calculating pivot table report" is displayed) and then Excel
sighs and gives up w/ the date columns in place.

Why did you ask this question? Do you have a theory?

Thanks again,

gt

 

[Jim Thomlinson]

I have to get going for the day. I will be back tomorrow. I would be
interested in knowing what your pivot table shows now. Did it successfully
remove the date dimension? Try removing everything from the table and add
things back one piece at a time excluding the dates. Note any error messages
that occure.

In a sperate workbook try recreating the pivot table. Do not group the dates
dimension. What happens.

Try re-creating the pivot table off of a query in the Access database that
returns only a subset of the records from your original table.

I have no great theory at this point. Mostly I am just trying to "Jiggle the
handle" as it were...

 

[gtgtgt]

Hi Jim:

Thanks for help. I've tried all that - and more now. Still no dice. I'm
going to create another field to use in the PageField area to function as a
filter. Maybe that will work.

gt