Connect to a DataSet Variable as a Source?

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

MilwaukeeCharlie wrote:

Hello. I’m new to SSIS. This forum and Kirk Haselden’s book are my teachers. I’m having a hard time grasping something basic to get started defining a set of packages to automate the ETL process, however, and perhaps I’m simply misunderstanding the best practices of SSIS.

I have source data in two different transactional databases, and use OleDb connection managers (and OleDb Source components in the Data Flow) to extract the data. I use a Script Task and several Lookup widgets in the Data Flow to transform the data, and output each to two different package-scope DataSet variables.

How do I join these two datasets in a third Data Flow task for loading into my data warehouse? It seems I can iterate through them in the Control Flow, but I can’t write a query against them in the Data Flow, since there is no connection manager that allows me to “connect” to a package-level variable. Should I instead be storing my extracted, transformed data in temporary database tables, and then joining these to do the final load?

Any advice greatly appreciated. Thanks in advance.

Charlie,

You're absolutely correct, there is no source adapter for these and with good reason too.

There is a workaround though:

Recordsets instead of raw files

(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx)

Another option (and the one I would use for performance reasons as explained here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) is to use raw files

By the way, the correct nomenclature is "components", not "widgets"

Good luck!

-Jamie

Connect from Visual Basic 6 to SQL Server 2005 express

Hi people,
I downloaded sql server 2005 express and I installed it. I have Visual
Basic 6 application, that work with MS access with ado connection. This
connection give me seek and findfirst methods. I want migrate this access db
to sql sever 2005, but i don't make any change in vb6 code (only the
connection).
My first question is, can i connect from vb6 application to sql sever
2005 express? Moreover,how must i connect the application to sql sever and
work with seek and findfirst methods?
If it's possible, you will put a code example to connect...
Thanks!> Hi people,
> I downloaded sql server 2005 express and I installed it. I have Visual
> Basic 6 application, that work with MS access with ado connection. This
> connection give me seek and findfirst methods. I want migrate this access
db
> to sql sever 2005, but i don't make any change in vb6 code (only the
> connection).
> My first question is, can i connect from vb6 application to sql sever
> 2005 express? Moreover,how must i connect the application to sql sever
and
> work with seek and findfirst methods?
> If it's possible, you will put a code example to connect...
> Thanks!
--
You can't use SEEK and FINDFIRST methods against a SQL Server table. Those
methods will only work against an Access Jet database.
You need to use T-SQL statements such as SELECT...WHERE to accomplish
similar results. Code changes are inevitable.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect from Visual Basic 6 to SQL Server 2005 express

Hi people,
I downloaded sql server 2005 express and I installed it. I have Visual
Basic 6 application, that work with MS access with ado connection. This
connection give me seek and findfirst methods. I want migrate this access db
to sql sever 2005, but i don't make any change in vb6 code (only the
connection).
My first question is, can i connect from vb6 application to sql sever
2005 express? Moreover,how must i connect the application to sql sever and
work with seek and findfirst methods?
If it's possible, you will put a code example to connect...
Thanks!

> Hi people,
> I downloaded sql server 2005 express and I installed it. I have Visual
> Basic 6 application, that work with MS access with ado connection. This
> connection give me seek and findfirst methods. I want migrate this access
db
> to sql sever 2005, but i don't make any change in vb6 code (only the
> connection).
> My first question is, can i connect from vb6 application to sql sever
> 2005 express? Moreover,how must i connect the application to sql sever
and
> work with seek and findfirst methods?
> If it's possible, you will put a code example to connect...
> Thanks!
You can't use SEEK and FINDFIRST methods against a SQL Server table. Those
methods will only work against an Access Jet database.
You need to use T-SQL statements such as SELECT...WHERE to accomplish
similar results. Code changes are inevitable.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect / use SQL server in C/C++?

