January 21, 2013 at 11:13 am
Hello everyone.
I am using the Excel 2007 Pivot Table "Show Details" functionality and find it limited to 1000 rows.
I thought I might be able to increase that by creating a default drill-through action in my SSAS Cube but, although the columns displayed are changed according to my drill through definition, the row limit to 1000 remains.
On the other hand, if I use "Additional Actions / My Drillthrough Action" in the right click menu, instead of "Show Details", I get the columns I defined but this time it is limited by the maximum rows I specified in "My Drillthrough Action".
Of course, I can instruct my users to use "Additional Actions / My Drillthrough Action" instead of "Show Details" but it would be nice if they did not have to!
Is it possible to overrule this 1000 rows limit? :ermm:
My server is SQL 2008 Enterprise 64 bits SP2
January 30, 2013 at 2:57 am
There is a memory limit on Pivot tables though i'm sure it's higher than 1000 rows but I supose that depends on your system and what you are trying to run.
(I'm sure the limit is around 35000 but i've never hit this issue personally so i'm not 100% certain)
Another thing i've heard about is 'server page fields' that help memory issues on pivot tables. Have a search on the internet that may help you further.
Regards
ld
Stoke-on-Trent
United Kingdom
If at first you don't succeed, go to the pub and drink away your current thought plan.
January 31, 2013 at 5:45 am
I do not have Office 2007 any more, but I believe it is the same as 2010... You make the change in the connection properites. You will want to go to the Pivot Table tab > Change Data Source > Connection Properties > Usage Tab... Change the maximum number of records to retrieve option from 1000.
January 31, 2013 at 6:47 am
Yes, if I go the Properties on that connection, there is a value "Maximum Number of Records to Retrieve" which defaults to 1000
For the record, I had actually found that I could create a Drillthrough action (in the cube) in which I can choose the maximum number of rows to retrieve as well.
I got it working with 500,000 rows and did not try more (that "should" be enough).
Thanks 😀
January 31, 2013 at 6:51 am
Ah fantastic 😀
Learnt something new there. :w00t:
In my environment you can set the maxium rows to 1048576.
Thanks
ld
Regards
ld
Stoke-on-Trent
United Kingdom
If at first you don't succeed, go to the pub and drink away your current thought plan.
January 31, 2013 at 7:13 am
Indeed, I just tried with 2007 and 2010 and got the same limit for "Show Details"
Just out of (silly) curiosity, I tried 5,000,000 in the Drillthrough and Visual Studio accepted it.
Within Excel, I had a warning that not all the data would fit in the spreadsheet and got 1048576 rows (including the 3 headers).
Pretty good!
January 31, 2013 at 7:18 am
Hi,
I just remembered 1048576 is the maximum row limit of any excel spreadsheet from excel 2003 upwards.:w00t:
It's the memory limitation due to the package being 32bit, when they go to 64bit excel it should go up.
Althought why anyone would want a spreadsheet with a million viewable rows... 😛
Regards
ld
Regards
ld
Stoke-on-Trent
United Kingdom
If at first you don't succeed, go to the pub and drink away your current thought plan.
January 31, 2013 at 7:20 am
I know people who do.
They use that to check the content of the datamart...
Thank you for the trust! 🙂
January 31, 2013 at 7:22 am
:-D:-P
Just found this looks like the 1048576 will be kept for 64bit versions aswell....
Excel 2013 is already introduced to us as part of ms office 2013 suite. Office 2013 is available in both 32 bit mode and 64 bit mode but the maximum number of rows and columns doesn’t depends on it. Having larger worksheet sizes just for 64-bit Excel would cause all sorts of document compatibility issues. Microsoft currently keep all copies of that application and version in one mode– regardless of whether it is 32-bit and 64-bit
Excel 2013 has maximum of 1,048,576 rows at present
Excel 2013 has maximum of 16,384 columns at present and represented by letters till XFD
Above details are only the maximum rows and columns details of a single sheet in excel 2013.If we want to handle more then 1 million rows of data we have to use multiple sheets to store the data. Excel 2013 can store upto 256 sheets theoretically.But with a 4 GB ram and quad core processor machine i am able to handle upto 5 million rows in 5 sheets and my machine went out of ‘low resource mode’ after that.
Regards
ld
Stoke-on-Trent
United Kingdom
If at first you don't succeed, go to the pub and drink away your current thought plan.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply