InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 10:24:56 GMT20http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspx:-D:-PJust 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.Thu, 31 Jan 2013 07:22:45 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxI know people who do.They use that to check the content of the datamart...Thank you for the trust! :-)Thu, 31 Jan 2013 07:20:44 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxHi,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... :-PRegardsldThu, 31 Jan 2013 07:18:07 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxIndeed, 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!Thu, 31 Jan 2013 07:13:41 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxAh fantastic :-DLearnt something new there. :w00t:In my environment you can set the maxium rows to 1048576.ThanksldThu, 31 Jan 2013 06:51:49 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxYes, if I go the Properties on that connection, there is a value "Maximum Number of Records to Retrieve" which defaults to 1000For 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 :-DThu, 31 Jan 2013 06:47:59 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxI 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.Thu, 31 Jan 2013 05:45:08 GMTScott Murray-240410http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxThere 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.Wed, 30 Jan 2013 02:57:30 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1409655-17-1.aspxHello 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 SP2Mon, 21 Jan 2013 11:13:35 GMTEric Mamet