I would like to know what is the most basic (I don't mean easy) way to connect and use an SQL (and/or Oracle...) database using C/C++, can anyone point me in a direction please? I simply cannot find any sort of API on the internet!

Don't database providers other than mySQL want people programming apps that access them?

Thanks in advance!!

The three ways to talk to a database using C/C++ are ODBC, OLEDB and ADO (the last is much more commonly done from VB and ASP than from C++).

If you're comfortable with programming ATL, OLEDB is probably the way to go (less code to write than ODBC).

Microsoft ships two implementations of both OLEDB and ODBC. If you intend to write an app that talks to SQL Server only, you'll want to use the SQL Native Client implementations (there will contain additional features and bugfixes vs. MDAC). You can find details about programming SQL Native Client here:

http://msdn2.microsoft.com/en-us/library/ms130904(SQL.90).aspx

If you need the flexibility of communicating with data sources other than SQL server, the MDAC implementations will allow you to do that. You can find details about OLEDB here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/dasdkoledboverview.asp

and ODBC here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview.asp

Hope this helps!

--John

Confusing data error

I have a gridview that is very basic and list name, address, ......, and a URL.

If I have this url in the database I have no problemhttp://www.lubbockisd.org/earlychildhood/

yet when I place this URLhttp://www.lubbockisd.org/erlychildhood/stubbs.htm

It triggers this error on my localhost server and my production server.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I've made changes to datatypes with no success, made sure my length of data type was set long enough with no success.

Which datatype is best for a URL in sql express?

Any ideas why this happens with such a minor change?

Thanks

Tony

Hi. Tha dataTable you are using as Datasource for the gridview has a unique constraint in the column you are changing tha data, or the column is defined as primary key... and you are attempting to insert a data that is not unique|||

You may have already done all this but I just want to make sure that that URL is not already present in the database. Other than that, what are the constraints on your fields. Does the URL field accepts only unique values.

bullpit

|||

Thanks for replying so quickly,

That is what is confusing me so much, I don't have any constraints on this field, nor is it a unique or identifier field.

Tony

|||

Hi Tony,

Is there any other table that reference this field in the DataSet?

|||

Nope, this is the only page that uses this particular table...http://www.tonyhager.com/schools/prek.aspx

TH

|||

Hi,

Please check each column that you have constraints on this table. If it has a unique constraint, make sure it is not violated with a duplicated value.

A VarChar data type will be suitable to store URLs.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

As stated previously, I have no constraints in this table. You can see that I have the URL duplicated on the page in question in the table with all three of the PreK schools listed. I have 5 pages that use this data table and none of the other pages trigger the error even when I have the URL that does trigger the error on the PreK page the other pages work fine. It only happens when I add the page name to the one URL. The default page ishttp://www.tonyhager.com/schools/

Thanks again,

TH

|||

Hi,

To troubleshoot on this issue, if you're using a DataSet to store the values, I suggest you use WriteXml("filename.xml", XmlWriteMode.WriteSchema) to write all the data and schema out to an xml file.

Analysis on this file will reveal where the constraint error is. If you're having problem, you can paste it here together with the new data you're inserting to this DataSet, and I will try to check this for you.

|||try checking your XML schema designer page and see if any of your fields have theNullValue property set to "(Throw exception)". If so, then try changing it to null.|||

Would I even have a XML schema page if I am using a sql express database? Where do I look for this page?

|||the XSD schema is generated if you "drag and drop" your tables. It creates strongly typed objects through which you can manage your scheme via the designer's interface.

if a schema has been generated it is normally generated under your "App_Code" folder and the name ends in "XSD". If you don't have a schema then you may want to consider this approach for creating strongly typed datasets in the future. It is much quicker and less error prone.|||Every field had it set to "throw exception" is that a default setting? So I should change each string field to read null?|||

only change the "offending" fields to null (unless you don't mind your other fields accepting null values as well).

the dataset schema acts as a second 'safety mechanism' to catch data-type mismatches, differences in column names, null values etc. It is a good thing but it can cause confusion sometimes because it adds an extra layer of complexity

Confused.

Still little bit confused regarding Statistics updates and rebuilding
indexes.... can any one give me one basic difference.
I remember you, you asked few days ago:
http://groups.google.de/group/micros...0354ae6af6fdbc
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:%23MNB6NZVFHA.2272@.TK2MSFTNGP15.phx.gbl...
> Still little bit confused regarding Statistics updates and rebuilding
> indexes.... can any one give me one basic difference.
>
|||On Tue, 10 May 2005 15:07:43 -0400, Joh wrote:

>Still little bit confused regarding Statistics updates and rebuilding
>indexes.... can any one give me one basic difference.
>
Hi Joh,
Take a text book, or an SQL manual or something like that. The last
20-50 pages will probably contain an index, that you can use to find
that deadlocks are discussed on pages 574-577 and 893, and that the
SELECT statement is discussed on pages 34-108, 145-160, 254 and 664.
Tear out this index. Then, take a stack of blank paper, scan each page
of the text book and recreate the index. This activity is somewhat
equivalent to rebuilding an index.
Now, take a quick scan over the index. You might note that there are
less (or maybe more?) words in the index that start with 'A' then with
'B'. You'll probably note that there are almost no words with 'X', but
quite a lot starting with 'Q'. Assuming that you don't forget this
information, but use it next time you need to find an entry to locate
the correct page in the index quicker, then you have just performed the
equivalent of updating statistics.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Really appreciated Hugo... thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rd32819sm6p33mfnhk6l62eb03hc8pun25@.4ax.com...
> On Tue, 10 May 2005 15:07:43 -0400, Joh wrote:
>
> Hi Joh,
> Take a text book, or an SQL manual or something like that. The last
> 20-50 pages will probably contain an index, that you can use to find
> that deadlocks are discussed on pages 574-577 and 893, and that the
> SELECT statement is discussed on pages 34-108, 145-160, 254 and 664.
> Tear out this index. Then, take a stack of blank paper, scan each page
> of the text book and recreate the index. This activity is somewhat
> equivalent to rebuilding an index.
> Now, take a quick scan over the index. You might note that there are
> less (or maybe more?) words in the index that start with 'A' then with
> 'B'. You'll probably note that there are almost no words with 'X', but
> quite a lot starting with 'Q'. Assuming that you don't forget this
> information, but use it next time you need to find an entry to locate
> the correct page in the index quicker, then you have just performed the
> equivalent of updating statistics.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Jens... really appreciated.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OwbMTXZVFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I remember you, you asked few days ago:
>
http://groups.google.de/group/micros...ts/browse_frm/
thread/17a8f88d35626ce5/4b0354ae6af6fdbc?q=Jens+S%C3%BC%C3%9Fmeyer+differe nc
e+statistics+index&rnum=1&hl=de#4b0354ae6af6fdbc
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:%23MNB6NZVFHA.2272@.TK2MSFTNGP15.phx.gbl...
>

COnfiguring Number of records per page?

Is there a way I can specify how many records per page should be displayed?> This is a very basic functionality that needs to go in the deployment at any cost for us?...any ideas.

thanksAssuming you display your data in a list or a table, you could add a grouping expression like this: =Ceiling(RowNumber(Nothing)/20)
This will cause the list/table to group on every 20 rows. On the grouping dialog, select to add a page break on every group and you should get the desired result.

-- Robert|||Thanks Robert. This is what happened:

If there were 60 records returned, I got 3 pages (which i want) but on every page you could only see the top record?

I clicked

List --> Properties >Edit Details Group

In the Group On dialog box, I inserted :
=Ceiling(RowNumber(Nothing)/20)

Any ideas on that?

Thanks.|||

Instead of a list, use a table and add a grouping the same way. The table detail rows should then show 20 records each.

-- Robert

|||How about in a Matrix?|||hi there. i encountered the same problem, and i tired doing what you said, applying it to a table.

however, it does the same thing. it only displays the first record out of 20|||

Here's a working example. The attached report has a self contained dataset and the same table twice, once using a list control with groupping applied. Hope this helps.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<Left>10cm</Left>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
<Height>3.1746cm</Height>
</Table>
<List Name="list1">
<Left>2.25cm</Left>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance_1">
<rd:DefaultName>balance_1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance_1">
<rd:DefaultName>prior_balance_1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet1</DataSetName>
<Top>0.75cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="account">
<rd:DefaultName>account</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance">
<rd:DefaultName>balance</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance">
<rd:DefaultName>prior_balance</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Top>0.5cm</Top>
<Width>6.5cm</Width>
<Grouping Name="list1_Details_Group">
<GroupExpressions>
<GroupExpression>=Ceiling(RowNumber(Nothing)/2)</GroupExpression>
</GroupExpressions>
</Grouping>
<Height>3.9246cm</Height>
</List>
</ReportItems>
<Height>5.4246cm</Height>
</Body>
<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance
union
select 'B', 2, 4, 8
union
select 'C', 1, 7, 6
union
select 'D', 2, 9, 12</CommandText>
<DataSourceName>master</DataSourceName>
</Query>
<Fields>
<Field Name="account">
<rd:TypeName>System.String</rd:TypeName>
<DataField>account</DataField>
</Field>
<Field Name="type">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>type</DataField>
</Field>
<Field Name="balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>balance</DataField>
</Field>
<Field Name="prior_balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>prior_balance</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16.5cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

|||

Hi.

I have a report containing a table and a subreport following the table. If there is enough space (if the table has less records at that page), I want the table and the subreport to be on the same page.But if there is not enough space , I want the following subreport to be on the next page.

How can I do this?

Note: The subreport may only be on the last page.

Any help would be appreciated.

COnfiguring Number of records per page?

Is there a way I can specify how many records per page should be displayed?> This is a very basic functionality that needs to go in the deployment at any cost for us?...any ideas.

thanksAssuming you display your data in a list or a table, you could add a grouping expression like this: =Ceiling(RowNumber(Nothing)/20)
This will cause the list/table to group on every 20 rows. On the grouping dialog, select to add a page break on every group and you should get the desired result.

-- Robert|||Thanks Robert. This is what happened:

If there were 60 records returned, I got 3 pages (which i want) but on every page you could only see the top record?

I clicked

List --> Properties >Edit Details Group

In the Group On dialog box, I inserted :
=Ceiling(RowNumber(Nothing)/20)

Any ideas on that?

Thanks.|||

Instead of a list, use a table and add a grouping the same way. The table detail rows should then show 20 records each.

-- Robert

|||How about in a Matrix?|||hi there. i encountered the same problem, and i tired doing what you said, applying it to a table.

however, it does the same thing. it only displays the first record out of 20|||

Here's a working example. The attached report has a self contained dataset and the same table twice, once using a list control with groupping applied. Hope this helps.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<Left>10cm</Left>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
<Height>3.1746cm</Height>
</Table>
<List Name="list1">
<Left>2.25cm</Left>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance_1">
<rd:DefaultName>balance_1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance_1">
<rd:DefaultName>prior_balance_1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet1</DataSetName>
<Top>0.75cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="account">
<rd:DefaultName>account</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance">
<rd:DefaultName>balance</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance">
<rd:DefaultName>prior_balance</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Top>0.5cm</Top>
<Width>6.5cm</Width>
<Grouping Name="list1_Details_Group">
<GroupExpressions>
<GroupExpression>=Ceiling(RowNumber(Nothing)/2)</GroupExpression>
</GroupExpressions>
</Grouping>
<Height>3.9246cm</Height>
</List>
</ReportItems>
<Height>5.4246cm</Height>
</Body>
<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance
union
select 'B', 2, 4, 8
union
select 'C', 1, 7, 6
union
select 'D', 2, 9, 12</CommandText>
<DataSourceName>master</DataSourceName>
</Query>
<Fields>
<Field Name="account">
<rd:TypeName>System.String</rd:TypeName>
<DataField>account</DataField>
</Field>
<Field Name="type">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>type</DataField>
</Field>
<Field Name="balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>balance</DataField>
</Field>
<Field Name="prior_balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>prior_balance</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16.5cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

|||

Hi.

I have a report containing a table and a subreport following the table. If there is enough space (if the table has less records at that page), I want the table and the subreport to be on the same page.But if there is not enough space , I want the following subreport to be on the next page.

How can I do this?

Note: The subreport may only be on the last page.

Any help would be appreciated.

COnfiguring Number of records per page?

Is there a way I can specify how many records per page should be displayed?> This is a very basic functionality that needs to go in the deployment at any cost for us?...any ideas.

thanksAssuming you display your data in a list or a table, you could add a grouping expression like this: =Ceiling(RowNumber(Nothing)/20)
This will cause the list/table to group on every 20 rows. On the grouping dialog, select to add a page break on every group and you should get the desired result.

-- Robert|||Thanks Robert. This is what happened:

If there were 60 records returned, I got 3 pages (which i want) but on every page you could only see the top record?

I clicked

List --> Properties >Edit Details Group

In the Group On dialog box, I inserted :
=Ceiling(RowNumber(Nothing)/20)

Any ideas on that?

Thanks.|||

Instead of a list, use a table and add a grouping the same way. The table detail rows should then show 20 records each.

-- Robert

|||How about in a Matrix?|||hi there. i encountered the same problem, and i tired doing what you said, applying it to a table.

however, it does the same thing. it only displays the first record out of 20|||

Here's a working example. The attached report has a self contained dataset and the same table twice, once using a list control with groupping applied. Hope this helps.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<Left>10cm</Left>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
<Height>3.1746cm</Height>
</Table>
<List Name="list1">
<Left>2.25cm</Left>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance_1">
<rd:DefaultName>balance_1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance_1">
<rd:DefaultName>prior_balance_1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>exc accts</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Final</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet1</DataSetName>
<Top>0.75cm</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="account">
<rd:DefaultName>account</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!account.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="balance">
<rd:DefaultName>balance</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="prior_balance">
<rd:DefaultName>prior_balance</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!prior_balance.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>account</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>balance</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>prior balance</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2cm</Width>
</TableColumn>
<TableColumn>
<Width>1.75cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Top>0.5cm</Top>
<Width>6.5cm</Width>
<Grouping Name="list1_Details_Group">
<GroupExpressions>
<GroupExpression>=Ceiling(RowNumber(Nothing)/2)</GroupExpression>
</GroupExpressions>
</Grouping>
<Height>3.9246cm</Height>
</List>
</ReportItems>
<Height>5.4246cm</Height>
</Body>
<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance
union
select 'B', 2, 4, 8
union
select 'C', 1, 7, 6
union
select 'D', 2, 9, 12</CommandText>
<DataSourceName>master</DataSourceName>
</Query>
<Fields>
<Field Name="account">
<rd:TypeName>System.String</rd:TypeName>
<DataField>account</DataField>
</Field>
<Field Name="type">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>type</DataField>
</Field>
<Field Name="balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>balance</DataField>
</Field>
<Field Name="prior_balance">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>prior_balance</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>16.5cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

|||

Hi.

I have a report containing a table and a subreport following the table. If there is enough space (if the table has less records at that page), I want the table and the subreport to be on the same page.But if there is not enough space , I want the following subreport to be on the next page.

How can I do this?

Note: The subreport may only be on the last page.

Any help would be appreciated.