Page break after a group ??

Hi all
In my report, a page break is getting addred for each group of records. Thus
if a group has only 3 lines of data , RS adds a page break and the next
grouping starts on a new page. Thereby making the report span in more pages
thatn expected.
I've checked the settings in the "edit group" window as well as on the table
properties but can not find anything that should cause these page breaks.
Any pointers are appreciated.
Thanks
KWould you verify that your RDL does not contain any PageBreakAtEnd tags.
What is the settings for the report and body heights?
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"k" <test@.msn.com> wrote in message
news:uluuhnghEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Hi all
> In my report, a page break is getting addred for each group of records.
Thus
> if a group has only 3 lines of data , RS adds a page break and the next
> grouping starts on a new page. Thereby making the report span in more
pages
> thatn expected.
> I've checked the settings in the "edit group" window as well as on the
table
> properties but can not find anything that should cause these page breaks.
> Any pointers are appreciated.
> Thanks
> K
>|||here are my settings:
Report Height = 11"
Body height = 3.125"
The rdl conatined the "PageBreakAtend" tag in the first grouping tag. But
if I remove the tag completely, then at the end of the group, the page break
is not displayed but the Table header is displayed as if its a new page.
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:#2hN$wghEHA.384@.TK2MSFTNGP10.phx.gbl...
> Would you verify that your RDL does not contain any PageBreakAtEnd tags.
> What is the settings for the report and body heights?
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "k" <test@.msn.com> wrote in message
> news:uluuhnghEHA.3964@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > In my report, a page break is getting addred for each group of records.
> Thus
> > if a group has only 3 lines of data , RS adds a page break and the next
> > grouping starts on a new page. Thereby making the report span in more
> pages
> > thatn expected.
> >
> > I've checked the settings in the "edit group" window as well as on the
> table
> > properties but can not find anything that should cause these page
breaks.
> >
> > Any pointers are appreciated.
> >
> > Thanks
> > K
> >
> >
>|||I am not quite sure what you in your RDL.
From the sounds of description, you did not understand why you were getting
a page break at the end of your group. You seemed to have resolved that by
the discovery of the PageBreakAtEnd tag in the first (outermost) group.
Do you have RepeatOnNewPage set for the table header or group header rows?
If you do then you will get a them repeated when a new page occurs.
If you want you can post your RDL I will take a look at it.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"k" <test@.msn.com> wrote in message
news:uyQYjQhhEHA.3476@.tk2msftngp13.phx.gbl...
> here are my settings:
> Report Height = 11"
> Body height = 3.125"
> The rdl conatined the "PageBreakAtend" tag in the first grouping tag. But
> if I remove the tag completely, then at the end of the group, the page
break
> is not displayed but the Table header is displayed as if its a new page.
>
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
> news:#2hN$wghEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Would you verify that your RDL does not contain any PageBreakAtEnd tags.
> > What is the settings for the report and body heights?
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "k" <test@.msn.com> wrote in message
> > news:uluuhnghEHA.3964@.TK2MSFTNGP12.phx.gbl...
> > > Hi all
> > >
> > > In my report, a page break is getting addred for each group of
records.
> > Thus
> > > if a group has only 3 lines of data , RS adds a page break and the
next
> > > grouping starts on a new page. Thereby making the report span in more
> > pages
> > > thatn expected.
> > >
> > > I've checked the settings in the "edit group" window as well as on the
> > table
> > > properties but can not find anything that should cause these page
> breaks.
> > >
> > > Any pointers are appreciated.
> > >
> > > Thanks
> > > K
> > >
> > >
> >
> >
>

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 ?
> > >

Override SQL character

I'm trying to search for all records that contain a quotation character in the database. These records were migrated from a mainframe system.

I use the following command:
%'%

The results are:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' order by cliLastName, cliFirstName, cliBirthName'.

/ladds/lib/getrecordset.asp, line 6

Is there a way to override the quotation character temporarily?

Thanks ...You just have to escape the character. Use this:

like '%''%'

That is, two single quotes, and not a double quote.|||Thanks ... worked like a charm!

Overlapping Records by DateTime

Hi guys and gals,
I need to pick your brains for a date/time series question. I'm trying to
write a query that will displays accounts, with their different account type
s
that overlap using the start and end dates.
So say I have the following records:-
AccountId - AccountType - Start - End
1 1 2006-01-01 2006-01-07
2 1 2006-01-06 2006-01-09
3 2 2006-01-02 2006-01-09
I can see that Account 1 and 2 are the same account type, but they overlap
by 1 day however account 3 is different and therefore is fine.
I need to write a query, to decipher all these account that overlap in date
of the same account type. This is part of a larger system, so you may wonde
r
why I wouldn't just place constraints to prevent this from happening, but th
e
reason is that I will allow accounts to overlap, and I have somewhere else i
n
the system a means to elect overlapped accounts based on merit which isn't
required in the query.
I've done some DDL and Inserts here, any help would be greatly appreciated.
Andy
CREATE TABLE Accounts(
AccountId int not null identity(1,1),
AccountType int not null,
UtcDateStart datetime not null,
UtcDateEnd datetime not null
)
CREATE INDEX PK_Accounts_AccountId
ON Accounts (AccountId)
GO
declare @.utcDateTime datetime
set @.utcDateTime = getutcdate()
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
SELECT * FROM AccountsIs this what you're after...
select
*
from
Accounts t1
join Accounts t2 on t1.AccountType = t2.AccountType
where
t2.AccountId > t1.AccountId
and (t1.UtcDateStart between t2.UtcDateStart and t2.UtcDateEnd
or t2.UtcDateStart between t1.UtcDateStart and t1.UtcDateEnd)
HTH. Ryan
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:893FFA23-53D8-4909-995D-C40BD71AF394@.microsoft.com...
> Hi guys and gals,
> I need to pick your brains for a date/time series question. I'm trying to
> write a query that will displays accounts, with their different account
> types
> that overlap using the start and end dates.
> So say I have the following records:-
> AccountId - AccountType - Start - End
> 1 1 2006-01-01 2006-01-07
> 2 1 2006-01-06 2006-01-09
> 3 2 2006-01-02 2006-01-09
> I can see that Account 1 and 2 are the same account type, but they overlap
> by 1 day however account 3 is different and therefore is fine.
> I need to write a query, to decipher all these account that overlap in
> date
> of the same account type. This is part of a larger system, so you may
> wonder
> why I wouldn't just place constraints to prevent this from happening, but
> the
> reason is that I will allow accounts to overlap, and I have somewhere else
> in
> the system a means to elect overlapped accounts based on merit which isn't
> required in the query.
> I've done some DDL and Inserts here, any help would be greatly
> appreciated.
> Andy
> CREATE TABLE Accounts(
> AccountId int not null identity(1,1),
> AccountType int not null,
> UtcDateStart datetime not null,
> UtcDateEnd datetime not null
> )
> CREATE INDEX PK_Accounts_AccountId
> ON Accounts (AccountId)
> GO
> declare @.utcDateTime datetime
> set @.utcDateTime = getutcdate()
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
>
> SELECT * FROM Accounts|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:578107
On Tue, 17 Jan 2006 01:41:03 -0800, Andy Furnival wrote:

>Hi guys and gals,
>I need to pick your brains for a date/time series question. I'm trying to
>write a query that will displays accounts, with their different account typ
es
>that overlap using the start and end dates.
>So say I have the following records:-
>AccountId - AccountType - Start - End
>1 1 2006-01-01 2006-01-07
>2 1 2006-01-06 2006-01-09
>3 2 2006-01-02 2006-01-09
>I can see that Account 1 and 2 are the same account type, but they overlap
>by 1 day however account 3 is different and therefore is fine.
>I need to write a query, to decipher all these account that overlap in date
>of the same account type. This is part of a larger system, so you may wond
er
>why I wouldn't just place constraints to prevent this from happening, but t
he
>reason is that I will allow accounts to overlap, and I have somewhere else
in
>the system a means to elect overlapped accounts based on merit which isn't
>required in the query.
>I've done some DDL and Inserts here, any help would be greatly appreciated.
Hi Andy,
Thanks for the DDL and the INSERTS! Made posting a breeze and answering
more fun.
In addition to Ryan's suggestion, here's another one that will work:
SELECT * FROM Accounts
go
SELECT *
FROM Accounts AS a
INNER JOIN Accounts AS b
ON a.AccountType = b.AccountType
AND a.AccountId > b.AccountId
AND a.utcDateStart < b.utcDateEnd
AND a.utcDateEnd > b.utcDateStart
The benefot of this version is that it avoids the use of OR. If the
utcDateStart and utcDateEnd columns in your real table are indexed, my
version will give the optimizer better opportunities to use that index.
Bottom line: test both for performance; choose the one that performs
best or (if there's no significant difference) the one that you find the
easiest to understand.
Hugo Kornelis, SQL Server MVP|||I would use a Calendar table and a query with a BETWEEN predicate. I
would also get a real key as an account_id with a check digit that
comforms to International banking standards instead of that silly and
dangerous IDENTITY pseudo-column.
As a matter of ISO-11179 conventions, the names should be
"start_utedate".
Use a BETWEEN predicate with a COUNT(*) > 1|||> instead of that silly and
> dangerous IDENTITY pseudo-column.
IDENTITY is a property of a column and NOT a column.
There is nothing stopping you creating a check digit based around IDENTITY
either.
There is nothing 'dangerous' about the IDENTITY 'property'.
The IDENTITY property can be successfully used to create surrogate keys or a
natural primary key where no other one may exist, for instance a message
board.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137635843.478530.301780@.f14g2000cwb.googlegroups.com...
>I would use a Calendar table and a query with a BETWEEN predicate. I
> would also get a real key as an account_id with a check digit that
> comforms to International banking standards instead of that silly and
> dangerous IDENTITY pseudo-column.
> As a matter of ISO-11179 conventions, the names should be
> "start_utedate".
>
> Use a BETWEEN predicate with a COUNT(*) > 1
>