January 8, 2009 at 1:21 pm
Hi,
I have ran into the below error because of bulk and huge data. The error is given below:
"Error: System.Exception: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. Message: Excel Rendering Extension : Number of rows in the Excel sheet exceeded the limit of 65536 rows. (Internal) Stack inner exception generated an error."
I know that the error is caused because of the more number of rows exceeding 65536.
Now i want to know how can i handle that in the SSRS 2005. I have a tabular report with header, details and footer section.
I tried assigning "=Int((RowNumber(Nothing) - 1) / 20)" as the expression when i right clicked on details section and selected the edit group option. And when i try to run this i get 9 pages becuase i have 175 records and the strange thing is only the first record is shown per page. And the sheet is broke in excel.
I donno why all other columns in the detail section are invisiible except the first record per page. only 1 record is shown out of 20 records and 21 out of 40 in second page and goes on.
If the records are visible my problem is solved. I will just replace that with 65536 so that the maximum number of records in excel are reached and then page break is applied. I dont know why are the data invisible.
Please tell me why the remaining items are getting invisible.
Any help would be greatly appreciated.
Thanks,
Suman
January 8, 2009 at 1:32 pm
The page break will not get you around the problem of max rows in excel. If you could maybe post some screen shots we might be able to offer some more information...
January 8, 2009 at 1:36 pm
Thanks for the reply. Can you please give me solution to show the records in second worsksheeet in excel if the records excell 65536.
I need solution to show records even if they cross 65536 when exported to excel.
Please answer my question, any help would be appreciateed.
Thanks,
Suman
January 8, 2009 at 2:13 pm
The excel limit is a hard limit. The only way to work around that is to summarize the data more in your select statement (group by) or select a smaller range of data in your parameters i.e. date range, from/to etc...
January 8, 2009 at 2:15 pm
The only other solution I can think of is paginate the query, and limit it to the max number of rows per sheet, and include the "page number" as an input parameter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2009 at 2:59 pm
In your table, try creating a group (outside of any current grouping you have) with no header or footer, and the page break after value set to true. For the grouping value, use an expression similar to
=ROWNUMBER(Nothing) MOD 60000
This should force a page break after 60000 rows of data, which will translate into a new excel tab when you export. I believe the 65K limit in Excel is on a per worksheet basis.
January 8, 2009 at 3:01 pm
can you please let me know how i can paginate the query. It would be great if you can assist me in making this happen.
Is this all to be written on RDL file ?? Can you please make it more clear instead?
Thanks,
Suman
January 9, 2009 at 8:59 am
Thanks for the reply.
I could achieve that as you said:
Create a group outside the table with no headers and having page break at the end and the expression for grouping should be as given. 30 is the number for which you need a break. Similarly we can use 65000 but it is really failing bcoz of the grouping I suppose, not sure what might be the answer.
=ceiling(rownumber(nothing)/30)
when i use a less number like 30 40 100 200 it is ok, but when i use 65000 my RDLC file looks like it is hanged up and no report comes up. But when i remove that and execute the report for the same data it comes it easily on the rdlc files. And exporting to excel from this viewer is really failing i think it is becoz of the memory option.
But page break with 65000 is making the report come up very slowly compared to without pagination. Is there any way we can avoid this.
And export to excel fails in normal (without pagination) when rows exceed like 40 or 50k.
Please let me know if you have any answer.
Any help would be greatly appreciated.
October 29, 2009 at 12:06 pm
I'm thinking about a different route. I don't know what kind of "good" information can be contained in a +65K line Excel spreadsheet. I don't think people looking at that data could glean any meaningful info. Try to go back to your customer and find out what they're using this for. You may be able to deliver a better solution thru an SSAS report or some kind of Excel Pivot table that reads from an SSAS cube.
If you're just dumping informational rows of data, try using a different format, such as CSV. Then the user can use some other tool (Access, etc.) besides Excel to view the data. But I would still consider the reason for the report request.
October 29, 2009 at 1:07 pm
There is a solution to go beyond 65k rows, which is use Excel 2007, which can go MUCH larger than that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2009 at 7:55 am
If you do go with Excel 2007, you'll have to remember to change the file type to xlsx during the Export process as SSRS2005 and 2008 default to saving files as xls file type when the Export choice of Excel is picked.
October 30, 2009 at 2:06 pm
GSquared (10/29/2009)
There is a solution to go beyond 65k rows, which is use Excel 2007, which can go MUCH larger than that.
100% correct - in fact you can go up to 1,048,576 rows in Excel 2007.
Joe
November 1, 2009 at 8:45 am
Spread the results on multiple worksheets.
Have a look at pagebreaks:
November 2, 2009 at 12:46 pm
crookj (10/30/2009)
100% correct - in fact you can go up to 1,048,576 rolls in Excel 2007.Joe
That's a lot of dinner rolls! 😛
November 2, 2009 at 1:06 pm
Mark Eytcheson (10/30/2009)
If you do go with Excel 2007, you'll have to remember to change the file type to xlsx during the Export process as SSRS2005 and 2008 default to saving files as xls file type when the Export choice of Excel is picked.
Everything I read, including the BOL (http://msdn.microsoft.com/en-us/library/ms159836.aspx), states that Excel 2007 is not supported is SSRS 2008.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply