Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Page breaks and Grouping

Author  Topic 

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2012-12-06 : 13:25:08
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, Detail
FROM TestGrouping
WHERE (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 Hospital
From 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>



Larry

Everyday life brings me back to reality
   

- Advertisement -