exporting more tha 65k rows to excel

hi all,

I wanted to know how to export a report that returns more than 65,000 rows of data to MS Excel, it will fail because excel has a limit of 65K records per excel sheet.

What i want to know if there is some way to use multiple sheets (in the same excel file) to show more than 65K records

Eg we have 105K rows of data ...can we export 65K records in the first sheet, and the remaining 40K in the second sheet (of the same excel file)?

Appreciate the Help,

Siddharth

Hello Siddharth,

Using a Table, you can force an explicit page break after a certain number of rows. Because the Excel renderer creates a new worksheet for every explicit page break, you can use this technique to make sure no more than 65,536 rows are exported to a single sheet.

Create an outer table group using this group expression: =Int((RowNumber(Nothing)-1)/65000). Set Page break at end on the group.

Keep in mind that there isn't a one-to-one relationship between SSRS table rows and Excel rows unless the table is the only report item in the report body. So, if you have other report items in addition to your table you will have to also take that into account.

-Chris

|||

Hi Chris,

I knew page break created a new sheet in excel but didnt know how to do it. thanks a lot for thehelp its woking. appreciate it.

siddharth

sql

Export to pdf fail

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:

Cache-Control: no-cache

Pragma: no-cache

Export to pdf fail

Hello,

I'm using SQL Reporting services 2000 SP2.
I create a report and when i select pdf format in the export dropdownlist and i click on export, a popup (open/save) appears but when i click ok, i've an error message : "IE can't download Format=PDF of IP_ADRESSE".

If i do the same thing with excel type in the dropdownlist it works!

Do you know why?
Have you tried looking in the log files or Event viewer on the server?|||Yes I have an exception in the reporting services log but nothing in the event viewer.
The exception :
[code]
w3wp!library!d10!08/25/2006-14:18:52:: i INFO: Call to RenderNext( '/EPMDB_Report/EPM_Full_Dashboard' )
w3wp!chunks!d10!08/25/2006-14:18:52:: i INFO: ### GetReportChunk('RenderingInfo_PDF', 2), chunk was not found! this=3ffd3365-b936-477e-9108-c7ad88799346
w3wp!reportrendering!d10!08/25/2006-14:18:52:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown., ;
Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
at System.String.Substring(Int32 startIndex, Int32 length)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.ProcessString(TextBox context, String text, CanvasFontPDF font, CanvasPenPDF pen, Double left, Double top, Double paddingTop, Double width, Double height, Boolean wordTrim, String fontLanguage, Int32& charactersFitted, Int32& linesFilled, String& streamString)
at Microsoft.ReportingServices.Rendering.ImageRenderer.CanvasPDF.DrawString(TextBox context, OutlineObjects outlines, Boolean wordTrim, String text, CanvasFont font, CanvasPen pen, Double left, Double top, Double paddingTop, Double width, Double height, String fontLanguage, ReportUrl hyperLinkURL)
at Microsoft.ReportingServices.Rendering.ImageRenderer.TextBox.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.Rectangle.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.RenderingItemContainer.RenderToPage(Measurement parentClipBorder)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.ProcessPages(CompositionBase theComposition, Report theReport, Boolean needTotalPages, Int32 startPage, Int32 endPage, Boolean render)
at Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport.RenderReport(Report report, NameValueCollection deviceInfo, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStreamCallback)
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
End of inner exception stack trace
at Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRendererBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
End of inner exception stack trace
w3wp!webserver!d10!08/25/2006-14:18:52:: e ERROR: Reporting Services error Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. > System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.
Parameter name: length
[/code]

It seems to be a problem with the length parameter but i don't known what to do.

Any idea?
|||Ok I find th problem.
In my rdl file I have the language define as 'en' and for exporting in pdf the format has to been syntaxed like this: xx-XX.
I put my rdl in en-US and it works.

Thanks @.+
|||I found I had put some HTTP headers in at the website level on IIS to disable caching on some non-related ASP applications running in the same IIS instance.

I removed the HTTP headers:
Cache-Control: no-cache Pragma: no-cache