Hi,
Any ideas on how to split data from a row set over two columns in a list box or table?
For example I have a data set with 10 rows and I want to display 5 rows in one column and the other 5 in the second column.
I tried creating two tables and filtering the first table to only show the even rows and the second to show odd rows. However the RowNumber(Nothing) function is not allowed in filters - 'RowNumber cannot be used in filters'
Thanks
KevinCould you use a multi-column report and limt the number of rows in each
column?
The major restriction is that you must use a "paged" rendered (PDF, Print
Preview) to see the multiple columns. Following your original posting is a
sample RDL that shows how this is done.
Note the table group expression.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> Hi,
> Any ideas on how to split data from a row set over two columns in a list
box or table?
> For example I have a data set with 10 rows and I want to display 5 rows in
one column and the other 5 in the second column.
> I tried creating two tables and filtering the first table to only show the
even rows and the second to show odd rows. However the RowNumber(Nothing)
function is not allowed in filters - 'RowNumber cannot be used in filters'
> Thanks
> Kevin
--
Sample Multi-Column Report - 5 records per column
<?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">
<PageHeader>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Franklin Gothic Medium</FontFamily>
<FontSize>12pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.11458in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.25in</Height>
<Width>3in</Width>
<CanGrow>true</CanGrow>
<Value>Sample Multiple Column Report</Value>
<Left>1.5in</Left>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.5in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>11pt</FontSize>
<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>Company Info</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<rd:DefaultName>Country</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.75in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.5in</Height>
<Columns>2</Columns>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT CompanyName, City, Country
FROM Customers</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
<PageFooter>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Top>0.125in</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>2in</Width>
<CanGrow>true</CanGrow>
<Value>="Page " & Globals!PageNumber</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.375in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Kevin,
Did you get your last idea implemented in code? If so, would you share it
with me?
Thanks,
Ming
"Kevin Wilson" wrote:
> Thanks for your time on this Bruce. The multi coloumn report works fine but it isn't really appropriate in this case as I need to view the data rendered in HTML.
> My last idea is to change the the stored proc so each row is identified with a unique number. Create two tables each with a filter - table1 returns odd rows Fields!ID_NUM MOD 2 = 1 and table 2 returns even rows.
> Cheers
> Kevin
> "Bruce Johnson [MSFT]" wrote:
> > Could you use a multi-column report and limt the number of rows in each
> > column?
> > The major restriction is that you must use a "paged" rendered (PDF, Print
> > Preview) to see the multiple columns. Following your original posting is a
> > sample RDL that shows how this is done.
> > Note the table group expression.
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:8512662A-BEDB-4194-8072-03CFFC7E8736@.microsoft.com...
> > > Hi,
> > > Any ideas on how to split data from a row set over two columns in a list
> > box or table?
> > > For example I have a data set with 10 rows and I want to display 5 rows in
> > one column and the other 5 in the second column.
> > > I tried creating two tables and filtering the first table to only show the
> > even rows and the second to show odd rows. However the RowNumber(Nothing)
> > function is not allowed in filters - 'RowNumber cannot be used in filters'
> > > Thanks
> > > Kevin
> >
> > --
> >
> > Sample Multi-Column Report - 5 records per column
> >
> > <?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">
> > <PageHeader>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontFamily>Franklin Gothic Medium</FontFamily>
> > <FontSize>12pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.11458in</Top>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <Height>0.25in</Height>
> > <Width>3in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Sample Multiple Column Report</Value>
> > <Left>1.5in</Left>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.5in</Height>
> > </PageHeader>
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>11pt</FontSize>
> > <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>Company Info</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > <RepeatOnNewPage>true</RepeatOnNewPage>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="CompanyName">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>CompanyName</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!CompanyName.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="City">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>City</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!City.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Country">
> > <rd:DefaultName>Country</rd:DefaultName>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Country.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>Northwind</DataSetName>
> > <TableGroups>
> > <TableGroup>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> >
> > <GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
> > >
> > </GroupExpressions>
> > <PageBreakAtEnd>true</PageBreakAtEnd>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>2.75in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>1.5in</Height>
> > <Columns>2</Columns>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="Northwind">
> >
> > <rd:DataSourceID>7bec244d-832e-4036-9143-257dd0f8dcaa</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=localhost;initial
> > catalog=Northwind</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>2.75in</Width>
> > <DataSets>
> > <DataSet Name="Northwind">
> > <Fields>
> > <Field Name="CompanyName">
> > <DataField>CompanyName</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="City">
> > <DataField>City</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="Country">
> > <DataField>Country</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>Northwind</DataSourceName>
> > <CommandText>SELECT CompanyName, City, Country
> > FROM Customers</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7535e659-af87-485d-b94d-cb398b82610b</rd:ReportID>
> > <PageFooter>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <Top>0.125in</Top>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <Width>2in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>="Page " & Globals!PageNumber</Value>
> > </Textbox>
> > </ReportItems>
> > <PrintOnLastPage>true</PrintOnLastPage>
> > <PrintOnFirstPage>true</PrintOnFirstPage>
> > <Style />
> > <Height>0.375in</Height>
> > </PageFooter>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> >
> >
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment