SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Page breaks and Grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LarryC74
Yak Posting Veteran

94 Posts

Posted - 12/06/2012 :  13:25:08  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.73 seconds. Powered By: Snitz Forums 2000