Ssrs report display customer data uniquely on each page

SSAS and SSRS
1

In a new ssrs 2008 report, I have the following requirements:

  1. Detailed Customer Information is suppose to print on one page. Basically each page will uniquely display detailed customer information. This is suppose to be the 'front side' of the paper.
  2. On the backside of the same paper, there will be a 'generic hardcoded' message that will be generated for each customer. On the backside of the same paper, there is about a 5% change that there will some unique information about each customer.

To accomplish this goal, I have done the following:

  1. Setup a rectangle.
  2. placed a table within the rectangle and setup a group by customer number and set a page break when the customer number changes.
  3. Setup a second table within the rectangle with a group by customer number.

However the above did not come up with the desired results of:

  1. Front page of report for customer number #1 followed by the back page of the the same or diferent report for customer number #1.
  2. Front page of report for customer number #2 followed by the back page of the the same or diferent report for customer number #2.

Thus would you tell me and/or point me to a url that will tell me how to accomplish my goal?

2

There may be better ways to do it but I have done that in the past with a single tablix. You do the grouping in that so you can control the breaking on the customer ID's. The funky part is using the rectangles within the rectangles in the tablix to do this.

Below is some RDL you can copy and create as a new report to see the example that I threw together. Just change the ConnectString value from MySQLServer to something you have local. I just used tempdb because I am creating a table variable and not persisting any actual tables.

