Friday, February 17, 2012

Excel rendering crash with parameters. Ok as HTML.

Hi,
I am trying to render report I have with a matrix on it using the web
service.
It works fine if I render to excel format with no parameters provided
to the report.
It also works fine if I use the exact same code and just change the
render format to "HTML4.0" instead of "EXCEL".
As the error only happens with Excel specified and parameters provided
could this be a bug?
I had a search in this group and didn't find anyone else reporting
this so I may well be doing something silly.
Any advice would be apreciated, code and the error message returned in
the SoapException.Detail.Text follow. Please let me know if you need
any more details about what I'm doing
Thanks
Code:
string strReportName = "/MyReport";
string Hist = null;
XL3Addin.ReportService.DataSourceCredentials[] Creds = new
XL3Addin.ReportService.DataSourceCredentials[0];
string ShowHide = "";
string Enc = "";
string Mime = "";
string[] Streams;
XL3Addin.ReportService.ParameterValue[] ParamsOut;
XL3Addin.ReportService.Warning[] Warns;
//get the parameters out of a datagrid I have
XL3Addin.ReportService.ParameterValue[] ParamsIn = new
XL3Addin.ReportService.ParameterValue[this.ParamterTable.Rows.Count];
for(int i=0; i<this.ParamterTable.Rows.Count; i++)
{
ParamsIn[i] = new XL3Addin.ReportService.ParameterValue();
ParamsIn[i].Name = this.ParamterTable.Rows[i].ItemArray[0].ToString();
ParamsIn[i].Value = this.ParamterTable.Rows[i].ItemArray[2].ToString();
ParamsIn[i].Label = this.ParamterTable.Rows[i].ItemArray[1].ToString();
}
byte[] rep = rs.Render(strReportName, RenderFormat, Hist, DeviceInfo,
ParamsIn, Creds, ShowHide, out Enc, out Mime, out ParamsOut, out
Warns, out Streams);
Works if:
string RenderFormat = "EXCEL";
string DeviceInfo = "<DeviceInfo><OmitDocumentMap>False</OmitDocumentMap><OmitFormulas>False</OmitFormulas><RemoveSpace>0.125in</RemoveSpace></DeviceInfo>";
Is changed to:
string RenderFormat = "HTML4.0";
string DeviceInfo = null;
Error Message :
rrRenderingError
400
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rrRenderingError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00
Microsoft SQL Server Reporting Services
8.00.878.00
127
OsIndependent
1033
Microsoft.ReportingServices.Processing
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Microsoft.ReportingServices.ExcelRendering
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Microsoft.ReportingServices.ExcelRendering
Object reference not set to an instance of an object.Hmm, no takers yet ;)
Will it help if i post my rdl definition?
It is an OLAP grid with drill down and filters.
Am an trying to render with parameters of "CA" and "Drink" for Param0
and Param1 respectively.
The HTML file is produced, but still .Render() with Excel as the type
crashes.
I hope posting this XML doesn't mess the board around too much.
Any help gratefully received
Thanks
Colin
<?xml version="1.0"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="<GridSpacing">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"><GridSpacing
xmlns="0.25cm</GridSpacing><RightMargin>1in</RightMargin><Body><ReportItems><Matrix">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">0.25cm</GridSpacing><RightMargin>1in</RightMargin><Body><ReportItems><Matrix
Name="Martix0"><Corner><ReportItems><Textbox
Name="TextBox0"><Style><BorderStyle><Default>Solid</Default><Top>None</Top><Left>None</Left></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><FontSize>26pt</FontSize><Color>DarkBlue</Color></Style><Value>Test
with filters</Value><CanGrow>true</CanGrow><ZIndex>0</ZIndex></Textbox></ReportItems></Corner><Style></Style><MatrixRows><MatrixRow><MatrixCells><MatrixCell><ReportItems><Textbox
Name="TextBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><BackgroundColor>AliceBlue</BackgroundColor><TextAlign>Right</TextAlign><Format>C0</Format></Style><Value>=Sum(Fields!Measure0.Value)</Value><CanGrow>true</CanGrow><ZIndex
1</ZIndex></Textbox></ReportItems></MatrixCell></MatrixCells><Height>0.25in</Height></MatrixRow></MatrixRows><MatrixColumns><MatrixColumn><Width>0.875in</Width></MatrixColumn></MatrixColumns><DataSetName>DataQuery</DataSetName><ColumnGroupings><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp0"><GroupExpressions><GroupExpression>=Fields!ColLvl0.Value</GroupExpression></GroupExpressions></Grouping><ReportItems><Textbox
Name="ColTxtBox0"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl0.Value</Value><CanGrow>true</CanGrow><ZIndex>2
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp1"><GroupExpressions><GroupExpression>=Fields!ColLvl1.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox0</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl1.Value</Value><CanGrow>true</CanGrow><ZIndex>3
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp2"><GroupExpressions><GroupExpression>=Fields!ColLvl2.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox1</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox2"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl2.Value</Value><CanGrow>true</CanGrow><ZIndex>4
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp3"><GroupExpressions><GroupExpression>=Fields!ColLvl3.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox2</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox3"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl3.Value</Value><CanGrow>true</CanGrow><ZIndex>5
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp4"><GroupExpressions><GroupExpression>=Fields!ColLvl4.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox3</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox4"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl4.Value</Value><CanGrow>true</CanGrow><ZIndex>6
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><Height>0.25in</Height><StaticColumns><StaticColumn><ReportItems><Textbox
Name="TextBox7"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>Unit
Sales</Value><CanGrow>true</CanGrow><ZIndex>7</ZIndex></Textbox></ReportItems></StaticColumn></StaticColumns></ColumnGrouping></ColumnGroupings><Filters><Filter><FilterExpression>=Fields!RowLvl1.Value</FilterExpression><Operator>Equal</Operator><FilterValues><FilterValue>=Parameters!Param0.Value</FilterValue></FilterValues></Filter><Filter><FilterExpression>=Fields!ColLvl0.Value</FilterExpression>
Operator>Equal</Operator><FilterValues><FilterValue>=Parameters!Param1.Value</FilterValue></FilterValues></Filter></Filters><RowGroupings><RowGrouping><DynamicRows><Grouping
Name="RowGrp0"><GroupExpressions><GroupExpression>=Fields!RowLvl0.Value</GroupExpression></GroupExpressions></Grouping><ReportItems><Textbox
Name="RowTxtBox0"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl0.Value</Value><CanGrow>true</CanGrow><ZIndex>8</
Index></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox9"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>9</ZIndex></Text
ox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping><RowGrouping><DynamicRows><Grouping
Name="RowGrp1"><GroupExpressions><GroupExpression>=Fields!RowLvl1.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>RowTxtBox0</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="RowTxtBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl1.Value</Value><CanGrow>true</CanGrow><ZIndex>10<
ZIndex></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox11"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>11</ZIndex></Te
tbox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping><RowGrouping><DynamicRows><Grouping
Name="RowGrp2"><GroupExpressions><GroupExpression>=Fields!RowLvl2.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>RowTxtBox1</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="RowTxtBox2"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl2.Value</Value><CanGrow>true</CanGrow><ZIndex>12<
ZIndex></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox13"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>13</ZIndex></Te
tbox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping></RowGroupings></Matrix></ReportItems><Style></Style><Height>1.25in</Height><ColumnSpacing>1cm</ColumnSpacing></Body><TopMargin>1in</TopMargin><DataSources><DataSource
Name="DataSource1"><DataSourceID
xmlns="B7BD158F-94EE-41E4-BA21-CDC54DBA3983</DataSourceID><ConnectionProperties><DataProvider>OLEDB</DataProvider><ConnectString>Provider=MSOLAP.2;Client">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">B7BD158F-94EE-41E4-BA21-CDC54DBA3983</DataSourceID><ConnectionProperties><DataProvider>OLEDB</DataProvider><ConnectString>Provider=MSOLAP.2;Client
Cache Size=25;Data Source=iis-covertonxp;Initial Catalog=FoodMart
2000;Auto Synch Period=10000</ConnectString><IntegratedSecurity>true</IntegratedSecurity></ConnectionProperties></DataSource></DataSources><Width>7.125in</Width><DataSets><DataSet
Name="DataQuery"><Fields><Field
Name="ColLvl0"><DataField>[Product].[Product
Family].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl1"><DataField>[Product].[Product
Department].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl2"><DataField>[Product].[Product
Category].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl3"><DataField>[Product].[Product
Subcategory].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl4"><DataField>[Product].[Brand
Name].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl0"><DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl1"><DataField>[Customers].[State
Province].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl2"><DataField>[Customers].[City].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="Measure0"><DataField>[Measures].[Unit
Sales]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>
SELECT
{[Measures].[Unit Sales]} on columns ,
{[Customers].[City].members} on rows ,
{[Product].[Brand Name].members} on pages
FROM
[Sales]
CELL PROPERTIES
VALUE, FORMATTED_VALUE, FORMAT_STRING, FORE_COLOR, BACK_COLOR,
FONT_FLAGS</CommandText></Query></DataSet><DataSet
Name="ParameterQuery0"><Fields><Field
Name="ParaLevelField0"><DataField>[Customers].[State
Province].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ParamNullField0"><DataField>[Measures].[NullColumn]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH
MEMBER Measures.NullColumn AS 'Null' SELECT {Measures.NullColumn} ON
COLUMNS, { [Customers].[State Province].Members } ON ROWS From
Sales</CommandText></Query></DataSet><DataSet
Name="ParameterQuery1"><Fields><Field
Name="ParaLevelField1"><DataField>[Product].[Product
Family].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ParamNullField1"><DataField>[Measures].[NullColumn]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH
MEMBER Measures.NullColumn AS 'Null' SELECT {Measures.NullColumn} ON
COLUMNS, { [Product].[Product Family].Members } ON ROWS From
Sales</CommandText></Query></DataSet></DataSets><LeftMargin>1in</LeftMargin><SnapToGrid
xmlns="true</SnapToGrid><PageHeight>12in</PageHeight><DrawGrid">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">true</SnapToGrid><PageHeight>12in</PageHeight><DrawGrid
xmlns="true</DrawGrid><PageWidth>8in</PageWidth><ReportID">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">true</DrawGrid><PageWidth>8in</PageWidth><ReportID
xmlns="7B383EFA-88B0-41CB-987B-3F766C0F4776</ReportID><BottomMargin>1in</BottomMargin><ReportParameters><ReportParameter">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">7B383EFA-88B0-41CB-987B-3F766C0F4776</ReportID><BottomMargin>1in</BottomMargin><ReportParameters><ReportParameter
Name="Param0"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>ParameterQuery0</DataSetName><ValueField>ParaLevelField0</ValueField></DataSetReference></DefaultValue><Prompt>State
Province</Prompt><ValidValues><DataSetReference><DataSetName>ParameterQuery0</DataSetName><ValueField>ParaLevelField0</ValueField><LabelField>ParaLevelField0</LabelField></DataSetReference></ValidValues></ReportParameter><ReportParameter
Name="Param1"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>ParameterQuery1</DataSetName><ValueField>ParaLevelField1</ValueField></DataSetReference></DefaultValue><Prompt>Product
Family</Prompt><ValidValues><DataSetReference><DataSetName>ParameterQuery1</DataSetName><ValueField>ParaLevelField1</ValueField><LabelField>ParaLevelField1</LabelField></DataSetReference></ValidValues></ReportParameter></ReportParameters></Report>
Posted by instan at 7:40 AM
Labels: , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)