Hi Everyone,I need to add a page after the 4th Patient in a report.The report has two groups. Hospital and patient.I've added =CInt(Ceiling(RowNumber(Nothing)/4)) to the "Hospital" group and this will work perfectly if only one hospital is picked. If I have 2 or more hospitals the page breaks get all messed up. I'm attaching a dummy data table and .rdl file, in the hopes that someone can guide me in the right direction.TableData:Create Table TestGrouping(Hospital varchar(10), Patient varchar(10), Detail varchar (10))Insert into TestGrouping Values ('Hospital1', 'Patient1', 'Detail1')Insert into TestGrouping Values ('Hospital1', 'Patient2', 'Detail2')Insert into TestGrouping Values ('Hospital1', 'Patient3', 'Detail3')Insert into TestGrouping Values ('Hospital1', 'Patient4', 'Detail4')Insert into TestGrouping Values ('Hospital1', 'Patient5', 'Detail5')Insert into TestGrouping Values ('Hospital2', 'Patient1', 'Detail1')Insert into TestGrouping Values ('Hospital2', 'Patient2', 'Detail2')Insert into TestGrouping Values ('Hospital2', 'Patient3', 'Detail3')Insert into TestGrouping Values ('Hospital2', 'Patient4', 'Detail4')Insert into TestGrouping Values ('Hospital2', 'Patient5', 'Detail5')Insert into TestGrouping Values ('Hospital3', 'Patient1', 'Detail1')Insert into TestGrouping Values ('Hospital3', 'Patient2', 'Detail2')Insert into TestGrouping Values ('Hospital3', 'Patient3', 'Detail3')Insert into TestGrouping Values ('Hospital3', 'Patient4', 'Detail4')Insert into TestGrouping Values ('Hospital3', 'Patient5', 'Detail5')Select * From TestGrouping--Drop Table TestGrouping
The .rdl (xml) <?xml version="1.0" encoding="utf-8"?><Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Tablix Name="Tablix1"> <TablixBody> <TablixColumns> <TablixColumn> <Width>0.80903in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.09375in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox5"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Detail</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox5</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.14583in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox18"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox18</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.14583in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox12"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox12</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.09375in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Detail"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Detail.Value</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Detail</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.11458in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox21"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Extra Detail</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox21</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.08333in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox25"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Extra Detail</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox25</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.09375in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox24"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Extra Detail</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox24</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <TablixHeader> <Size>1in</Size> <CellContents> <Textbox Name="Textbox13"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Hospital</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox13</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <TablixHeader> <Size>1in</Size> <CellContents> <Textbox Name="Textbox7"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Patient</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox7</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Hospital"> <GroupExpressions> <GroupExpression>=Fields!Hospital.Value</GroupExpression> <GroupExpression>=CInt(Ceiling((RowNumber(Nothing)/4)))</GroupExpression> </GroupExpressions> <PageBreak> <BreakLocation>Between</BreakLocation> </PageBreak> </Group> <SortExpressions> <SortExpression> <Value>=Fields!Hospital.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>1in</Size> <CellContents> <Textbox Name="Hospital"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Hospital.Value</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Hospital</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <TablixHeader> <Size>1in</Size> <CellContents> <Textbox Name="Textbox17"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox17</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Patient"> <GroupExpressions> <GroupExpression>=Fields!Patient.Value</GroupExpression> </GroupExpressions> </Group> <SortExpressions> <SortExpression> <Value>=Fields!Patient.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>1in</Size> <CellContents> <Textbox Name="Patient"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Patient.Value</Value> <Style> <FontSize>8pt</FontSize> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Patient</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Details"> <GroupExpressions> <GroupExpression>=Fields!Detail.Value</GroupExpression> </GroupExpressions> </Group> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSet1</DataSetName> <Top>0.33208in</Top> <Left>0.125in</Left> <Height>0.77083in</Height> <Width>2.80903in</Width> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>2in</Height> <Style /> </Body> <Width>6.5in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="DataSource1"> <DataSourceReference>iTransplant</DataSourceReference> <rd:SecurityType>None</rd:SecurityType> <rd:DataSourceID>cad341f9-900e-4911-8bb7-5bd148764ce2</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="DataSet1"> <Query> <DataSourceName>DataSource1</DataSourceName> <QueryParameters> <QueryParameter Name="@Hospital"> <Value>=Parameters!Hospital.Value</Value> </QueryParameter> </QueryParameters> <CommandText>SELECT Hospital, Patient, DetailFROM TestGroupingWHERE (Hospital IN (@Hospital))</CommandText> </Query> <Fields> <Field Name="Hospital"> <DataField>Hospital</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Patient"> <DataField>Patient</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Detail"> <DataField>Detail</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="DataSet2"> <Query> <DataSourceName>DataSource1</DataSourceName> <CommandText>Select Distinct HospitalFrom TestGrouping</CommandText> </Query> <Fields> <Field Name="Hospital"> <DataField>Hospital</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> <ReportParameters> <ReportParameter Name="Hospital"> <DataType>String</DataType> <Prompt>Hospital</Prompt> <ValidValues> <DataSetReference> <DataSetName>DataSet2</DataSetName> <ValueField>Hospital</ValueField> <LabelField>Hospital</LabelField> </DataSetReference> </ValidValues> <MultiValue>true</MultiValue> </ReportParameter> </ReportParameters> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>07bb234c-442a-453e-8b42-4487494b7a87</rd:ReportID></Report>
LarryEveryday life brings me back to reality