Basically you:

  1. Create a tablix.
  2. Group on the customer number
  3. Drop a rectangle in the details section.
  4. Drop another rectangle in the details section (this will be the "front" page.
  5. Add the data you need for the front page in that rectangle.
  6. Add a new rectangle below the first. This is the "back" page.
  7. Set the second rectangle to add a page break before
  8. Add in the contents for the "back" page.

Using the tablix to control the grouping is what makes this work for me. Again, I am sure someone has a better way of doing this but I thought I would share.

<?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>6.5in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>2.57167in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Rectangle Name="Rectangle1">
                      <ReportItems>
                        <Rectangle Name="Rectangle2">
                          <ReportItems>
                            <Textbox Name="CustomerName">
                              <CanGrow>true</CanGrow>
                              <KeepTogether>true</KeepTogether>
                              <Paragraphs>
                                <Paragraph>
                                  <TextRuns>
                                    <TextRun>
                                      <Value>=Fields!CustomerName.Value</Value>
                                      <Style />
                                    </TextRun>
                                  </TextRuns>
                                  <Style />
                                </Paragraph>
                              </Paragraphs>
                              <rd:DefaultName>CustomerName</rd:DefaultName>
                              <Top>0.29167in</Top>
                              <Left>1.45833in</Left>
                              <Height>0.25in</Height>
                              <Width>2.22917in</Width>
                              <Style>
                                <Border>
                                  <Style>None</Style>
                                </Border>
                                <PaddingLeft>2pt</PaddingLeft>
                                <PaddingRight>2pt</PaddingRight>
                                <PaddingTop>2pt</PaddingTop>
                                <PaddingBottom>2pt</PaddingBottom>
                              </Style>
                            </Textbox>
                            <Textbox Name="Textbox14">
                              <CanGrow>true</CanGrow>
                              <KeepTogether>true</KeepTogether>
                              <Paragraphs>
                                <Paragraph>
                                  <TextRuns>
                                    <TextRun>
                                      <Value>Front Page for:</Value>
                                      <Style />
                                    </TextRun>
                                  </TextRuns>
                                  <Style />
                                </Paragraph>
                              </Paragraphs>
                              <rd:DefaultName>Textbox14</rd:DefaultName>
                              <Top>0.29167in</Top>
                              <Left>0.20833in</Left>
                              <Height>0.25in</Height>
                              <Width>1in</Width>
                              <ZIndex>1</ZIndex>
                              <Style>
                                <Border>
                                  <Style>None</Style>
                                </Border>
                                <PaddingLeft>2pt</PaddingLeft>
                                <PaddingRight>2pt</PaddingRight>
                                <PaddingTop>2pt</PaddingTop>
                                <PaddingBottom>2pt</PaddingBottom>
                              </Style>
                            </Textbox>
                          </ReportItems>
                          <KeepTogether>true</KeepTogether>
                          <Top>0.26958in</Top>
                          <Left>1.16542in</Left>
                          <Height>1in</Height>
                          <Width>4in</Width>
                          <Style>
                            <Border>
                              <Style>None</Style>
                            </Border>
                          </Style>
                        </Rectangle>
                        <Rectangle Name="Rectangle3">
                          <ReportItems>
                            <Textbox Name="Textbox15">
                              <CanGrow>true</CanGrow>
                              <KeepTogether>true</KeepTogether>
                              <Paragraphs>
                                <Paragraph>
                                  <TextRuns>
                                    <TextRun>
                                      <Value>Back Page for:</Value>
                                      <Style />
                                    </TextRun>
                                  </TextRuns>
                                  <Style />
                                </Paragraph>
                              </Paragraphs>
                              <rd:DefaultName>Textbox14</rd:DefaultName>
                              <Top>0.1875in</Top>
                              <Left>0.20833in</Left>
                              <Height>0.25in</Height>
                              <Width>1in</Width>
                              <Style>
                                <Border>
                                  <Style>None</Style>
                                </Border>
                                <PaddingLeft>2pt</PaddingLeft>
                                <PaddingRight>2pt</PaddingRight>
                                <PaddingTop>2pt</PaddingTop>
                                <PaddingBottom>2pt</PaddingBottom>
                              </Style>
                            </Textbox>
                            <Textbox Name="CustomerName2">
                              <CanGrow>true</CanGrow>
                              <KeepTogether>true</KeepTogether>
                              <Paragraphs>
                                <Paragraph>
                                  <TextRuns>
                                    <TextRun>
                                      <Value>=Fields!CustomerName.Value</Value>
                                      <Style />
                                    </TextRun>
                                  </TextRuns>
                                  <Style />
                                </Paragraph>
                              </Paragraphs>
                              <rd:DefaultName>CustomerName</rd:DefaultName>
                              <Top>0.1875in</Top>
                              <Left>1.45833in</Left>
                              <Height>0.25in</Height>
                              <Width>2.22917in</Width>
                              <ZIndex>1</ZIndex>
                              <Style>
                                <Border>
                                  <Style>None</Style>
                                </Border>
                                <PaddingLeft>2pt</PaddingLeft>
                                <PaddingRight>2pt</PaddingRight>
                                <PaddingTop>2pt</PaddingTop>
                                <PaddingBottom>2pt</PaddingBottom>
                              </Style>
                            </Textbox>
                          </ReportItems>
                          <PageBreak>
                            <BreakLocation>Start</BreakLocation>
                          </PageBreak>
                          <KeepTogether>true</KeepTogether>
                          <Top>1.26958in</Top>
                          <Left>1.16542in</Left>
                          <Height>1in</Height>
                          <Width>4in</Width>
                          <ZIndex>1</ZIndex>
                          <Style>
                            <Border>
                              <Style>None</Style>
                            </Border>
                          </Style>
                        </Rectangle>
                      </ReportItems>
                      <KeepTogether>true</KeepTogether>
                      <Style>
                        <Border>
                          <Style>None</Style>
                        </Border>
                      </Style>
                    </Rectangle>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="CustomerId">
                <GroupExpressions>
                  <GroupExpression>=Fields!CustomerId.Value</GroupExpression>
                </GroupExpressions>
                <PageBreak>
                  <BreakLocation>Between</BreakLocation>
                </PageBreak>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!CustomerId.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixMembers>
                <TablixMember>
                  <Group Name="Details" />
                </TablixMember>
              </TablixMembers>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet1</DataSetName>
        <Height>2.57167in</Height>
        <Width>6.5in</Width>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Tablix>
    </ReportItems>
    <Height>2.57167in</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">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=MySQLServer;Initial Catalog=tempdb</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>acdf39c0-d165-4053-8240-595d4950d117</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>DECLARE @CustomerTable TABLE
(
	CustomerId		INT NOT NULL PRIMARY KEY,
	CustomerName	VARCHAR(100) NOT NULL
);

INSERT INTO @CustomerTable (CustomerId, CustomerName)
VALUES
	(1, 'Test Customer 1'),
	(2, 'Test Customer 2'),
	(3, 'Test Customer 3');

SELECT * FROM @CustomerTable;</CommandText>
      </Query>
      <Fields>
        <Field Name="CustomerId">
          <DataField>CustomerId</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="CustomerName">
          <DataField>CustomerName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>8aa40cfe-b404-4704-8bc3-ebd830c4e48c</rd:ReportID>
</Report>
1 Like
3

This worked very well! Thank you very much!