2 sub reports show despite different criteria

Question

2 sub reports show despite different criteria

qutip

SSC Eights!

Points: 870
More actions
September 20, 2012 at 10:05 am

#267037

Hello I have a strange an infuriating problem.
I have a master report which displays 1 of 2 sub reports depending on a value.
Here's what I have in the hidden property for each sub report:
Sub report 1: =(Fields!field.Value <> 1)
Sub report 2: =(Fields!field.Value = 1)
The field is an int field and can have a value of 1 to 4.
The problem is that when this report renders to pdf it displays both sub reports. I've played around with various expressions to hide the fields and I get varied results ranging from 1 sub report only showing blank pages and the other displaying fine when I use the below expressions:
Sub report 1: =NOT(Fields!field.Value = 1)
Sub report 2: =NOT(Fields!field.Value = 2)
any help is greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

H4K Hall of Fame Points: 3374 More actions · Answer 1

Here's what I have in the hidden property for each sub report:
Sub report 1: =(Fields!field.Value <> 1)
Sub report 2: =(Fields!field.Value = 1)
The field is an int field and can have a value of 1 to 4.

You should write the expression in the hidden property of the subreport as follows:

=IIF(Fields!field.Value <> 1,True,False)

=IIF(Fields!field.Value=1,True,False)

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

qutip SSC Eights! Points: 870 More actions · Answer 2

Hi, thanks for the response.

Just to clarify, in the properties of the sub report .rdl files or the sub report properties within the master report?

The reason I ask is that I think I've already tried that within the sub report report properties on the master report and I'm hoping it's within the .rdl file. I'd much rather clout myself for being stupid than have this infuriating issue carry on.

H4K Hall of Fame Points: 3374 More actions · Answer 3

It's set in the subreport properties in the master report.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

qutip SSC Eights! Points: 870 More actions · Answer 4

Hi thanks for the response. As I thought I had actually tried that, which makes this all the more annoying.

I did try it again to make sure it wasn't a subtle error on my part but I still get the sub reports rendering on top of each other. I should note these reports automatically get converted to pdf. I also get a Error: Subreport could not be shown. error at the top of the pdf's

I should also note that this is 2008 SP3

Here's the xml, can anyone spot a reason why it's doing this?

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

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

<DataSources>

<DataSource Name="Spice_Gas_Liberate_CRM">

<DataSourceReference>LibertyReports</DataSourceReference>

<rd:DataSourceID>7228200b-9b68-400a-b763-aa6a35d00c7e</rd:DataSourceID>

</DataSource>

</DataSources>

<DataSets>

<DataSet Name="CRM">

<Fields>

<Field Name="JobID">

<DataField>JobID</DataField>

<rd:TypeName>System.Guid</rd:TypeName>

</Field>

<Field Name="PropertyID">

<DataField>PropertyID</DataField>

<rd:TypeName>System.Guid</rd:TypeName>

</Field>

<Field Name="InstructionID">

<DataField>InstructionID</DataField>

<rd:TypeName>System.Guid</rd:TypeName>

</Field>

<Field Name="InsID">

<DataField>InsID</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="OccupierTitle">

<DataField>OccupierTitle</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="OccupierForename">

<DataField>OccupierForename</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="OccupierSurname">

<DataField>OccupierSurname</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Addressline1">

<DataField>Addressline1</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Addressline2">

<DataField>Addressline2</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="City">

<DataField>City</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="County">

<DataField>County</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Postcode">

<DataField>Postcode</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Clientid">

<DataField>Clientid</DataField>

<rd:TypeName>System.Guid</rd:TypeName>

</Field>

<Field Name="ContractName">

<DataField>ContractName</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ContactName">

<DataField>ContactName</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="AppointmentDateandTime">

<DataField>AppointmentDateandTime</DataField>

<rd:TypeName>System.DateTime</rd:TypeName>

</Field>

<Field Name="AppointmentLetterPrinted">

<DataField>AppointmentLetterPrinted</DataField>

<rd:TypeName>System.Boolean</rd:TypeName>

</Field>

<Field Name="AppointmentLetterStage">

<DataField>AppointmentLetterStage</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ContractCode">

<DataField>ContractCode</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ClientName">

<DataField>ClientName</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>Spice_Gas_Liberate_CRM</DataSourceName>

<CommandType>StoredProcedure</CommandType>

<CommandText>rpt_Letter_Master</CommandText>

<QueryParameters>

<QueryParameter Name="@ISBLANK">

<Value>=IIF(Parameters!ContractList.Value(0) = "", "TRUE", "FALSE")</Value>

</QueryParameter>

<QueryParameter Name="@ContractList">

<Value>=Parameters!ContractList.Value</Value>

</QueryParameter>

<QueryParameter Name="@InstructionId">

<Value>=Parameters!InstructionId.Value</Value>

</QueryParameter>

</QueryParameters>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

</Query>

</DataSet>

</DataSets>

<Body>

<ReportItems>

<Tablix Name="list1">

<TablixBody>

<TablixColumns>

<TablixColumn>

<Width>8.03125in</Width>

</TablixColumn>

</TablixColumns>

<TablixRows>

<TablixRow>

<Height>9.5in</Height>

<TablixCells>

<TablixCell>

<CellContents>

<Rectangle Name="list1_Contents">

<ReportItems>

<Subreport Name="subreport2">

<ReportName>Letter2</ReportName>

<Parameters>

<Parameter Name="InstructionId">

<Value>=Fields!InsID.Value</Value>

</Parameter>

<Parameter Name="ContractCode">

<Value>=Fields!ContractCode.Value</Value>

</Parameter>

</Parameters>

<KeepTogether>true</KeepTogether>

<Height>24.13cm</Height>

<Width>17.78cm</Width>

<Visibility>

<Hidden>=IIF(Fields!AppointmentLetterStage.Value=1,True,False)</Hidden>

</Visibility>

<DataElementOutput>NoOutput</DataElementOutput>

<Style>

<Border />

</Style>

</Subreport>

<Subreport Name="subreport1">

<ReportName>Letter1</ReportName>

<Parameters>

<Parameter Name="InstructionId">

<Value>=Fields!InsID.Value</Value>

</Parameter>

<Parameter Name="ContractCode">

<Value>=Fields!ContractCode.Value</Value>

</Parameter>

</Parameters>

<KeepTogether>true</KeepTogether>

<Height>24.13cm</Height>

<Width>17.78cm</Width>

<ZIndex>1</ZIndex>

<Visibility>

<Hidden>=IIF(Fields!AppointmentLetterStage.Value <> 1,True,False)</Hidden>

</Visibility>

<DataElementOutput>NoOutput</DataElementOutput>

<Style>

<Border />

</Style>

</Subreport>

</ReportItems>

<KeepTogether>true</KeepTogether>

<Style />

</Rectangle>

</CellContents>

</TablixCell>

</TablixCells>

</TablixRow>

</TablixRows>

</TablixBody>

<TablixColumnHierarchy>

<TablixMembers>

<TablixMember />

</TablixMembers>

</TablixColumnHierarchy>

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<Group Name="list1_Details_Group">

<GroupExpressions>

<GroupExpression>=Fields!InsID.Value</GroupExpression>

</GroupExpressions>

<PageBreak>

<BreakLocation>Between</BreakLocation>

</PageBreak>

</Group>

<DataElementOutput>Output</DataElementOutput>

<KeepTogether>true</KeepTogether>

</TablixMember>

</TablixMembers>

</TablixRowHierarchy>

<PageBreak>

<BreakLocation>End</BreakLocation>

</PageBreak>

<Height>9.5in</Height>

<Width>8.03125in</Width>

<Style />

</Tablix>

</ReportItems>

<Height>24.13cm</Height>

<Style />

</Body>

<ReportParameters>

<ReportParameter Name="ContractList">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>=""</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>Contract List</Prompt>

<MultiValue>true</MultiValue>

</ReportParameter>

<ReportParameter Name="InstructionId">

<DataType>String</DataType>

<Nullable>true</Nullable>

<DefaultValue>

<Values>

<Value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

</Values>

</DefaultValue>

<Prompt>Instruction ID</Prompt>

</ReportParameter>

<ReportParameter Name="ISBLANK">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>="TRUE"</Value>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<Width>20.39938cm</Width>

<Page>

<PageHeight>26.67cm</PageHeight>

<PageWidth>19.05cm</PageWidth>

<InteractiveHeight>26.67cm</InteractiveHeight>

<InteractiveWidth>8in</InteractiveWidth>

<LeftMargin>1.27cm</LeftMargin>

<RightMargin>1.27cm</RightMargin>

<TopMargin>1.27cm</TopMargin>

<BottomMargin>1.27cm</BottomMargin>

<ColumnSpacing>0cm</ColumnSpacing>

<Style />

</Page>

<Language>en-US</Language>

<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>

<rd:ReportID>7741a740-8cc9-4768-b8dd-440ee25f08f5</rd:ReportID>

<rd:ReportUnitType>Cm</rd:ReportUnitType>

</Report>

H4K Hall of Fame Points: 3374 More actions · Answer 5

Maybe the value of "Fields!AppointmentLetterStage.Value" changes within the rows of the tablix.

Check for its value which maybe includes integers as 1,2,3,....

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

qutip SSC Eights! Points: 870 More actions · Answer 6

Hi, it's a bit value not an integer as I first thought.

However I thought I would be accounting for that with

sub report 1 as =IIF(Fields!AppointmentLetterStage.Value <> 1,True,False)

sub report 2 as =IIF(Fields!AppointmentLetterStage.Value=1,True,False)

?

Thanks for the help.

qutip SSC Eights! Points: 870 More actions · Answer 7

As a part time idiot i forgot to hit save before deploying. Your suggested fix worked.

Thank you very much.