Tuesday, March 27, 2012

Excel Export problem

Hello,
I am trying to export some reports to excel format, using Reporting Services
SP1. It contains an OLAP query and a matrix allowing drill-down.
Reporting services errors with:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Object reference not set to an instance of an object.
An anyone help?
Thanks.
Ben.Can you post some code?
Dave
"Ben Mann" <BenMann@.discussions.microsoft.com> wrote in message
news:976C1E52-2D9F-4EA3-AC3E-1D7440B8FAB3@.microsoft.com...
> Hello,
> I am trying to export some reports to excel format, using Reporting
> Services
> SP1. It contains an OLAP query and a matrix allowing drill-down.
> Reporting services errors with:
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Object reference not set to an instance of an object.
> An anyone help?
> Thanks.
> Ben.|||Hello,
Im not sure how much help this might be, but this is the rdl source for the
report...
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<Top>1.82275cm</Top>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.5cm</Height>
<Width>9.75cm</Width>
<CanGrow>true</CanGrow>
<Value>="Team: " + Parameters!ReportTeam.Label</Value>
<Left>0.25cm</Left>
</Textbox>
<Textbox Name="ParameterDisplayYear">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Top>1.32275cm</Top>
<Height>0.5cm</Height>
<Width>14cm</Width>
<CanGrow>true</CanGrow>
<Value>="Year: " + Parameters!ReportYear.Value</Value>
<Left>0.25cm</Left>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>2.5cm</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Leavers">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_Leavers</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Measures_Leavers.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Total_Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Measures_Total_Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Measures_Total_Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>P</Format>
<FontFamily>Verdana</FontFamily>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=iIF(Sum(Fields!Measures_Leavers.Value) /
Sum(Fields!Measures_Total_Sales.Value) > 0,
Sum(Fields!Measures_Leavers.Value) / Sum(Fields!Measures_Total_Sales.Value),
0)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.5cm</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>2.55291cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.75cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.5cm</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>LeaversSummary</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_PopulationDate_Year">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="PopulationDate_Year">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>PopulationDate_Year</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PopulationDate_Year.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup2">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Quarter.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>PopulationDate_Year</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="PopulationDate_Quarter">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!PopulationDate_Quarter.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_ColumnGroup3">
<GroupExpressions>
<GroupExpression>=Fields!PopulationDate_Month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>PopulationDate_Quarter</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="PopulationDate_Month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value>=MonthName(Fields!PopulationDate_Month.Value,
true)</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.5cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.5cm</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Leavers</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total Sales</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#b5ddad</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Leavers (%)</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<Width>14.05291cm</Width>
<Top>2.32275cm</Top>
<Left>0.25cm</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Consultant_Sales_Team">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Sales_Team.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Consultant_Sales_Team">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>Consultant_Sales_Team</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Sales_Team.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>2.25cm</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>Consultant_Sales_Team</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="Consultant_Name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Name.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>2.75cm</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!Consultant_Post_Code_Area.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>Consultant_Name</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ReportItems>
<Textbox Name="Consultant_PostcodeArea">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BackgroundColor>#99ccff</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!Consultant_Post_Code_Area.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.25cm</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Image Name="image1">
<MIMEType />
<Height>1.32275cm</Height>
<Width>7.93651cm</Width>
<Source>External</Source>
<Style />
<Value>leaverssummarybyteam.gif</Value>
<Left>0.25cm</Left>
<ToolTip>Leavers Summary by Team</ToolTip>
<Sizing>AutoSize</Sizing>
</Image>
</ReportItems>
<Style />
<Height>5.25cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="MIS">
<rd:DataSourceID>922803da-5239-4648-8a28-ec0db0f80d99</rd:DataSourceID>
<DataSourceReference>MIS</DataSourceReference>
</DataSource>
</DataSources>
<Width>14.75cm</Width>
<DataSets>
<DataSet Name="LeaversSummary">
<Fields>
<Field Name="PopulationDate_Year">
<DataField>[PopulationDate].[Year].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PopulationDate_Quarter">
<DataField>[PopulationDate].[Quarter].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PopulationDate_Month">
<DataField>[PopulationDate].[Month].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Sales_Team">
<DataField>[Consultant].[Sales Team].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Type">
<DataField>[Consultant].[Type].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Name">
<DataField>[Consultant].[Name].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Consultant_Post_Code_Area">
<DataField>[Consultant].[Post Code
Area].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Leavers">
<DataField>[Measures].[Leavers]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Total_Sales">
<DataField>[Measures].[Total Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures__Leavers">
<DataField>[Measures].[%Leavers]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>MIS</DataSourceName>
<CommandText>= "SELECT { [Measures].[Leavers], [Measures].[Total
Sales], [Measures].[%Leavers] } ON COLUMNS, { Descendants([Consultant].[" +
Parameters!ReportTeam.Value + "], [Consultant].[Post Code Area], LEAVES) } ON
ROWS, { Descendants ([PopulationDate].[All PopulationDate].[" +
Parameters!ReportYear.Value + "], [PopulationDate].[Month], LEAVES)} ON PAGES
FROM PopulationActualAndSalesTarget"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>d36619a3-0ff9-4f79-b9a9-52cd0865cb2c</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="ReportYear">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Year</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>2004</Value>
</ParameterValue>
<ParameterValue>
<Value>2003</Value>
</ParameterValue>
<ParameterValue>
<Value>2002</Value>
</ParameterValue>
<ParameterValue>
<Value>2001</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ReportTeam">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Team</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>All Consultant</Value>
<Label>All Teams</Label>
</ParameterValue>
<ParameterValue>
<Value>Team 1</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 2</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 3</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 4</Value>
</ParameterValue>
<ParameterValue>
<Value>Team 5</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
Thanks.
Ben.
"Yoshi" wrote:
> Can you post some code?
> Dave
> "Ben Mann" <BenMann@.discussions.microsoft.com> wrote in message
> news:976C1E52-2D9F-4EA3-AC3E-1D7440B8FAB3@.microsoft.com...
> > Hello,
> >
> > I am trying to export some reports to excel format, using Reporting
> > Services
> > SP1. It contains an OLAP query and a matrix allowing drill-down.
> >
> > Reporting services errors with:
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Object reference not set to an instance of an object.
> >
> > An anyone help?
> >
> > Thanks.
> >
> > Ben.
>
>sql
Posted by molas at 10:56 PM
Labels: , , , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)