Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

increasing Excel 2007 "Show Details" limit above 1000 rows? Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 11:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
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?

My server is SQL 2008 Enterprise 64 bits SP2
Post #1409655
Posted Wednesday, January 30, 2013 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 292, Visits: 262
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.
Post #1413538
Posted Thursday, January 31, 2013 5:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 243, Visits: 2,752
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.
Post #1414030
Posted Thursday, January 31, 2013 6:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
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

Post #1414099
Posted Thursday, January 31, 2013 6:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 292, Visits: 262
Ah fantastic

Learnt something new there.
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.
Post #1414104
Posted Thursday, January 31, 2013 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
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!
Post #1414123
Posted Thursday, January 31, 2013 7:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 292, Visits: 262
Hi,

I just remembered 1048576 is the maximum row limit of any excel spreadsheet from excel 2003 upwards.
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.
Post #1414127
Posted Thursday, January 31, 2013 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
I know people who do.

They use that to check the content of the datamart...

Thank you for the trust!
Post #1414131
Posted Thursday, January 31, 2013 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 292, Visits: 262


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.
Post #1414133
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse