Page Break after sub report - Not by design

All,

I have a report for a Quote print that has 1 table with 1 group in it by Quote #. The detail level is showing the line items on the quote and a second detail line for a sub report to show any notes. If the quote has 1 line on it, I am always getting a page break after the sub report even if it doesn't display anything.

The report shows the following:

Note each of these will contain a rectangle that the text boxes are displayed in unless it is a sub-report then no rectange.

Group header 1 - Address information

Group header 1 - contains a sub report for some notes

Group header 1 - Opening paragraph

Group header 1 - Blank line

Group header 1 - contains the line headings

Detail - contains the line details

Detail - contains the sub report for line notes

Detail - blank line for spacing

Group footer 1 - hidden based on expression

Group footer 1 - hidden based on expression ** One of these 2 lines will print based on the expression

Group footer 1 - sub report for more notes

Group footer 1 - Closing paragraph

Group footer 1 - Signature area

Group footer 1 - sub report for last page of terms / scope notes. NOTE: This sub report also contains a table and the table has a page break before so that I can get these on another page.

So, after # 8 above there is a page break. The remaining footers before # 14 easily fit on the rest of the page. The group 1 also has a page break at the end so it breaks between quotes. I have tried to insert a page break on the rectangle for # 13 and remove the page break on the sub report in # 14 but although everything fits it doesn't page break before the sub report.

It looks like it always page breaks after the last page in the sub report.

Any ideas on how to get around this?

Thanks in advance,

Sherry

Try this:
1.- In the sub report (on every List or Rectangle you have):
1.1.- Right click, Properties.
1.2.- uncheck "Insert a page break after this list".
1.3.- Click on "Edit details group"
1.4.- uncheck "Page break at end".|||

Thanks for the feedback. I had tried that and then it wouldn't page break.

I did resolve it although I feel the original way should have worked.

I created another Group 1 for the same grouping clause (Quote #). This pushed my original grouping to Group 2. I then put a page break after Group #2. I only had a group footer for Group # 1 with the sub report in it. I had to take the sub report page break before off.

Thanks again,

Sherry

page break after 10 rows

I have a report with 1 grouping level, and I want to display only 10 records
per page. How can I force this grouping in RS, can I use functions like
Rownumber, and how precisely does it work ?Yes, search this forum, it's been mentioned loads of times in here.
Effectively use the RowNumber function divided by 10 with the Ceiling
function.
--
Regards
Chris
joia wrote:
> I have a report with 1 grouping level, and I want to display only 10
> records per page. How can I force this grouping in RS, can I use
> functions like Rownumber, and how precisely does it work ?|||Hi, the easiest way to do it, as i found out, is using this function:
System.Math.Ceiling (RowNumber (Nothing)/10)
where 10 can be replaced by a report parameter.
Cheers, Johan
"Chris McGuigan" wrote:
> Yes, search this forum, it's been mentioned loads of times in here.
> Effectively use the RowNumber function divided by 10 with the Ceiling
> function.
> --
> Regards
> Chris
> joia wrote:
> > I have a report with 1 grouping level, and I want to display only 10
> > records per page. How can I force this grouping in RS, can I use
> > functions like Rownumber, and how precisely does it work ?
>|||I am new to reporting services. Can you show me how to use this function?
Thanks,
Anna
"joia" wrote:
> Hi, the easiest way to do it, as i found out, is using this function:
> System.Math.Ceiling (RowNumber (Nothing)/10)
> where 10 can be replaced by a report parameter.
> Cheers, Johan
> "Chris McGuigan" wrote:
> > Yes, search this forum, it's been mentioned loads of times in here.
> > Effectively use the RowNumber function divided by 10 with the Ceiling
> > function.
> > --
> > Regards
> > Chris
> >
> > joia wrote:
> >
> > > I have a report with 1 grouping level, and I want to display only 10
> > > records per page. How can I force this grouping in RS, can I use
> > > functions like Rownumber, and how precisely does it work ?
> >|||I figure out how to use this function now.
Thank you for sharing this function.
Anna :)
"Anna" wrote:
> I am new to reporting services. Can you show me how to use this function?
> Thanks,
> Anna
> "joia" wrote:
> > Hi, the easiest way to do it, as i found out, is using this function:
> > System.Math.Ceiling (RowNumber (Nothing)/10)
> >
> > where 10 can be replaced by a report parameter.
> >
> > Cheers, Johan
> >
> > "Chris McGuigan" wrote:
> >
> > > Yes, search this forum, it's been mentioned loads of times in here.
> > > Effectively use the RowNumber function divided by 10 with the Ceiling
> > > function.
> > > --
> > > Regards
> > > Chris
> > >
> > > joia wrote:
> > >
> > > > I have a report with 1 grouping level, and I want to display only 10
> > > > records per page. How can I force this grouping in RS, can I use
> > > > functions like Rownumber, and how precisely does it work ?
> > >

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

overflow error

i m getting date like this 25-Dec-2006.....now i want to convert this date to 2006-12-25...

but i m getting erorr ?

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%',tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 4) and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=left(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) - 1) and
tbl_cams_uploaddetails.trade_date=convert(datetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2),126)
where tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism = 'EC'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89)
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

instead of your manual logic try to use the Convert(datetime, tbl_cms_uploaddetails.AdditionalField1)|||hi mani thanx for reply.....acyually in additionalfoeld1 i m getting string like this

1930454/IFG/25/DEC-06/15878....so after doing parsing i m getting date 25-Dec-2006......

|||

check all your rows retunrs the same value as you expected (only date)..

You can use the following expression...

Case When IsDate(parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)) = 1 Then

Convert(datetime, parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2))

Else NULL END

|||

try this, you can change you convert(datetime.. to convert(smalldatetime.. and you can also change the tbl_cams_uploaddetails.trade_date so that both have the same format

convert(varchar(10),tbl_cams_uploaddetails.trade_date,101)=convert(varchar(10),convert(smalldatetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)),101)

|||

i added this in my existing query..now it is returning 12 records......and when i run my actual query than it returns only 3 records....

why?

|||i tride yours also...getting same error message.|||

Remove the Group BY clause & validate your records ..

|||same result..3 and 12 records.|||hmm.. did you check you excel if it has dirty date values?
|||

How about rewriting the query as follow as...

select

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP

From

tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails WITH(NOLOCK) on

tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount

and tbl_cms_uploaddetails.AdditionalField1Like cast(tbl_cams_uploaddetails.Scheme_Code as varchar) + '/' +

tbl_cams_uploaddetails.folio_no+ '/' + Cast(day(tbl_cams_uploaddetails.trade_date) as varchar) + '/' +

Upper(Substring(Datename(MM,tbl_cams_uploaddetails.trade_date),1,3)) + '-' +

Cast(Year(tbl_cams_uploaddetails.trade_date) as varchar) + '/%'

Where

tbl_cams_uploaddetails.compare_status='Pending'

andPayment_Mechanism = 'EC'

and tbl_cms_uploaddetails.compare_status='Pending'

and Format_ID in (88,89)

group by

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date

|||now 5 minutes passed..still i m waiting for query result...still it is executing.|||

Its bcs of the expression may violate to use the index - the expression.

The operator LIKE also may cause this issue...

'1930454/IFG/25/DEC-06/15878' here what is 15878 stands for. Are you connecting this value also on join?

|||no i m not using this value 15878