I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.
Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.
The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).
As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.
There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.
Regards
John Burns
John,
You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.
You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.
Thanks!
|||
You could in SQL 2000!
I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression
e.g.
=IIf(1=1, false, true)
against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.
Try this example report which queries sysobjects in the master database (change the data source first)
<?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="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<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.91429cm</Height>
<Value>Report1</Value>
</Textbox>
<Table Name="table1">
<DataSetName>DataSource1</DataSetName>
<Top>0.91429cm</Top>
<Visibility>
<Hidden>=IIf(1=1, false, true)</Hidden>
</Visibility>
<Width>5.07936cm</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="name">
<DataElementOutput>Output</DataElementOutput>
<rd:DefaultName>name</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!name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="id">
<DataElementOutput>Output</DataElementOutput>
<rd:DefaultName>id</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>=Fields!id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</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>name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<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>id</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Height>2.00635cm</Height>
</Body>
<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DataSource1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from sysobjects</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>name</DataField>
</Field>
<Field Name="id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>id</DataField>
</Field>
<Field Name="xtype">
<rd:TypeName>System.String</rd:TypeName>
<DataField>xtype</DataField>
</Field>
<Field Name="uid">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>uid</DataField>
</Field>
<Field Name="info">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>info</DataField>
</Field>
<Field Name="status">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>status</DataField>
</Field>
<Field Name="base_schema_ver">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>base_schema_ver</DataField>
</Field>
<Field Name="replinfo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>replinfo</DataField>
</Field>
<Field Name="parent_obj">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>parent_obj</DataField>
</Field>
<Field Name="crdate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>crdate</DataField>
</Field>
<Field Name="ftcatid">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ftcatid</DataField>
</Field>
<Field Name="schema_ver">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>schema_ver</DataField>
</Field>
<Field Name="stats_schema_ver">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>stats_schema_ver</DataField>
</Field>
<Field Name="type">
<rd:TypeName>System.String</rd:TypeName>
<DataField>type</DataField>
</Field>
<Field Name="userstat">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>userstat</DataField>
</Field>
<Field Name="sysstat">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>sysstat</DataField>
</Field>
<Field Name="indexdel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>indexdel</DataField>
</Field>
<Field Name="refdate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>refdate</DataField>
</Field>
<Field Name="version">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>version</DataField>
</Field>
<Field Name="deltrig">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>deltrig</DataField>
</Field>
<Field Name="instrig">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>instrig</DataField>
</Field>
<Field Name="updtrig">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>updtrig</DataField>
</Field>
<Field Name="seltrig">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>seltrig</DataField>
</Field>
<Field Name="category">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>category</DataField>
</Field>
<Field Name="cache">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>cache</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>12.69841cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>
No comments:
Post a Comment