Orphans

Hi,
In simple terms, in SQL2K, I have 2 header tables A and B and a junction
table AB. All of keys are defined with the Cascade Delete constraint. Table
A has a one-to-many relationship with table B. My problem is that if a row
in table A is deleted, the rows that reference it in AB are deleted (which
is good) but table B is untouched. I need to delete all rows in B that are
referenced in the AB row that is deleted in the cascade operation. If those
rows are not deleted, they are left orphaned with no purpose in life except
to take up room on the disk.
I can manually code this but I thought that there might be a chance that I
am missing something a lot simpler. Thanks for any ideas.
SteveSteve Prescott wrote:
> Hi,
> In simple terms, in SQL2K, I have 2 header tables A and B and a junction
> table AB. All of keys are defined with the Cascade Delete constraint. Tabl
e
> A has a one-to-many relationship with table B. My problem is that if a row
> in table A is deleted, the rows that reference it in AB are deleted (which
> is good) but table B is untouched. I need to delete all rows in B that are
> referenced in the AB row that is deleted in the cascade operation. If thos
e
> rows are not deleted, they are left orphaned with no purpose in life excep
t
> to take up room on the disk.
If there is no direct relationship from A->B, but instead it goes
through AB it isn't going to happen. This is because AB references A and
B. B does not reference AB.
This may help you visualize. A->AB, B->AB. When you cascade the
delete/update it only follows the direction of the arrow.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||On Tue, 8 Feb 2005 19:06:38 -0600, Steve Prescott wrote:

>Hi,
>In simple terms, in SQL2K, I have 2 header tables A and B and a junction
>table AB. All of keys are defined with the Cascade Delete constraint. Table
>A has a one-to-many relationship with table B.
Hi Steve,
A junction table is not needed for a one-to-many relationship. If you
really need a one-to-many relationship, then drop the junction table and
instead, insert a foreign key to A in table B.
If you really need the junciton table, then your relationship is
many-to-many, not one-to-many.

