Monday, March 19, 2012

Bug when a table is adjacent to a matrix

I am trying to create a report that has a table adjacent to a matrix, with
the tops of each aligned together. I am trying to create a report where the
rows of the table and the matrix "line up" with each other. I have included
an example below where I have selected the customerID, orderID, and shipper
name from the rows of the Northwind orders table. I'd like to put the
customerID and orderID in a table that is adjacent to a matrix where each
column is a particular shipper. I'd like each row of this combination to
show the customerId, orderID with the shipper name in the appropriate
column.
HOWEVER -- When I render this report in HTML, the matrix terminates and
continues to the next page before the table does, causing the two to get out
of sync on the second page. I can't figure out a way to make the table and
the matrix stay aligned from the first page onto the second.
The report below should duplicate this behavior (I assume that you have the
Northwind DB installed).
Thanks,
Michael Carr
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>0.25in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CustomerID_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>1.875in</Width>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
</Table>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID, OrderID</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.375in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CompanyName_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CompanyName_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.25in</Width>
<Left>2in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_CustomerID">
<GroupExpressions>
<GroupExpression>=Fields!CustomerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>0.25in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT
OrderID,
Orders.CustomerID,
Shippers.CompanyName FROM Orders
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>0.25in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
<BottomMargin>0.25in</BottomMargin>
<Language>en-US</Language>
</Report>This looks like a bug to me. I've let the dev team know. Thanks.
Possible workaround: Add explicit page breaks every N rows by adding an
outer grouping by =Ceiling(RowNumber(Nothing)/20)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:%23hRajEjiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> I am trying to create a report that has a table adjacent to a matrix, with
> the tops of each aligned together. I am trying to create a report where
the
> rows of the table and the matrix "line up" with each other. I have
included
> an example below where I have selected the customerID, orderID, and
shipper
> name from the rows of the Northwind orders table. I'd like to put the
> customerID and orderID in a table that is adjacent to a matrix where each
> column is a particular shipper. I'd like each row of this combination to
> show the customerId, orderID with the shipper name in the appropriate
> column.
> HOWEVER -- When I render this report in HTML, the matrix terminates and
> continues to the next page before the table does, causing the two to get
out
> of sync on the second page. I can't figure out a way to make the table and
> the matrix stay aligned from the first page onto the second.
> The report below should duplicate this behavior (I assume that you have
the
> Northwind DB installed).
> Thanks,
> Michael Carr
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>0.25in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.5in</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Order ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>CustomerID_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="OrderID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>OrderID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>1.875in</Width>
> <TableColumns>
> <TableColumn>
> <Width>1in</Width>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID, OrderID</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CompanyName.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.25in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>1.375in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>DataSet1</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_CompanyName">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="CompanyName_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>CompanyName_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>3.25in</Width>
> <Left>2in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_CustomerID">
> <GroupExpressions>
> <GroupExpression>=Fields!CustomerID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>0.875in</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_OrderID">
> <GroupExpressions>
> <GroupExpression>=Fields!OrderID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>3in</Height>
> </Body>
> <TopMargin>0.25in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>8in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT
> OrderID,
> Orders.CustomerID,
> Shippers.CompanyName FROM Orders
> INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>0.25in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
> <BottomMargin>0.25in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>
Posted by Clay at 5:35 AM
Labels: , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)