SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


increasing Excel 2007 "Show Details" limit above 1000 rows?


increasing Excel 2007 "Show Details" limit above 1000 rows?

Author
Message
Eric Mamet
Eric  Mamet
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2968 Visits: 925
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
ldorian81
ldorian81
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 335
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.
Scott Murray-240410
Scott Murray-240410
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 3142
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.
Eric Mamet
Eric  Mamet
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2968 Visits: 925
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 :-D
ldorian81
ldorian81
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 335
Ah fantastic :-D

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.
Eric Mamet
Eric  Mamet
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2968 Visits: 925
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!
ldorian81
ldorian81
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 335
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... :-P

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.
Eric Mamet
Eric  Mamet
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2968 Visits: 925
I know people who do.

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

Thank you for the trust! :-)
ldorian81
ldorian81
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 335
:-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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search