> My problem is that if a row
>in table A is deleted, the rows that reference it in AB are deleted (which
>is good) but table B is untouched. I need to delete all rows in B that are
>referenced in the AB row that is deleted in the cascade operation. If those
>rows are not deleted, they are left orphaned with no purpose in life except
>to take up room on the disk.
If you really need a one-to-many relationship, then changing the design as
indicated above will take care of this - rows from B will be deleted when
a row from A is deleted.
If you actually have a many-to-many relationship, then you should rethink
what you are doing. If a business terminates a project, the rows that
store the assignment of employees to that project should be removed as
well (that's the junction table) - but you wouldn't want to remove the
rows holding the personnel data for those employees, right?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Aaron and Hugo,
When I called the A/B relationship one to many I meant that any row in B
that has a reference to A in the AB table is exclusive to that row in A. A
needs to be able to own many B's and every B that it owns, it owns
exclusively so that if A is deleted, every B that it owns needs to be
deleted also. The best example I can think of would be that A is a table of
customers and B is a table of orders where if you delete a customer, the
orders have to be deleted as well.
The actual problem is quite a bit more complicted. The reason I designed it
that way is because a B can also be owned by a C, D, E or F, etc. One B
cannot be owned by more than one owner even if the owners are of different
types.
Given that the Cascade Delete constraint is not fully functional for the
need, I was wondering what the best practices would be for deletion of child
rows in this situation. I am working on my own solution now but would be
happy to throw it out for a better one.
Thanks again.
Steve|||On Wed, 9 Feb 2005 17:21:18 -0600, Steve Prescott wrote:
(snip)
>The actual problem is quite a bit more complicted. The reason I designed it
>that way is because a B can also be owned by a C, D, E or F, etc. One B
>cannot be owned by more than one owner even if the owners are of different
>types.
>Given that the Cascade Delete constraint is not fully functional for the
>need, I was wondering what the best practices would be for deletion of chil
d
>rows in this situation. I am working on my own solution now but would be
>happy to throw it out for a better one.
Hi Steve,
What do you mean with "not fully functional"?
The correct (IMO) design in your case, if I'm not misunderstanding you, is
CREATE TABLE B (.....,
....,
A_Key int NULL, -- int is assumption
C_Key int NULL, -- int is assumption
D_Key int NULL, -- int is assumption
E_Key int NULL, -- int is assumption
PRIMARY KEY (...),
FOREIGN KEY (A_Key) REFERENCES A
ON UPDATE CASCADE ON DELETE CASCASE,
FOREIGN KEY (C_Key) REFERENCES C
ON UPDATE CASCADE ON DELETE CASCASE,
FOREIGN KEY (D_Key) REFERENCES D
ON UPDATE CASCADE ON DELETE CASCASE,
FOREIGN KEY (E_Key) REFERENCES E
ON UPDATE CASCADE ON DELETE CASCASE,
CHECK (CASE WHEN A_Key IS NULL THEN 1 ELSE 0 END
+ CASE WHEN C_Key IS NULL THEN 1 ELSE 0 END
+ CASE WHEN D_Key IS NULL THEN 1 ELSE 0 END
+ CASE WHEN E_Key IS NULL THEN 1 ELSE 0 END = 1)
)
Though I should probably add that I'm intrigued - what is the actual
business problem here? One "thing" that can be owned by different "things"
but yet stays the same is not too common. I don't think I ever encoutered
anything like this (and if I did, I've forgotten).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
The business problem is church contributions. It goes like this:
A Batch is a money counting session
A Batch can own one or more Churches
A Church owns People and Families
A Person or Family can own one or more Contributions.
The junction table in question references a Batch, a Church, a Person (or
Family) and a Contribution. Thus the Contribution ('B' in the preceeding
emails) can belong to a Person or a Family.
The simplified AB junction table I referred to actually has a primary key
made up of Batch_ID, Church_ID, Person_ID (or Family_ID) and
Contribution_ID.
This arrangement allows the money counters to count up contributions for
more than one Church at one sitting. They can count Contributions for both
People and Families and refer to the data later by referencing the Batch.
Contributions that go into one Batch are not visible to other Batches even
though the same Church, Person or Family might be in the other Batch.
I am grateful for your code example. I'm not sure if I can re-design the DB
at this point. My design is not exactly a cripple but it could have been
done better. This issue of deleting orphans has brought that to light to me.
Steve|||On Wed, 9 Feb 2005 19:07:20 -0600, Steve Prescott wrote:

>The business problem is church contributions. It goes like this:
> A Batch is a money counting session
> A Batch can own one or more Churches
> A Church owns People and Families
> A Person or Family can own one or more Contributions.
>The junction table in question references a Batch, a Church, a Person (or
>Family) and a Contribution. Thus the Contribution ('B' in the preceeding
>emails) can belong to a Person or a Family.
Hi Steve,
This is partly enlightening, but some new questions arise as well. What
does "A Church owns People and Families" mean? (It sounds more like a sect
than like church <shudder> )
I assume that "owning" refers to a one to many relationship, but that
would mean that each person or family can only donate to one church. Is
that the case? Or can a person / family donate to church 1 and church 2
today, and also donate to church 1 and church 3 during the next batch?
For now, I'll assume the following - let me know if I'm wrong:
* Each contribution is from either one person or one family
* Each person and each familiy is allowed to make multiple contributions
* Each contribution is for one church
* A church may receive more than one contribution
* The same person or family can contribute to different churches
* Each church can receive contributions from multiple persons and families
* Each contribution is counted during one batch
* A batch can consist of more than one contribution
* A batch can consist of contributions of different persons and families
* A person or family can donate contributions during more than one batch
* A batch can consist of contributions for different churches
* A church can receive contributions during more than one batch

>The simplified AB junction table I referred to actually has a primary key
>made up of Batch_ID, Church_ID, Person_ID (or Family_ID) and
>Contribution_ID.
If all the above assumptions are completely correct, then this is quite a
good design (but read on for one possible improvement). But if I made
incorrect assumptions (e.g. if the "owning" implies that a person or
family is not allowed to contribute to more than one church), then this
design holds some redundancy. Building on the example: if Peter may only
donate to the Holy Church of Petersburg, it suffices to store that a
donation is from Peter - explicitly storing that this donation is for the
Holy Church of Petersburg would bhe redundant. There might still be good
reasons to choose it, but you should be awarre of the implications and the
increased possibility of data corruption.
(snip)
>I am grateful for your code example. I'm not sure if I can re-design the DB
>at this point. My design is not exactly a cripple but it could have been
>done better. This issue of deleting orphans has brought that to light to me.[/color
]
Well, if you can re-design, my first advice would be to replace the two
entities Family and Person with one entity: Contributor. My guess is that
the families table and the persons table have a lot of columns in common.
All these shared columns should be moved to the Contributors table; the
columns that are exclusive for either persons (like date of birth) or
families (like number of family members) can either go in seperate
subtables, or in mutually exclusive NULLable columns in the Contributors
table.
Based on my current information (and the above assumptions), my proposal
for the design would be:
BATCHES (Batch_ID (PK), other columns)
CHURCHES (Church_ID (PK), other columns)
CONTRIBUTORS (Contrib_ID (PK), other columns)
PERSONS (Contrib_ID (PK and FK to Contributors), other columns)
FAMILIES (Contrib_ID (PK and FK to Contributors), other columns)
DONATIONS (Batch_ID (FK to Batches), Church_ID (FK to Churches),
Contrib_ID (FK to Contributions), Amount, other columns)
For donations, the PK would be (Batch_ID, Church_ID, Contrib_ID)
For all tables, I'd examine if a surrogate key is really needed. I prefer
to use natuaral keys where possible.
The Persons and Families tables are optional - if their number of columns
is limited, I'd simply keep those columns in the Contributors table.
If you really need to be sure that no family will ever be entered in the
persons table and vice versa, you can use Joe Celko's trick:
CREATE TABLE Contributors (Contrib_ID int NOT NULL,
Type char(1) NOT NULL,
-- other columns
PRIMARY KEY (Contrib_ID),
CONSTRAINT Celko1 UNIQUE (Contrib_ID, Type),
CONSTRAINT Celko2 CHECK (Type IN ('F', 'P'))
)
CREATE TABLE Persons (Contrib_ID int NOT NULL,
Type char(1) NOT NULL,
-- other columns
PRIMARY KEY (Contrib_ID),
FOREIGN KEY (Contrib_ID, Type)
REFERENCES Contributors (Contrib_ID, Type)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT OnlyPersons CHECK (Type = 'P')
)
CREATE TABLE Families (Contrib_ID int NOT NULL,
Type char(1) NOT NULL,
-- other columns
PRIMARY KEY (Contrib_ID),
FOREIGN KEY (Contrib_ID, Type)
REFERENCES Contributors (Contrib_ID, Type)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT OnlyFamilies CHECK (Type = 'F')
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
All of the questions and assumptions you pose are correct. The Batch knows
nothing about the church affiliation of a Person or Family. One Person or
Family can make multiple Contributions to the same Church or multiple
Churches in the same Batch.
The Families and Persons tables have no columns in common. Actually, the
Families table has just 3 columns (Name, Salutation and Anniversay) outside
of the primary key and 'DateCreated', et. al. All info regarding people in
a Family is in the Persons table or the FamilyPerson junction table.
Regarding the fabled 'AB' junction table, that actually represents 2
junction tables: BatchChurchPersonContribution and
BatchChurchFamilyContribution. I left that out before for the sake of
simplicity. I'm not too clear about the Donations table you suggest, but I
think that the issues surrounding that suggestion are addressed by the fact
that there are the 2 tables I reference above. A Family gives strictly as a
Family and the same is true for a Person. A Person in a Family can make a
Contribution to the same Church in the same Batch and the Family he belongs
to can do that as well and there is no knowledge on the part of the Batch as
to the Family/Person relationship.
I hesitate to bring this up but there is one serious redundancy issue that I
took on knowing that it is bad practice but I could not think of another
solution. There is a ChurchPersonContribution table that references the same
Church, Person and Contribution keys that are in the
BatchChurchPersonContribution table. Rows are created in both tables each
time the user saves. I did this because it is necessary to be able to
reference all of the Contributions of a Person through the Church. This way,
Contributions of a Person can be fetched from the DB without reference to
the Batch. The user can take a 'Church Centric' view of Contributions. The
intracacies of doing this are coded into the Data layer and it works good.
Deleting a Batch or a Contribution from a Batch works well and the
corresponding Contribution from the ChurchPersonContribution table works in
sync thanks to Cascade Delete. Is this a big Achilles Heel or is this kind
of thing regarded in the trade as acceptable but not desireable? Except for
help from Delete Cascade in the case of deletions, I am relying entirely on
Visual Basic code at the data layer to keep the DB uncorrupted.
Thanks for all of your help.
Steve|||On Thu, 10 Feb 2005 11:04:14 -0600, Steve Prescott wrote:

>All of the questions and assumptions you pose are correct. The Batch knows
>nothing about the church affiliation of a Person or Family. One Person or
>Family can make multiple Contributions to the same Church or multiple
>Churches in the same Batch.
Hi Steve,
Okay - thanks for confirming. I suggest you to re-design your database
along the lines I gave in my previous message.
I forgot to add the effects of that design on cascading deletes: if you
delete a batch, all contributions that belong to that batch will be
deleted by the cascading delete option, but the churches that received the
contributions and the contributors that paid them will be retained. Along
the same lines, if you delete a church all contributions to that church
will be deleted, but not the contributors paying them, nor the batches
containing those contributions. I'd say that this is a Good Thing, as
there's a good chance that these batches also hold contributions for other
churches, so you *should* not delete the complete batch.

>The Families and Persons tables have no columns in common. Actually, the
>Families table has just 3 columns (Name, Salutation and Anniversay) outside
>of the primary key and 'DateCreated', et. al. All info regarding people in
>a Family is in the Persons table or the FamilyPerson junction table.
In that case, I might still go for the contributors table (to keep the
foreign keys in the contributions table clear), or I might use two
mutually exclusive foreign keys in the contributions table (PersonID and
FamilyID, both as foreign key, both nullable and with a CHECK constraint
to ensure that exactly one is NULL in each row). You'll need to add a
surrogate primary key to the contributions table; the natural key
(BatchID, ChurchID, FamilyID and PersonID) has NULLable columns, it can
still be declared as a UNIQUE constraint, but not as PRIMARY KEY.

>Regarding the fabled 'AB' junction table, that actually represents 2
>junction tables: BatchChurchPersonContribution and
>BatchChurchFamilyContribution. I left that out before for the sake of
>simplicity. I'm not too clear about the Donations table you suggest, but I
>think that the issues surrounding that suggestion are addressed by the fact
>that there are the 2 tables I reference above.
The name Donations was ill chosen - I should have called it Contributions.
If you have a seperate table for contributions AND one (or two, that is
indeed not the core issue) junction tables BatchChurchPersonContribution
and BatchChurchFamilyContribution, then this is a seriuos flaw in your
design. Each contribution is part of one batch, goes to one church and is
paid by one family or person, so BatchID, ChurchID, PersonID and FamilyID
are functionally dependant on a contribution. They should be stored in the
table that holds all the information about individual contributions.
If you have a table Contributions and a junction table that relates a
contribution to a church, a family (or person) and a batch, then there is
nothing that would stop a user from entering than one single contribution
is part of three batches, paid by five families and four persons and in
favor of seventeen different churches.
And since this is allowed by your design, it makes sense that deleting a
batch would not remove the contributions in that batch, since they might
be part of another batch as well.
(snip)
>I hesitate to bring this up but there is one serious redundancy issue that
I
>took on knowing that it is bad practice but I could not think of another
>solution. There is a ChurchPersonContribution table that references the sam
e
>Church, Person and Contribution keys that are in the
>BatchChurchPersonContribution table.
There's nothing wrong with **CONTROLLED** redundancy -- unless it is
needless. In this case, it is needless. Based on my proposed design, the
ChurchPersonContribution table can be replaced by a view (and I'm quite
positive it can also be replaced by a view in your situation, it's just
that I still don't feel secorer about all details of your current
implementation).
CREATE VIEW ChurchPersonContribution
AS
SELECT ChurchID, PersonID, Amount, DatePaid, ...
FROM Contributions
go

> Except for
>help from Delete Cascade in the case of deletions, I am relying entirely on
>Visual Basic code at the data layer to keep the DB uncorrupted.
Ouch!!! That means that if you one day have to replace your front-end,
you'll have to redo all your integrity issues. That also means you could
easily mess up if you're using straight SQL queries to do some maintenance
work or trouble-shooting.
ALWAYS use constraints to guard against data corruption. If the business
rules are too complex for constraints, use triggers. Triggers and
constraints are the only methods that can't easily be circumvented.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Orphaned Table Footers

Hello.
I have a report that uses one table in the body. The actual report header is
empty and the actual report footer only shows the page number. I use the
Table header and have "Repeat on New Page" set to True. I use the table
header instead because I need to display some summary data at the top of each
page.
I have 4 groups total and the header of the outermost group (table1_group1)
is the one that repeats on each page.
The reports display fine most of the time and the Table1_group1 table header
repeats as it should, but whenever the spacing works out that the
Table1_Footer1 table footer needs to start on a new page, the footer prints
on its own without the table header. The table1_Group1 table footer does not
repeat on each page and only shows up as the last page for each group as it
shows some totals/sums.
Any ideas on how to fix this so the Table_1Group1 table header will print
even when only the Table1Group1 table footer is being printed?
Thanks,
Brian.Hi Brian,
I'd like to get a clear understanding of your issue to
be able to assist you better.
>I have a report that uses one table in the body. The
actual report header is
>empty and the actual report footer only shows the page
number. I use the
1. In Reporting Services there is no sections called
Report Header or Report Footer although the black space
above/below the data region can be regarded as Report
Header/Report Footer. Since "the actual report footer
only shows the page number", I'd like to confirm if you
mean Page Header/Page Footer by saying Report
Header/Report Footer.
>Table header and have "Repeat on New Page" set to True.
I use the table
>header instead because I need to display some summary
data at the top of each
>page.
2. This is the second one I'd like to confirm. You set
the "RepeatOnNewPage" property of the Table Header
rather than the Group Header to true.
>I have 4 groups total and the header of the outermost
group (table1_group1)
>is the one that repeats on each page.
3. How did you do with the outermost group named
table1_group1 so that it repeats on each page? Did you
set the "RepeatOnNewPage" property of the Group Header
of this group to true or place a "Page Break at end" of
the group?
>The reports display fine most of the time and the
Table1_group1 table header
4. Here by saying "the Table1_group1 table header" do
you mean the "group header of Table1_group1"?
>repeats as it should, but whenever the spacing works
out that the
>Table1_Footer1 table footer needs to start on a new
page,
5. A Table Footer only appears at the end of a table.
I'd like to confirm if you mean Table Footer or Group
Footer of Table1_group1.
>the footer prints
>on its own without the table header. The table1_Group1
table footer does not
>repeat on each page and only shows up as the last page
for each group as it
>shows some totals/sums.
>Any ideas on how to fix this so the Table_1Group1 table
header will print
>even when only the Table1Group1 table footer is being
printed?
6. Setting the "RepeatOnNewPage" property of the Group
Header to true should have no such a problem. By doing
this the group header repeats on each page.
For us, the most efficient troubleshooting method would
be reproducing this issue on our end. If the issue
persists, please try to reproduce the issue with a
sample database and post the detailed reproduce steps.
If anything is unclear, get in touch.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Orphaned Table Footers
>thread-index: AcUCWF6Szxan+iN7TLGB155pQjDGbg==>X-WBNR-Posting-Host: 204.92.98.25
>From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>Subject: Orphaned Table Footers
>Date: Mon, 24 Jan 2005 13:05:01 -0800
>Lines: 20
>Message-ID:
<C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:40664
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hello.
>I have a report that uses one table in the body. The
actual report header is
>empty and the actual report footer only shows the page
number. I use the
>Table header and have "Repeat on New Page" set to True.
I use the table
>header instead because I need to display some summary
data at the top of each
>page.
>I have 4 groups total and the header of the outermost
group (table1_group1)
>is the one that repeats on each page.
>The reports display fine most of the time and the
Table1_group1 table header
>repeats as it should, but whenever the spacing works
out that the
>Table1_Footer1 table footer needs to start on a new
page, the footer prints
>on its own without the table header. The table1_Group1
table footer does not
>repeat on each page and only shows up as the last page
for each group as it
>shows some totals/sums.
>Any ideas on how to fix this so the Table_1Group1 table
header will print
>even when only the Table1Group1 table footer is being
printed?
>Thanks,
>Brian.
>|||Hello.
Sorry, I used some wrong terminology. Here are the clarifications.
1) -Page header is empty.
- it is the page footer that only shows the page number
2) -Table header row is deleted (I don't use it).
- RepeatOnNewPage property of the Table1_Group1 header is set to true. This
is the header that contains some summary data and I want it to repeat on each
page.
- Table Footer is included. RepeatOnNewPage property is set to TRUE. This is
just an empty row with a bottom border. This repeats on each page to give the
table a bottom border on each page.
3) -Table1_Group1 Header RepeatOnNewPage Property is TRUE
-When I go "Edit Group", Page break at start, include group header, include
group footer, AND repeat group header are checked off. So both "Repeat Group
Header" is checked and RepeatOnNewPage property is TRUE.
4) - Yes, I mean the Group Header of Table1_Group1
5) - Yes, I mean the Group Footer of Table1_Group1
I think this might be the problem:
The Group Headers will repeat on each page in the print preview and when
exporting to PDF as long as the page displays data from the Table Details
section or data from inner groups. But if the Outermost group footer is
squeezed onto the next page by itself then the group header does not show.
I was able to replicate this problem using a very simple report.
Steps:
1) Create a new report. have the data set return only 1 row to make things
simple (.EG Select Top 1 * from table).
2) Create a table in the body of the report
3) Get rid of the table header and the table footer. so only the table
details section remains.
4) Add a group. Group on anything since only 1 row is returned by the query.
-so now there's 3 rows in the layout. Table1_Group1 Header, Detail, and
Table1_Group1 Footer.
5) Type something into the table1_group1 header,
-Edit the group and check off
6) Drag a field into the body.
7) type something into the table1_Group1 footer. footer only shows up at the
end of each group change and does not repeat on each page.
8) Now, make the detail row long, so that it just squeezes the table1_Group1
footer onto the next page in the print preview.
The Table1_Group1 Header will show up on the first page along with the
detail row, but in the next page, only the table1_Group1 footer row shows.
The table1_Group1 header does not appear even though it should repeat on each
page.
This happens in the Print Preview (NOT THE PREVIEW TAB OR WHEN VIEWING
ONLINE) and also when you export the report to PDF. Viewing the report just
online is fine.
For my real report, the table1_Group1 header always repeats fine except on
pages where only the table1_Group1 footer has been squeezed onto the next
page by itself.
Like I said, my report has 4 groups, each group gives subtotals at various
levels and the table1_Group1 Footer gives a grand total for each set.
Hopefully the problem is clear now.
Thanks!
Brian.
"William Wang[MSFT]" wrote:
> Hi Brian,
> I'd like to get a clear understanding of your issue to
> be able to assist you better.
> >I have a report that uses one table in the body. The
> actual report header is
> >empty and the actual report footer only shows the page
> number. I use the
> 1. In Reporting Services there is no sections called
> Report Header or Report Footer although the black space
> above/below the data region can be regarded as Report
> Header/Report Footer. Since "the actual report footer
> only shows the page number", I'd like to confirm if you
> mean Page Header/Page Footer by saying Report
> Header/Report Footer.
>
> >Table header and have "Repeat on New Page" set to True.
> I use the table
> >header instead because I need to display some summary
> data at the top of each
> >page.
> 2. This is the second one I'd like to confirm. You set
> the "RepeatOnNewPage" property of the Table Header
> rather than the Group Header to true.
>
> >I have 4 groups total and the header of the outermost
> group (table1_group1)
> >is the one that repeats on each page.
> 3. How did you do with the outermost group named
> table1_group1 so that it repeats on each page? Did you
> set the "RepeatOnNewPage" property of the Group Header
> of this group to true or place a "Page Break at end" of
> the group?
>
> >The reports display fine most of the time and the
> Table1_group1 table header
> 4. Here by saying "the Table1_group1 table header" do
> you mean the "group header of Table1_group1"?
>
> >repeats as it should, but whenever the spacing works
> out that the
> >Table1_Footer1 table footer needs to start on a new
> page,
> 5. A Table Footer only appears at the end of a table.
> I'd like to confirm if you mean Table Footer or Group
> Footer of Table1_group1.
>
> >the footer prints
> >on its own without the table header. The table1_Group1
> table footer does not
> >repeat on each page and only shows up as the last page
> for each group as it
> >shows some totals/sums.
> >Any ideas on how to fix this so the Table_1Group1 table
> header will print
> >even when only the Table1Group1 table footer is being
> printed?
> 6. Setting the "RepeatOnNewPage" property of the Group
> Header to true should have no such a problem. By doing
> this the group header repeats on each page.
> For us, the most efficient troubleshooting method would
> be reproducing this issue on our end. If the issue
> persists, please try to reproduce the issue with a
> sample database and post the detailed reproduce steps.
> If anything is unclear, get in touch.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> >Thread-Topic: Orphaned Table Footers
> >thread-index: AcUCWF6Szxan+iN7TLGB155pQjDGbg==> >X-WBNR-Posting-Host: 204.92.98.25
> >From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
> >Subject: Orphaned Table Footers
> >Date: Mon, 24 Jan 2005 13:05:01 -0800
> >Lines: 20
> >Message-ID:
> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> >Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:40664
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >Hello.
> >
> >I have a report that uses one table in the body. The
> actual report header is
> >empty and the actual report footer only shows the page
> number. I use the
> >Table header and have "Repeat on New Page" set to True.
> I use the table
> >header instead because I need to display some summary
> data at the top of each
> >page.
> >I have 4 groups total and the header of the outermost
> group (table1_group1)
> >is the one that repeats on each page.
> >The reports display fine most of the time and the
> Table1_group1 table header
> >repeats as it should, but whenever the spacing works
> out that the
> >Table1_Footer1 table footer needs to start on a new
> page, the footer prints
> >on its own without the table header. The table1_Group1
> table footer does not
> >repeat on each page and only shows up as the last page
> for each group as it
> >shows some totals/sums.
> >Any ideas on how to fix this so the Table_1Group1 table
> header will print
> >even when only the Table1Group1 table footer is being
> printed?
> >
> >Thanks,
> >Brian.
> >
>|||Hi Brian,
I will be looking into this issue and I will update you
once I have more information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with
technical phone support at no charge during critical LAN
outages or "business down" situations. This benefit is
available 24 hours a day, 7 days a week to all Microsoft
technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/support
overview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/support
overview/
If you are outside the United States, please visit our
International Support page:
http://support.microsoft.com/default.aspx?scid=%2finterna
tional.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Orphaned Table Footers
>thread-index: AcUDE9iY3e1yULFPQLmd8LylZ4RFtQ==>X-WBNR-Posting-Host: 204.92.98.25
>From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>References:
<C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
<QmipjprAFHA.2768@.cpmsftngxa10.phx.gbl>
>Subject: RE: Orphaned Table Footers
>Date: Tue, 25 Jan 2005 11:27:02 -0800
>Lines: 228
>Message-ID:
<F2866859-4257-41B1-9F2F-FAACB7EEC085@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:40795
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hello.
>Sorry, I used some wrong terminology. Here are the
clarifications.
>1) -Page header is empty.
>- it is the page footer that only shows the page number
>2) -Table header row is deleted (I don't use it).
>- RepeatOnNewPage property of the Table1_Group1 header
is set to true. This
>is the header that contains some summary data and I
want it to repeat on each
>page.
>- Table Footer is included. RepeatOnNewPage property is
set to TRUE. This is
>just an empty row with a bottom border. This repeats on
each page to give the
>table a bottom border on each page.
>
>3) -Table1_Group1 Header RepeatOnNewPage Property is
TRUE
>-When I go "Edit Group", Page break at start, include
group header, include
>group footer, AND repeat group header are checked off.
So both "Repeat Group
>Header" is checked and RepeatOnNewPage property is TRUE.
>4) - Yes, I mean the Group Header of Table1_Group1
>5) - Yes, I mean the Group Footer of Table1_Group1
>I think this might be the problem:
>The Group Headers will repeat on each page in the print
preview and when
>exporting to PDF as long as the page displays data from
the Table Details
>section or data from inner groups. But if the Outermost
group footer is
>squeezed onto the next page by itself then the group
header does not show.
>I was able to replicate this problem using a very
simple report.
>Steps:
>1) Create a new report. have the data set return only 1
row to make things
>simple (.EG Select Top 1 * from table).
>2) Create a table in the body of the report
>3) Get rid of the table header and the table footer. so
only the table
>details section remains.
>4) Add a group. Group on anything since only 1 row is
returned by the query.
> -so now there's 3 rows in the layout. Table1_Group1
Header, Detail, and
>Table1_Group1 Footer.
>5) Type something into the table1_group1 header,
> -Edit the group and check off
>6) Drag a field into the body.
>7) type something into the table1_Group1 footer. footer
only shows up at the
>end of each group change and does not repeat on each
page.
>8) Now, make the detail row long, so that it just
squeezes the table1_Group1
>footer onto the next page in the print preview.
>The Table1_Group1 Header will show up on the first page
along with the
>detail row, but in the next page, only the
table1_Group1 footer row shows.
>The table1_Group1 header does not appear even though it
should repeat on each
>page.
>This happens in the Print Preview (NOT THE PREVIEW TAB
OR WHEN VIEWING
>ONLINE) and also when you export the report to PDF.
Viewing the report just
>online is fine.
>For my real report, the table1_Group1 header always
repeats fine except on
>pages where only the table1_Group1 footer has been
squeezed onto the next
>page by itself.
>Like I said, my report has 4 groups, each group gives
subtotals at various
>levels and the table1_Group1 Footer gives a grand total
for each set.
>Hopefully the problem is clear now.
>Thanks!
>Brian.
>"William Wang[MSFT]" wrote:
>> Hi Brian,
>> I'd like to get a clear understanding of your issue
to
>> be able to assist you better.
>> >I have a report that uses one table in the body. The
>> actual report header is
>> >empty and the actual report footer only shows the
page
>> number. I use the
>> 1. In Reporting Services there is no sections called
>> Report Header or Report Footer although the black
space
>> above/below the data region can be regarded as Report
>> Header/Report Footer. Since "the actual report footer
>> only shows the page number", I'd like to confirm if
you
>> mean Page Header/Page Footer by saying Report
>> Header/Report Footer.
>>
>> >Table header and have "Repeat on New Page" set to
True.
>> I use the table
>> >header instead because I need to display some
summary
>> data at the top of each
>> >page.
>> 2. This is the second one I'd like to confirm. You
set
>> the "RepeatOnNewPage" property of the Table Header
>> rather than the Group Header to true.
>>
>> >I have 4 groups total and the header of the
outermost
>> group (table1_group1)
>> >is the one that repeats on each page.
>> 3. How did you do with the outermost group named
>> table1_group1 so that it repeats on each page? Did
you
>> set the "RepeatOnNewPage" property of the Group
Header
>> of this group to true or place a "Page Break at end"
of
>> the group?
>>
>> >The reports display fine most of the time and the
>> Table1_group1 table header
>> 4. Here by saying "the Table1_group1 table header" do
>> you mean the "group header of Table1_group1"?
>>
>> >repeats as it should, but whenever the spacing works
>> out that the
>> >Table1_Footer1 table footer needs to start on a new
>> page,
>> 5. A Table Footer only appears at the end of a table.
>> I'd like to confirm if you mean Table Footer or Group
>> Footer of Table1_group1.
>>
>> >the footer prints
>> >on its own without the table header. The
table1_Group1
>> table footer does not
>> >repeat on each page and only shows up as the last
page
>> for each group as it
>> >shows some totals/sums.
>> >Any ideas on how to fix this so the Table_1Group1
table
>> header will print
>> >even when only the Table1Group1 table footer is
being
>> printed?
>> 6. Setting the "RepeatOnNewPage" property of the
Group
>> Header to true should have no such a problem. By
doing
>> this the group header repeats on each page.
>> For us, the most efficient troubleshooting method
would
>> be reproducing this issue on our end. If the issue
>> persists, please try to reproduce the issue with a
>> sample database and post the detailed reproduce steps.
>> If anything is unclear, get in touch.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> Get Secure! - <www.microsoft.com/security>
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> --
>> >Thread-Topic: Orphaned Table Footers
>> >thread-index: AcUCWF6Szxan+iN7TLGB155pQjDGbg==>> >X-WBNR-Posting-Host: 204.92.98.25
>> >From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>> >Subject: Orphaned Table Footers
>> >Date: Mon, 24 Jan 2005 13:05:01 -0800
>> >Lines: 20
>> >Message-ID:
>> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:40664
>> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> >
>> >Hello.
>> >
>> >I have a report that uses one table in the body. The
>> actual report header is
>> >empty and the actual report footer only shows the
page
>> number. I use the
>> >Table header and have "Repeat on New Page" set to
True.
>> I use the table
>> >header instead because I need to display some
summary
>> data at the top of each
>> >page.
>> >I have 4 groups total and the header of the
outermost
>> group (table1_group1)
>> >is the one that repeats on each page.
>> >The reports display fine most of the time and the
>> Table1_group1 table header
>> >repeats as it should, but whenever the spacing works
>> out that the
>> >Table1_Footer1 table footer needs to start on a new
>> page, the footer prints
>> >on its own without the table header. The
table1_Group1
>> table footer does not
>> >repeat on each page and only shows up as the last
page
>> for each group as it
>> >shows some totals/sums.
>> >Any ideas on how to fix this so the Table_1Group1
table
>> header will print
>> >even when only the Table1Group1 table footer is
being
>> printed?
>> >
>> >Thanks,
>> >Brian.
>> >
>>
>|||Hi Brian,
From the product team's feedback, in this case we've
made the detail row tall enough that the header row plus
the detail row plus the footer row is simply too big for
a page.
In the case of HTML and Preview, we use a flexible
pagination algorithm that allows pages to grow somewhat
to accommodate this sort of layout.
In the case of PDF or Print Preview, we must respect the
page size settings precisely.
This is by design in Reporting Services. As an end user
too, I can truly understand your concern about it.
Currently we have some options for you regarding this
issue:
a. You may want to send a feature change request to
mswish@.microsoft.com. By submitting your concern to
mswish@.microsoft.com you can get your voice into the
design plans for upcoming versions. Microsoft takes
customer requests and suggestions very seriously. MSWISH
is your voice to the development team. A significant
number of the design change requests come from our
customers through the MSWISH alias and the MSWISH web
site. http://www.microsoft.com/mswish. I encourage you
to submit this request.
b. You can talk to the support engineers directly in
Microsoft Product Support Services (PSS) to see if they
can find a workaround or offer a fix. For a complete
list of Microsoft Product Support Services phone
numbers, please go to the following address on the World
Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see
http://support.microsoft.com for regional support phone
numbers.
HTH!
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>X-Tomcat-ID: 215340416
>References:
<C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
<QmipjprAFHA.2768@.cpmsftngxa10.phx.gbl>
<F2866859-4257-41B1-9F2F-FAACB7EEC085@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain
>Content-Transfer-Encoding: 7bit
>From: v-rxwang@.online.microsoft.com (William Wang[MSFT])
>Organization: Microsoft
>Date: Wed, 26 Jan 2005 10:18:13 GMT
>Subject: RE: Orphaned Table Footers
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Message-ID: <8rTWYB5AFHA.2504@.cpmsftngxa10.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>Lines: 273
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:40883
>NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
>Hi Brian,
>I will be looking into this issue and I will update you
>once I have more information.
>Sincerely,
>William Wang
>Microsoft Online Partner Support
>When responding to posts, please "Reply to Group" via
>your newsreader so that others may learn and benefit
>from your issue.
>=====================================================>Business-Critical Phone Support (BCPS) provides you
with
>technical phone support at no charge during critical
LAN
>outages or "business down" situations. This benefit is
>available 24 hours a day, 7 days a week to all
Microsoft
>technology partners in the United States and Canada.
>This and other support options are available here:
>BCPS:
>https://partner.microsoft.com/US/technicalsupport/suppor
t
>overview/40010469
>Others:
>https://partner.microsoft.com/US/technicalsupport/suppor
t
>overview/
>If you are outside the United States, please visit our
>International Support page:
>http://support.microsoft.com/default.aspx?scid=%2fintern
a
>tional.aspx.
>=====================================================>This posting is provided "AS IS" with no warranties,
and
>confers no rights.
>--
>>Thread-Topic: Orphaned Table Footers
>>thread-index: AcUDE9iY3e1yULFPQLmd8LylZ4RFtQ==>>X-WBNR-Posting-Host: 204.92.98.25
>>From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>>References:
><C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
><QmipjprAFHA.2768@.cpmsftngxa10.phx.gbl>
>>Subject: RE: Orphaned Table Footers
>>Date: Tue, 25 Jan 2005 11:27:02 -0800
>>Lines: 228
>>Message-ID:
><F2866859-4257-41B1-9F2F-FAACB7EEC085@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
>microsoft.public.sqlserver.reportingsvcs:40795
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hello.
>>Sorry, I used some wrong terminology. Here are the
>clarifications.
>>1) -Page header is empty.
>>- it is the page footer that only shows the page number
>>2) -Table header row is deleted (I don't use it).
>>- RepeatOnNewPage property of the Table1_Group1 header
>is set to true. This
>>is the header that contains some summary data and I
>want it to repeat on each
>>page.
>>- Table Footer is included. RepeatOnNewPage property
is
>set to TRUE. This is
>>just an empty row with a bottom border. This repeats
on
>each page to give the
>>table a bottom border on each page.
>>
>>3) -Table1_Group1 Header RepeatOnNewPage Property is
>TRUE
>>-When I go "Edit Group", Page break at start, include
>group header, include
>>group footer, AND repeat group header are checked off.
>So both "Repeat Group
>>Header" is checked and RepeatOnNewPage property is
TRUE.
>>4) - Yes, I mean the Group Header of Table1_Group1
>>5) - Yes, I mean the Group Footer of Table1_Group1
>>I think this might be the problem:
>>The Group Headers will repeat on each page in the
print
>preview and when
>>exporting to PDF as long as the page displays data
from
>the Table Details
>>section or data from inner groups. But if the
Outermost
>group footer is
>>squeezed onto the next page by itself then the group
>header does not show.
>>I was able to replicate this problem using a very
>simple report.
>>Steps:
>>1) Create a new report. have the data set return only
1
>row to make things
>>simple (.EG Select Top 1 * from table).
>>2) Create a table in the body of the report
>>3) Get rid of the table header and the table footer.
so
>only the table
>>details section remains.
>>4) Add a group. Group on anything since only 1 row is
>returned by the query.
>> -so now there's 3 rows in the layout. Table1_Group1
>Header, Detail, and
>>Table1_Group1 Footer.
>>5) Type something into the table1_group1 header,
>> -Edit the group and check off
>>6) Drag a field into the body.
>>7) type something into the table1_Group1 footer.
footer
>only shows up at the
>>end of each group change and does not repeat on each
>page.
>>8) Now, make the detail row long, so that it just
>squeezes the table1_Group1
>>footer onto the next page in the print preview.
>>The Table1_Group1 Header will show up on the first
page
>along with the
>>detail row, but in the next page, only the
>table1_Group1 footer row shows.
>>The table1_Group1 header does not appear even though
it
>should repeat on each
>>page.
>>This happens in the Print Preview (NOT THE PREVIEW TAB
>OR WHEN VIEWING
>>ONLINE) and also when you export the report to PDF.
>Viewing the report just
>>online is fine.
>>For my real report, the table1_Group1 header always
>repeats fine except on
>>pages where only the table1_Group1 footer has been
>squeezed onto the next
>>page by itself.
>>Like I said, my report has 4 groups, each group gives
>subtotals at various
>>levels and the table1_Group1 Footer gives a grand
total
>for each set.
>>Hopefully the problem is clear now.
>>Thanks!
>>Brian.
>>"William Wang[MSFT]" wrote:
>> Hi Brian,
>> I'd like to get a clear understanding of your issue
>to
>> be able to assist you better.
>> >I have a report that uses one table in the body.
The
>> actual report header is
>> >empty and the actual report footer only shows the
>page
>> number. I use the
>> 1. In Reporting Services there is no sections called
>> Report Header or Report Footer although the black
>space
>> above/below the data region can be regarded as
Report
>> Header/Report Footer. Since "the actual report
footer
>> only shows the page number", I'd like to confirm if
>you
>> mean Page Header/Page Footer by saying Report
>> Header/Report Footer.
>>
>> >Table header and have "Repeat on New Page" set to
>True.
>> I use the table
>> >header instead because I need to display some
>summary
>> data at the top of each
>> >page.
>> 2. This is the second one I'd like to confirm. You
>set
>> the "RepeatOnNewPage" property of the Table Header
>> rather than the Group Header to true.
>>
>> >I have 4 groups total and the header of the
>outermost
>> group (table1_group1)
>> >is the one that repeats on each page.
>> 3. How did you do with the outermost group named
>> table1_group1 so that it repeats on each page? Did
>you
>> set the "RepeatOnNewPage" property of the Group
>Header
>> of this group to true or place a "Page Break at end"
>of
>> the group?
>>
>> >The reports display fine most of the time and the
>> Table1_group1 table header
>> 4. Here by saying "the Table1_group1 table header"
do
>> you mean the "group header of Table1_group1"?
>>
>> >repeats as it should, but whenever the spacing
works
>> out that the
>> >Table1_Footer1 table footer needs to start on a new
>> page,
>> 5. A Table Footer only appears at the end of a
table.
>> I'd like to confirm if you mean Table Footer or
Group
>> Footer of Table1_group1.
>>
>> >the footer prints
>> >on its own without the table header. The
>table1_Group1
>> table footer does not
>> >repeat on each page and only shows up as the last
>page
>> for each group as it
>> >shows some totals/sums.
>> >Any ideas on how to fix this so the Table_1Group1
>table
>> header will print
>> >even when only the Table1Group1 table footer is
>being
>> printed?
>> 6. Setting the "RepeatOnNewPage" property of the
>Group
>> Header to true should have no such a problem. By
>doing
>> this the group header repeats on each page.
>> For us, the most efficient troubleshooting method
>would
>> be reproducing this issue on our end. If the issue
>> persists, please try to reproduce the issue with a
>> sample database and post the detailed reproduce
steps.
>> If anything is unclear, get in touch.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> Get Secure! - <www.microsoft.com/security>
>> =====================================================>> When responding to posts, please "Reply to Group"
via
>> your newsreader so that others may learn and benefit
>> from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties,
>and
>> confers no rights.
>> --
>> >Thread-Topic: Orphaned Table Footers
>> >thread-index: AcUCWF6Szxan+iN7TLGB155pQjDGbg==>> >X-WBNR-Posting-Host: 204.92.98.25
>> >From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>> >Subject: Orphaned Table Footers
>> >Date: Mon, 24 Jan 2005 13:05:01 -0800
>> >Lines: 20
>> >Message-ID:
>> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE
V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> >Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:40664
>> >X-Tomcat-NG:
microsoft.public.sqlserver.reportingsvcs
>> >
>> >Hello.
>> >
>> >I have a report that uses one table in the body.
The
>> actual report header is
>> >empty and the actual report footer only shows the
>page
>> number. I use the
>> >Table header and have "Repeat on New Page" set to
>True.
>> I use the table
>> >header instead because I need to display some
>summary
>> data at the top of each
>> >page.
>> >I have 4 groups total and the header of the
>outermost
>> group (table1_group1)
>> >is the one that repeats on each page.
>> >The reports display fine most of the time and the
>> Table1_group1 table header
>> >repeats as it should, but whenever the spacing
works
>> out that the
>> >Table1_Footer1 table footer needs to start on a new
>> page, the footer prints
>> >on its own without the table header. The
>table1_Group1
>> table footer does not
>> >repeat on each page and only shows up as the last
>page
>> for each group as it
>> >shows some totals/sums.
>> >Any ideas on how to fix this so the Table_1Group1
>table
>> header will print
>> >even when only the Table1Group1 table footer is
>being
>> printed?
>> >
>> >Thanks,
>> >Brian.
>> >
>>
>|||Hi William,
I have the same problem as Brian and need to find a solution.
Whilst I accept that pagination dictates that the footer will be forced
onto a new page, it seems to me that, where one section of the
group/table is rendered on a new page (in this case the footer), then
the header should also be repeated on that page.
Can you or the product team suggest a workaround for this issue ?
Dave Martin
William Wang[MSFT] wrote:
> Hi Brian,
> From the product team's feedback, in this case we've
> made the detail row tall enough that the header row plus
> the detail row plus the footer row is simply too big for
> a page.
> In the case of HTML and Preview, we use a flexible
> pagination algorithm that allows pages to grow somewhat
> to accommodate this sort of layout.
> In the case of PDF or Print Preview, we must respect the
> page size settings precisely.
> This is by design in Reporting Services. As an end user
> too, I can truly understand your concern about it.
> Currently we have some options for you regarding this
> issue:
> a. You may want to send a feature change request to
> mswish@.microsoft.com. By submitting your concern to
> mswish@.microsoft.com you can get your voice into the
> design plans for upcoming versions. Microsoft takes
> customer requests and suggestions very seriously. MSWISH
> is your voice to the development team. A significant
> number of the design change requests come from our
> customers through the MSWISH alias and the MSWISH web
> site. http://www.microsoft.com/mswish. I encourage you
> to submit this request.
> b. You can talk to the support engineers directly in
> Microsoft Product Support Services (PSS) to see if they
> can find a workaround or offer a fix. For a complete
> list of Microsoft Product Support Services phone
> numbers, please go to the following address on the World
> Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see
> http://support.microsoft.com for regional support phone
> numbers.
> HTH!
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
>> X-Tomcat-ID: 215340416
>> References:
> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
> <QmipjprAFHA.2768@.cpmsftngxa10.phx.gbl>
> <F2866859-4257-41B1-9F2F-FAACB7EEC085@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain
>> Content-Transfer-Encoding: 7bit
>> From: v-rxwang@.online.microsoft.com (William Wang[MSFT])
>> Organization: Microsoft
>> Date: Wed, 26 Jan 2005 10:18:13 GMT
>> Subject: RE: Orphaned Table Footers
>> X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> Message-ID: <8rTWYB5AFHA.2504@.cpmsftngxa10.phx.gbl>
>> Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> Lines: 273
>> Path: cpmsftngxa10.phx.gbl
>> Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:40883
>> NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
>> Hi Brian,
>> I will be looking into this issue and I will update you
>> once I have more information.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>>from your issue.
>> =====================================================>> Business-Critical Phone Support (BCPS) provides you
> with
>> technical phone support at no charge during critical
> LAN
>> outages or "business down" situations. This benefit is
>> available 24 hours a day, 7 days a week to all
> Microsoft
>> technology partners in the United States and Canada.
>> This and other support options are available here:
>> BCPS:
>> https://partner.microsoft.com/US/technicalsupport/suppor
> t
>> overview/40010469
>> Others:
>> https://partner.microsoft.com/US/technicalsupport/suppor
> t
>> overview/
>> If you are outside the United States, please visit our
>> International Support page:
>> http://support.microsoft.com/default.aspx?scid=%2fintern
> a
>> tional.aspx.
>> =====================================================>> This posting is provided "AS IS" with no warranties,
> and
>> confers no rights.
>> --
>> Thread-Topic: Orphaned Table Footers
>> thread-index: AcUDE9iY3e1yULFPQLmd8LylZ4RFtQ==>> X-WBNR-Posting-Host: 204.92.98.25
>> From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>> References:
>> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
>> <QmipjprAFHA.2768@.cpmsftngxa10.phx.gbl>
>> Subject: RE: Orphaned Table Footers
>> Date: Tue, 25 Jan 2005 11:27:02 -0800
>> Lines: 228
>> Message-ID:
>> <F2866859-4257-41B1-9F2F-FAACB7EEC085@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> charset="Utf-8"
>> Content-Transfer-Encoding: 7bit
>> X-Newsreader: Microsoft CDO for Windows 2000
>> Content-Class: urn:content-classes:message
>> Importance: normal
>> Priority: normal
>> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:40795
>> X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> Hello.
>> Sorry, I used some wrong terminology. Here are the
>> clarifications.
>> 1) -Page header is empty.
>> - it is the page footer that only shows the page number
>> 2) -Table header row is deleted (I don't use it).
>> - RepeatOnNewPage property of the Table1_Group1 header
>> is set to true. This
>> is the header that contains some summary data and I
>> want it to repeat on each
>> page.
>> - Table Footer is included. RepeatOnNewPage property
> is
>> set to TRUE. This is
>> just an empty row with a bottom border. This repeats
> on
>> each page to give the
>> table a bottom border on each page.
>>
>> 3) -Table1_Group1 Header RepeatOnNewPage Property is
>> TRUE
>> -When I go "Edit Group", Page break at start, include
>> group header, include
>> group footer, AND repeat group header are checked off.
>> So both "Repeat Group
>> Header" is checked and RepeatOnNewPage property is
> TRUE.
>> 4) - Yes, I mean the Group Header of Table1_Group1
>> 5) - Yes, I mean the Group Footer of Table1_Group1
>> I think this might be the problem:
>> The Group Headers will repeat on each page in the
> print
>> preview and when
>> exporting to PDF as long as the page displays data
> from
>> the Table Details
>> section or data from inner groups. But if the
> Outermost
>> group footer is
>> squeezed onto the next page by itself then the group
>> header does not show.
>> I was able to replicate this problem using a very
>> simple report.
>> Steps:
>> 1) Create a new report. have the data set return only
> 1
>> row to make things
>> simple (.EG Select Top 1 * from table).
>> 2) Create a table in the body of the report
>> 3) Get rid of the table header and the table footer.
> so
>> only the table
>> details section remains.
>> 4) Add a group. Group on anything since only 1 row is
>> returned by the query.
>> -so now there's 3 rows in the layout. Table1_Group1
>> Header, Detail, and
>> Table1_Group1 Footer.
>> 5) Type something into the table1_group1 header,
>> -Edit the group and check off
>> 6) Drag a field into the body.
>> 7) type something into the table1_Group1 footer.
> footer
>> only shows up at the
>> end of each group change and does not repeat on each
>> page.
>> 8) Now, make the detail row long, so that it just
>> squeezes the table1_Group1
>> footer onto the next page in the print preview.
>> The Table1_Group1 Header will show up on the first
> page
>> along with the
>> detail row, but in the next page, only the
>> table1_Group1 footer row shows.
>> The table1_Group1 header does not appear even though
> it
>> should repeat on each
>> page.
>> This happens in the Print Preview (NOT THE PREVIEW TAB
>> OR WHEN VIEWING
>> ONLINE) and also when you export the report to PDF.
>> Viewing the report just
>> online is fine.
>> For my real report, the table1_Group1 header always
>> repeats fine except on
>> pages where only the table1_Group1 footer has been
>> squeezed onto the next
>> page by itself.
>> Like I said, my report has 4 groups, each group gives
>> subtotals at various
>> levels and the table1_Group1 Footer gives a grand
> total
>> for each set.
>> Hopefully the problem is clear now.
>> Thanks!
>> Brian.
>> "William Wang[MSFT]" wrote:
>> Hi Brian,
>> I'd like to get a clear understanding of your issue
>> to
>> be able to assist you better.
>> I have a report that uses one table in the body.
> The
>> actual report header is
>> empty and the actual report footer only shows the
>> page
>> number. I use the
>> 1. In Reporting Services there is no sections called
>> Report Header or Report Footer although the black
>> space
>> above/below the data region can be regarded as
> Report
>> Header/Report Footer. Since "the actual report
> footer
>> only shows the page number", I'd like to confirm if
>> you
>> mean Page Header/Page Footer by saying Report
>> Header/Report Footer.
>>
>> Table header and have "Repeat on New Page" set to
>> True.
>> I use the table
>> header instead because I need to display some
>> summary
>> data at the top of each
>> page.
>> 2. This is the second one I'd like to confirm. You
>> set
>> the "RepeatOnNewPage" property of the Table Header
>> rather than the Group Header to true.
>>
>> I have 4 groups total and the header of the
>> outermost
>> group (table1_group1)
>> is the one that repeats on each page.
>> 3. How did you do with the outermost group named
>> table1_group1 so that it repeats on each page? Did
>> you
>> set the "RepeatOnNewPage" property of the Group
>> Header
>> of this group to true or place a "Page Break at end"
>> of
>> the group?
>>
>> The reports display fine most of the time and the
>> Table1_group1 table header
>> 4. Here by saying "the Table1_group1 table header"
> do
>> you mean the "group header of Table1_group1"?
>>
>> repeats as it should, but whenever the spacing
> works
>> out that the
>> Table1_Footer1 table footer needs to start on a new
>> page,
>> 5. A Table Footer only appears at the end of a
> table.
>> I'd like to confirm if you mean Table Footer or
> Group
>> Footer of Table1_group1.
>>
>> the footer prints
>> on its own without the table header. The
>> table1_Group1
>> table footer does not
>> repeat on each page and only shows up as the last
>> page
>> for each group as it
>> shows some totals/sums.
>> Any ideas on how to fix this so the Table_1Group1
>> table
>> header will print
>> even when only the Table1Group1 table footer is
>> being
>> printed?
>> 6. Setting the "RepeatOnNewPage" property of the
>> Group
>> Header to true should have no such a problem. By
>> doing
>> this the group header repeats on each page.
>> For us, the most efficient troubleshooting method
>> would
>> be reproducing this issue on our end. If the issue
>> persists, please try to reproduce the issue with a
>> sample database and post the detailed reproduce
> steps.
>> If anything is unclear, get in touch.
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> Get Secure! - <www.microsoft.com/security>
>> =====================================================>> When responding to posts, please "Reply to Group"
> via
>> your newsreader so that others may learn and benefit
>> from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties,
>> and
>> confers no rights.
>> --
>> Thread-Topic: Orphaned Table Footers
>> thread-index: AcUCWF6Szxan+iN7TLGB155pQjDGbg==>> X-WBNR-Posting-Host: 204.92.98.25
>> From: "=?Utf-8?B?QnJpYW4=?=" <Bri-Guy@.nospam.nospam>
>> Subject: Orphaned Table Footers
>> Date: Mon, 24 Jan 2005 13:05:01 -0800
>> Lines: 20
>> Message-ID:
>> <C6FB5F66-C1CB-41A0-A449-620826ECF7D7@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> charset="Utf-8"
>> Content-Transfer-Encoding: 7bit
>> X-Newsreader: Microsoft CDO for Windows 2000
>> Content-Class: urn:content-classes:message
>> Importance: normal
>> Priority: normal
>> X-MimeOLE: Produced By Microsoft MimeOLE
> V6.00.3790.0
>> Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:40664
>> X-Tomcat-NG:
> microsoft.public.sqlserver.reportingsvcs
>> Hello.
>> I have a report that uses one table in the body.
> The
>> actual report header is
>> empty and the actual report footer only shows the
>> page
>> number. I use the
>> Table header and have "Repeat on New Page" set to
>> True.
>> I use the table
>> header instead because I need to display some
>> summary
>> data at the top of each
>> page.
>> I have 4 groups total and the header of the
>> outermost
>> group (table1_group1)
>> is the one that repeats on each page.
>> The reports display fine most of the time and the
>> Table1_group1 table header
>> repeats as it should, but whenever the spacing
> works
>> out that the
>> Table1_Footer1 table footer needs to start on a new
>> page, the footer prints
>> on its own without the table header. The
>> table1_Group1
>> table footer does not
>> repeat on each page and only shows up as the last
>> page
>> for each group as it
>> shows some totals/sums.
>> Any ideas on how to fix this so the Table_1Group1
>> table
>> header will print
>> even when only the Table1Group1 table footer is
>> being
>> printed?
>> Thanks,
>> Brian.
>>
>