Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table row fields limit vs. number of rows in use for Excel 2
I'm confused on the definitions of row field "items" limits vs. number of
rows in use, and "items" in a field and report. Help bulletin 264626 states there is a limit of "8,000 items in a PivotTable report". HB 211517 states "There is a limit of 8,000 unique items per row field, column field, or page field." And "The prduct of the number of items in all row fields in a PivotTable cannot exceed ...approx. 2.1 billiion items." Here is my problem. Existing PT report suddenly started showing error message on Excel 2000 or earlier versions when trying to use drop down sort box from Page field. Says "Unable to make change. Too many row field items...drag one of the row fields to Page field location or delete from PT." When I do either of these I can sort the drop down box again. Newer Excel versions have no problem sorting, I'm guessing since they can handle up to 32,500 items in a field. I am trying to understand what exactly is the limiting factor for the older versions. One expert is telling me that the total number of report rows has a 2800 line limit but I can't find that anywhere in the Help database. Year end report for last year was just under 2800 total rows and works fine for all versions. Currently trying to consolidate data to get down to 2800 rows hoping report will again work on older versions. Any insite would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table row fields limit vs. number of rows in use for Excel 2
The 8,000 limit refers to the number of member you are allowed to have in a
single dimension. A dimension is something like say Time, Geography, Parts or... Members are the individual items that are stored in that dimension. So for example a Parts dimension can only hold 8,000 unique Part Numbers. You source data could have a million rows but the number of unique part numbers can not exceed 8,000. Another way to think of it is that in any one dimension there are only 8,000 buckets into which you can store amounts. As soon as you come up with the 8,001 unique item then the pivot table will crash becuase it ran out of buckets. As for your limit of 2,800 I have not heard that one... Not to say that there isn't one, but that is new to me. -- HTH... Jim Thomlinson "VP needs help" wrote: I'm confused on the definitions of row field "items" limits vs. number of rows in use, and "items" in a field and report. Help bulletin 264626 states there is a limit of "8,000 items in a PivotTable report". HB 211517 states "There is a limit of 8,000 unique items per row field, column field, or page field." And "The prduct of the number of items in all row fields in a PivotTable cannot exceed ...approx. 2.1 billiion items." Here is my problem. Existing PT report suddenly started showing error message on Excel 2000 or earlier versions when trying to use drop down sort box from Page field. Says "Unable to make change. Too many row field items...drag one of the row fields to Page field location or delete from PT." When I do either of these I can sort the drop down box again. Newer Excel versions have no problem sorting, I'm guessing since they can handle up to 32,500 items in a field. I am trying to understand what exactly is the limiting factor for the older versions. One expert is telling me that the total number of report rows has a 2800 line limit but I can't find that anywhere in the Help database. Year end report for last year was just under 2800 total rows and works fine for all versions. Currently trying to consolidate data to get down to 2800 rows hoping report will again work on older versions. Any insite would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table row fields limit vs. number of rows in use for Exc
Thanks Jim. That is a finite number per row field, or a combined number for
all? PT contains Sales Territory number, Customer name, Generator Name and Generator State sorted by date of sales transaction where many of the Customer name and Generator Name items are the same. If any one of the row fields exceed 8,000 I would expect the sort function to stop working. From this I would expect to have 8000 rows/lines for the report but am at 2800 and limiting out. How can I determine which row field has exceeded the 8000 items? Is it the actual rows and cells that you can see on the report, or does it include all of the background data entries that are sorted to fill in the PT as you look at it? "Jim Thomlinson" wrote: The 8,000 limit refers to the number of member you are allowed to have in a single dimension. A dimension is something like say Time, Geography, Parts or... Members are the individual items that are stored in that dimension. So for example a Parts dimension can only hold 8,000 unique Part Numbers. You source data could have a million rows but the number of unique part numbers can not exceed 8,000. Another way to think of it is that in any one dimension there are only 8,000 buckets into which you can store amounts. As soon as you come up with the 8,001 unique item then the pivot table will crash becuase it ran out of buckets. As for your limit of 2,800 I have not heard that one... Not to say that there isn't one, but that is new to me. -- HTH... Jim Thomlinson "VP needs help" wrote: I'm confused on the definitions of row field "items" limits vs. number of rows in use, and "items" in a field and report. Help bulletin 264626 states there is a limit of "8,000 items in a PivotTable report". HB 211517 states "There is a limit of 8,000 unique items per row field, column field, or page field." And "The prduct of the number of items in all row fields in a PivotTable cannot exceed ...approx. 2.1 billiion items." Here is my problem. Existing PT report suddenly started showing error message on Excel 2000 or earlier versions when trying to use drop down sort box from Page field. Says "Unable to make change. Too many row field items...drag one of the row fields to Page field location or delete from PT." When I do either of these I can sort the drop down box again. Newer Excel versions have no problem sorting, I'm guessing since they can handle up to 32,500 items in a field. I am trying to understand what exactly is the limiting factor for the older versions. One expert is telling me that the total number of report rows has a 2800 line limit but I can't find that anywhere in the Help database. Year end report for last year was just under 2800 total rows and works fine for all versions. Currently trying to consolidate data to get down to 2800 rows hoping report will again work on older versions. Any insite would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table row fields limit vs. number of rows in use for Exc
It is a finite number per dimension. If I understand you correctly you have
the following dimensions (Give or take a few)... Sales Territory number Customer Name Generator Name Generator State Date Sales Amount You can not have more than 8,000 unique Sales Territory Numbers, or 8,000 unique Customer Names, or 8,000 unique... The limit is per dimension (what I think you are calling a field). Any place you have more than 8,000 you will not be able to add that dimension (Field) to the left column, top row or upper filter. If you do exceed that number then the pivot table just will not work when you try to aggregate by that dimension. If you exceeded the 8,000 limit by adding more records of source data to an existing pivot table then the pivot will not refresh properly. Try creating a new pivot off of your existing source data and see which field that it is having a problem with... -- HTH... Jim Thomlinson "VP needs help" wrote: Thanks Jim. That is a finite number per row field, or a combined number for all? PT contains Sales Territory number, Customer name, Generator Name and Generator State sorted by date of sales transaction where many of the Customer name and Generator Name items are the same. If any one of the row fields exceed 8,000 I would expect the sort function to stop working. From this I would expect to have 8000 rows/lines for the report but am at 2800 and limiting out. How can I determine which row field has exceeded the 8000 items? Is it the actual rows and cells that you can see on the report, or does it include all of the background data entries that are sorted to fill in the PT as you look at it? "Jim Thomlinson" wrote: The 8,000 limit refers to the number of member you are allowed to have in a single dimension. A dimension is something like say Time, Geography, Parts or... Members are the individual items that are stored in that dimension. So for example a Parts dimension can only hold 8,000 unique Part Numbers. You source data could have a million rows but the number of unique part numbers can not exceed 8,000. Another way to think of it is that in any one dimension there are only 8,000 buckets into which you can store amounts. As soon as you come up with the 8,001 unique item then the pivot table will crash becuase it ran out of buckets. As for your limit of 2,800 I have not heard that one... Not to say that there isn't one, but that is new to me. -- HTH... Jim Thomlinson "VP needs help" wrote: I'm confused on the definitions of row field "items" limits vs. number of rows in use, and "items" in a field and report. Help bulletin 264626 states there is a limit of "8,000 items in a PivotTable report". HB 211517 states "There is a limit of 8,000 unique items per row field, column field, or page field." And "The prduct of the number of items in all row fields in a PivotTable cannot exceed ...approx. 2.1 billiion items." Here is my problem. Existing PT report suddenly started showing error message on Excel 2000 or earlier versions when trying to use drop down sort box from Page field. Says "Unable to make change. Too many row field items...drag one of the row fields to Page field location or delete from PT." When I do either of these I can sort the drop down box again. Newer Excel versions have no problem sorting, I'm guessing since they can handle up to 32,500 items in a field. I am trying to understand what exactly is the limiting factor for the older versions. One expert is telling me that the total number of report rows has a 2800 line limit but I can't find that anywhere in the Help database. Year end report for last year was just under 2800 total rows and works fine for all versions. Currently trying to consolidate data to get down to 2800 rows hoping report will again work on older versions. Any insite would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
Pivot Table Repeats Names in multiple rows | Excel Discussion (Misc queries) | |||
Select all subtotal rows on Pivot Table | Excel Worksheet Functions | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table error (25000 rows!!) | Excel Discussion (Misc queries) |