Export Report to File: Export to Excel failed with ReportItems and sub report

Hi,

I have two RDL files, one is main.rdl, the other one is sub.rdl.

In the body of main.rdl, I threw in a subreport that links to sub.rdl. Follow by the subreport is a table, in which has a textbox called mytitle. In the page footer, I added one text box that references to mytitle by using ReportItems!mytitle.Value.

When this report rendered in HTML or PDF format, it worked fine. However, when exporting to excel format, it failed. I figured it is because there isn't a ReportItem in the subreport called mytitle. I tried adding a dummy textbox into the subreport and called it mytitle, but again, no luck.

Did anyone ever encounter the same problem? Is there a work around way?

Thanks,

How did the export fail? Did the report not export at all, or it was part of the report that didn't work? What error did you get? The excel renderer doesn't support nested tables/matrics. Did you have some complex in the subreport?|||

I've done more testing on the problem. I didn't use nested tables/matrics in main and sub reports. In my sub report, it has two tables, not nested, one follows the other.

The report was not exporting at all. It gave me an error when I tried to export to Excel in VS.NET 2005. The error message was:

An error occurred during local report processing.

An error occurred during rendering of the report.

An error occurred during rendering of the report

An unexpected error occurred in Report Processing.

The expression referenced a non-existing reportitem in the reportitems collection.

|||This sounds like a problem in our code. Can you attach your RDL please?|||

RDL for the main report

|||

RDL for sub report

<?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">
<DataSources>
<DataSource Name="DS">
<DataSourceReference>DS</DataSourceReference>
<rd:DataSourceID>fe1336f9-bea0-4334-8dca-de54f755ec93</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DS2</DataSetName>
<Top>1.125in</Top>
<ZIndex>3</ZIndex>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID_1">
<rd:DefaultName>ID_1</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>Turquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Second Table</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
<Textbox Name="ForFooter">
<Top>2.25in</Top>
<ZIndex>2</ZIndex>
<Width>2.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Dummy TextBox for Footer in MainReport</Value>
</Textbox>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.36in</Height>
<Value>SubReport</Value>
</Textbox>
<Table Name="table1">
<DataSetName>DS1</DataSetName>
<Top>0.5in</Top>
<Width>2in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ID">
<rd:DefaultName>ID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>First table</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.22in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
<Height>0.43in</Height>
</Table>
</ReportItems>
<Height>4.79in</Height>
</Body>
<rd:ReportID>d7980735-ccd4-4952-b9e3-849d60c1bc55</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DS1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select 'Value in Sub Report' AS ID</CommandText>
<DataSourceName>DS</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ID</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DS2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 'Second Table in SubReport' AS ID</CommandText>
<DataSourceName>DS</DataSourceName>
</Query>
<Fields>
<Field Name="ID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||Is this going to be resolved in the next service pack?|||I've filed a bug in our internal bug tracking database. We'll consider it for the next service pack or a future release. Thanks for reporting it.|||We have actually fixed this issue in SQL Server 2005 SP1. Please try it and let us know if it still doesn't work for you.|||

In Portuguese: Excelente

In Inglish: It's Very Good...

Thiago Cruz

thiago.cruz@.dotnetraptors.com.br

|||This was working without SP1 and is now broken after installing SP1.|||

i have vs studio 2k5 sp1 installed and i am getting this error. I have office 2003 installed. Is there still a bug ticket out for this? For now what I do is wait until the report is loaded then delete Excel from the dropdown list so it can't be accessed ( so the user can't generate this error, but it would nice to be working.)

Here is the code to remove Excel from the drop down list ...

Code Snippet

Dim dropExport As DropDownList = ReportViewer1.Controls(1).Controls(5).Controls(0)

dropExport.SelectedValue = "EXCEL"

dropExport.Items.RemoveAt(dropExport.SelectedIndex)

And here is the stack trace of the error when I click Export while Excel is selected:

Code Snippet

[Exception: The expression referenced a non-existing reportitem in the reportitems collection.]

[Exception: An unexpected error occurred in Report Processing.]

[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 have exactly the same error here. I have a list on the report body(to avoid the excel sub-reports rendering problem, I have to use list instead of table). There is a textbox and some other sub-reports on the list. A textbox in the header reference the list textbox.

The report looks ok for the screen view, but when I try the "Excel" export, I got the "The expression referenced a non-existing reportitem in the reportitems collection. " error. If I remove all sub-reports, it works fine.

My SQL server installed SP2. Below is the version message for the sql server [ Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ]. Appreciate any help!

(I'm using VS2005 with SP1, and reportviewer control with asp.net)

|||

Could any body give any help?

Thanks!

Sunday, February 26, 2012

Export to Excel failed with ReportItems and sub report

No comments:

Post a Comment

Export Report to File

Blog Archive

About Me