Showing posts with label formats. Show all posts
Showing posts with label formats. Show all posts

Thursday, March 29, 2012

Error exporting to Excel with expression in footer

I can export to all other formats from the report viewer with no issues, but when I try to render to Excel, I get an exception:

[Exception: Object reference not set to an instance of an object.]

[Exception: An error occurred during rendering of the report.]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +553
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +97
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +126
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I've pinned it down to a footer cell that uses an expression to determine the TextAlign property. If I do something as simple as ="Right", I get the error. Of course, if I just use Right, there is no problem. So are expressions not supported in footer cell properties or is there possibly some fix for this?

Thanks.

The only solution would be to keep the textbox in the report body than the footer.

Shyam

|||

How can I get text to act like a page footer in the report body?

In case any MS folks are reading and want to reproduce this problem, here's a simple report that exhibits the behavior:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>0.79in</Height>

</Body>

<rd:ReportID>503a3ed2-a87d-4315-8de8-77c3ff0e807f</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<Width>5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<PageFooter>

<ReportItems>

<Textbox Name="textbox3">

<Top>0.125in</Top>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<TextAlign>=IIf(Globals!PageNumber Mod 2 = 0, "Left", "Right")</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Test</Value>

</Textbox>

</ReportItems>

<Height>0.375in</Height>

<PrintOnLastPage>true</PrintOnLastPage>

<PrintOnFirstPage>true</PrintOnFirstPage>

</PageFooter>

<TopMargin>1in</TopMargin>

</Report>

|||

Try to handle in the value property of the textbox. For example:

IIf(Globals!PageNumber Mod 2 = 0, CStr(Globals!PageNumber) & Space(15), Space(15) & CStr(Globals!PageNumber))

Shyam

Error exporting to Excel with expression in footer

I can export to all other formats from the report viewer with no issues, but when I try to render to Excel, I get an exception:

[Exception: Object reference not set to an instance of an object.]

[Exception: An error occurred during rendering of the report.]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +553
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +97
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +126
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

I've pinned it down to a footer cell that uses an expression to determine the TextAlign property. If I do something as simple as ="Right", I get the error. Of course, if I just use Right, there is no problem. So are expressions not supported in footer cell properties or is there possibly some fix for this?

Thanks.

The only solution would be to keep the textbox in the report body than the footer.

Shyam

|||

How can I get text to act like a page footer in the report body?

In case any MS folks are reading and want to reproduce this problem, here's a simple report that exhibits the behavior:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<Height>0.79in</Height>

</Body>

<rd:ReportID>503a3ed2-a87d-4315-8de8-77c3ff0e807f</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<Width>5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<PageFooter>

<ReportItems>

<Textbox Name="textbox3">

<Top>0.125in</Top>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<TextAlign>=IIf(Globals!PageNumber Mod 2 = 0, "Left", "Right")</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Test</Value>

</Textbox>

</ReportItems>

<Height>0.375in</Height>

<PrintOnLastPage>true</PrintOnLastPage>

<PrintOnFirstPage>true</PrintOnFirstPage>

</PageFooter>

<TopMargin>1in</TopMargin>

</Report>

|||

Try to handle in the value property of the textbox. For example:

IIf(Globals!PageNumber Mod 2 = 0, CStr(Globals!PageNumber) & Space(15), Space(15) & CStr(Globals!PageNumber))

Shyam

Error exporting to Excel

SSRS 2005...report exports to all formats, but when I try to go to excel i get an error.

Unhandled exception was caught:

Microsoft.Reporting.WebForms.ReportServerException: An error occurred during rendering of the report. (rrRenderingError)

Microsoft.Reporting.WebForms.ReportServerException: Stack empty

Excel has limitations that other rendering extensions do not have. Two of these limitations are from Excel - Max of 256 columns per sheet and ~65000 rows per sheet.|||

Actually that wasn't the issue...the report had about 10 columns and 3000 rows of data.

I solved it. The issue is that one of the formulas was trying to divide by zero in the denominator. The buggy behavior was that in HTML or PDF this simply renders as #Error in the textbox. But when you go to export to Excel, you don't even get that luxury. You get a nasty and ambiguous "Stack Empty" rendering error.

Actually if the resultset IS more than 65k rows it would be nice to provide an enduser with a message to that effect anyway...

sql