Create Dynamic Table of Contents and Indexes

I was wondering if someone can tell me if there is a way to create a
dynamic table of contents and index for a report that is run.
For example, I need to create a directory of physicians based on a search
query of our database. So each report run would contain a different list of
doctors and locations, and on each query of the report the doctors would
show up on different pages of the report. I want to be able to create a
table of contents and an index for the report that will list the page
number the doctor first shows up on.
With MS Access VBA, I was able to do this by using the print event, in
which I would add each record to the access database table which contains
the table of contents, while the report was printing and therefore creating
the table of contents.
Is there a way (similiar) with SQL RS that I can accomplish this task?
--
Message posted via http://www.sqlmonster.comNot currently. The closest thing available right now is a Document Map.
This creates a bunch of bookmarks when you export to PDF, so that would
create an indexed report/PDF for you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff005f8e72c242a99456548fc63e62f0@.SQLMonster.com...
>I was wondering if someone can tell me if there is a way to create a
> dynamic table of contents and index for a report that is run.
> For example, I need to create a directory of physicians based on a search
> query of our database. So each report run would contain a different list
> of
> doctors and locations, and on each query of the report the doctors would
> show up on different pages of the report. I want to be able to create a
> table of contents and an index for the report that will list the page
> number the doctor first shows up on.
> With MS Access VBA, I was able to do this by using the print event, in
> which I would add each record to the access database table which contains
> the table of contents, while the report was printing and therefore
> creating
> the table of contents.
> Is there a way (similiar) with SQL RS that I can accomplish this task?
> --
> Message posted via http://www.sqlmonster.com|||Thanks for your response Jeff. Do you know if Crystal Reports or any other
reporting solution can handle the task of creating a Table of Contents and
document Index?
--
Message posted via http://www.sqlmonster.com|||Crystal Reports can do it, but it has to put it at the last page (it
generates the page numbers as it goes and accumulates them for display when
finished printing the records). Not an ideal solution for a TOC.
Brian Bischof
www.CrystalReportsBook.com
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> Thanks for your response Jeff. Do you know if Crystal Reports or any other
> reporting solution can handle the task of creating a Table of Contents and
> document Index?
> --
> Message posted via http://www.sqlmonster.com|||This is a multipart message in MIME format
--Xananews.1.2.3
I was given a 'sleazy hack' to do this in RS, it requires the report to
be run twice once so it can work out the page numbers, second time it
can use them in the table of contents. If the table is going at the end
you may be able to change to a run once version! Good luck!
I never said it would be easy!
I've attached the file, and copied it below for those viewing via web.
-- --
Unfortunately, there's currently no good way to build a table of
contents.
And even adding defined execution order isn't going to help, since
pagination happens as a completely separate step from the rest of the
report. This is because you can request the same report with different
page
size/margin settings (and even an entirely different rendering target,
which
could radically change pagination). Under normal circumstances, we don't
want to force reexecution of the entire report just because you change
your
margins.
To handle table of contents type operations, we're going to (eventually)
have to implement an entirely new mode wherein we know that the body of
the
report contains references to page numbers, which will force the entire
report to reexecute if pagination information changes.
In the mean time, there's no general solution for the table of contents
problem.
But... Since you're doing this in a batch once a month, you may not
need a
fully general solution.
Without further ado, here's Sleazy Hack #792 ("Simulating Table of
Contents
in Batch Reporting Scenarios"):
1. Write a custom assembly containing a class that can write rows to a
TableOfContents table in your database.
Have shared methods for both initializing the TOC and writing a new
row
to the TOC.
Note: Don't forget to give it database permissions in the CAS file
when
you deploy it to your server.
See the documentation for details on custom assemblies.
2. In the Code section of the report, call the TOC initialization in
the
OnInit() event.
3. At the start of each section you want to appear in your TOC, put a
hidden textbox that contains the label you want to appear in your TOC.
4. In the page header, put a hidden textbox with something like the
following expression:
=Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
& ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
from step 3 (since only one will appear on any given page, the rest
will be
empty)
5. Add a dataset to your report which selects from the TableOfContents
table
6. Display the results of the TOC data set in a table at the end of
your
report*
7. Run the report twice. The first time will initialize the TOC. The
second time will use the values from the previous run.
* If you want it at the beginning, you'll either need to run the report
three times (the first to get some rows into the TOC table, the second
to
get the numbers populated correctly and the third to use those numbers)
or
you'll need to make sure the TOC table has the right number of rows to
begin
with (perhaps by not emptying it from the previous month) otherwise your
page numbers will be incorrect due to the TOC pushing things around.
This
isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
however.
-- ---
Brian Bischof wrote:
> Crystal Reports can do it, but it has to put it at the last page (it
> generates the page numbers as it goes and accumulates them for
> display when finished printing the records). Not an ideal solution
> for a TOC.
>
> Brian Bischof
> www.CrystalReportsBook.com
>
> "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > Thanks for your response Jeff. Do you know if Crystal Reports or
> > any other reporting solution can handle the task of creating a
> > Table of Contents and document Index?
> >
> > --
> > Message posted via http://www.sqlmonster.com
--Xananews.1.2.3
Content-Type: Application/Octet-Stream; name=Sleazy Hack #792.txt
Content-Transfer-Encoding: base64
VW5mb3J0dW5hdGVseSwgdGhlcmUncyBjdXJyZW50bHkgbm8gZ29vZCB3YXkgdG8gYnVpbGQgYSB0
YWJsZSBvZiBjb250ZW50cy4NCg0KQW5kIGV2ZW4gYWRkaW5nIGRlZmluZWQgZXhlY3V0aW9uIG9y
ZGVyIGlzbid0IGdvaW5nIHRvIGhlbHAsIHNpbmNlDQpwYWdpbmF0aW9uIGhhcHBlbnMgYXMgYSBj
b21wbGV0ZWx5IHNlcGFyYXRlIHN0ZXAgZnJvbSB0aGUgcmVzdCBvZiB0aGUNCnJlcG9ydC4gIFRo
aXMgaXMgYmVjYXVzZSB5b3UgY2FuIHJlcXVlc3QgdGhlIHNhbWUgcmVwb3J0IHdpdGggZGlmZmVy
ZW50IHBhZ2UNCnNpemUvbWFyZ2luIHNldHRpbmdzIChhbmQgZXZlbiBhbiBlbnRpcmVseSBkaWZm
ZXJlbnQgcmVuZGVyaW5nIHRhcmdldCwgd2hpY2gNCmNvdWxkIHJhZGljYWxseSBjaGFuZ2UgcGFn
aW5hdGlvbikuIFVuZGVyIG5vcm1hbCBjaXJjdW1zdGFuY2VzLCB3ZSBkb24ndA0Kd2FudCB0byBm
b3JjZSByZWV4ZWN1dGlvbiBvZiB0aGUgZW50aXJlIHJlcG9ydCBqdXN0IGJlY2F1c2UgeW91IGNo
YW5nZSB5b3VyDQptYXJnaW5zLg0KVG8gaGFuZGxlIHRhYmxlIG9mIGNvbnRlbnRzIHR5cGUgb3Bl
cmF0aW9ucywgd2UncmUgZ29pbmcgdG8gKGV2ZW50dWFsbHkpDQpoYXZlIHRvIGltcGxlbWVudCBh
biBlbnRpcmVseSBuZXcgbW9kZSB3aGVyZWluIHdlIGtub3cgdGhhdCB0aGUgYm9keSBvZiB0aGUN
CnJlcG9ydCBjb250YWlucyByZWZlcmVuY2VzIHRvIHBhZ2UgbnVtYmVycywgd2hpY2ggd2lsbCBm
b3JjZSB0aGUgZW50aXJlDQpyZXBvcnQgdG8gcmVleGVjdXRlIGlmIHBhZ2luYXRpb24gaW5mb3Jt
YXRpb24gY2hhbmdlcy4NCg0KSW4gdGhlIG1lYW4gdGltZSwgdGhlcmUncyBubyBnZW5lcmFsIHNv
bHV0aW9uIGZvciB0aGUgdGFibGUgb2YgY29udGVudHMNCnByb2JsZW0uDQoNCkJ1dC4uLiBTaW5j
ZSB5b3UncmUgZG9pbmcgdGhpcyBpbiBhIGJhdGNoIG9uY2UgYSBtb250aCwgeW91IG1heSBub3Qg
bmVlZCBhDQpmdWxseSBnZW5lcmFsIHNvbHV0aW9uLg0KDQpXaXRob3V0IGZ1cnRoZXIgYWRvLCBo
ZXJlJ3MgU2xlYXp5IEhhY2sgIzc5MiAoIlNpbXVsYXRpbmcgVGFibGUgb2YgQ29udGVudHMNCmlu
IEJhdGNoIFJlcG9ydGluZyBTY2VuYXJpb3MiKToNCg0KMS4gIFdyaXRlIGEgY3VzdG9tIGFzc2Vt
Ymx5IGNvbnRhaW5pbmcgYSBjbGFzcyB0aGF0IGNhbiB3cml0ZSByb3dzIHRvIGENClRhYmxlT2ZD
b250ZW50cyB0YWJsZSBpbiB5b3VyIGRhdGFiYXNlLg0KICAgIEhhdmUgc2hhcmVkIG1ldGhvZHMg
Zm9yIGJvdGggaW5pdGlhbGl6aW5nIHRoZSBUT0MgYW5kIHdyaXRpbmcgYSBuZXcgcm93DQp0byB0
aGUgVE9DLg0KICAgIE5vdGU6ICBEb24ndCBmb3JnZXQgdG8gZ2l2ZSBpdCBkYXRhYmFzZSBwZXJt
aXNzaW9ucyBpbiB0aGUgQ0FTIGZpbGUgd2hlbg0KeW91IGRlcGxveSBpdCB0byB5b3VyIHNlcnZl
ci4NCiAgICBTZWUgdGhlIGRvY3VtZW50YXRpb24gZm9yIGRldGFpbHMgb24gY3VzdG9tIGFzc2Vt
YmxpZXMuDQoyLiAgSW4gdGhlIENvZGUgc2VjdGlvbiBvZiB0aGUgcmVwb3J0LCBjYWxsIHRoZSBU
T0MgaW5pdGlhbGl6YXRpb24gaW4gdGhlDQpPbkluaXQoKSBldmVudC4NCjMuICBBdCB0aGUgc3Rh
cnQgb2YgZWFjaCBzZWN0aW9uIHlvdSB3YW50IHRvIGFwcGVhciBpbiB5b3VyIFRPQywgcHV0IGEN
CmhpZGRlbiB0ZXh0Ym94IHRoYXQgY29udGFpbnMgdGhlIGxhYmVsIHlvdSB3YW50IHRvIGFwcGVh
ciBpbiB5b3VyIFRPQy4NCjQuICBJbiB0aGUgcGFnZSBoZWFkZXIsIHB1dCBhIGhpZGRlbiB0ZXh0
Ym94IHdpdGggc29tZXRoaW5nIGxpa2UgdGhlDQpmb2xsb3dpbmcgZXhwcmVzc2lvbjoNCiAgICA9
Q29kZS5NeVRPQ0NsYXNzLkFkZFRvVE9DKEdsb2JhbHMuUGFnZU51bWJlcixSZXBvcnRJdGVtcyFU
ZXh0Ym94MS5WYWx1ZQ0KJiBSZXBvcnRJdGVtcyFUZXh0Ym94Mi5WYWx1ZSAmIFJlcG9ydEl0ZW1z
IVRleHRib3gzLlZhbHVlKQ0KICAgIFRleGJveDEsIFRleHRib3gyIGFuZCBUZXh0Ym94MyBhcmUg
dGhlIG5hbWVzIG9mIHRoZSBoaWRkZW4gdGV4dGJveGVzDQpmcm9tIHN0ZXAgMyAoc2luY2Ugb25s
eSBvbmUgd2lsbCBhcHBlYXIgb24gYW55IGdpdmVuIHBhZ2UsIHRoZSByZXN0IHdpbGwgYmUNCmVt
cHR5KQ0KNS4gIEFkZCBhIGRhdGFzZXQgdG8geW91ciByZXBvcnQgd2hpY2ggc2VsZWN0cyBmcm9t
IHRoZSBUYWJsZU9mQ29udGVudHMNCnRhYmxlDQo2LiAgRGlzcGxheSB0aGUgcmVzdWx0cyBvZiB0
aGUgVE9DIGRhdGEgc2V0IGluIGEgdGFibGUgYXQgdGhlIGVuZCBvZiB5b3VyDQpyZXBvcnQqDQo3
LiAgUnVuIHRoZSByZXBvcnQgdHdpY2UuICBUaGUgZmlyc3QgdGltZSB3aWxsIGluaXRpYWxpemUg
dGhlIFRPQy4gIFRoZQ0Kc2Vjb25kIHRpbWUgd2lsbCB1c2UgdGhlIHZhbHVlcyBmcm9tIHRoZSBw
cmV2aW91cyBydW4uDQoNCiogSWYgeW91IHdhbnQgaXQgYXQgdGhlIGJlZ2lubmluZywgeW91J2xs
IGVpdGhlciBuZWVkIHRvIHJ1biB0aGUgcmVwb3J0DQp0aHJlZSB0aW1lcyAodGhlIGZpcnN0IHRv
IGdldCBzb21lIHJvd3MgaW50byB0aGUgVE9DIHRhYmxlLCB0aGUgc2Vjb25kIHRvDQpnZXQgdGhl
IG51bWJlcnMgcG9wdWxhdGVkIGNvcnJlY3RseSBhbmQgdGhlIHRoaXJkIHRvIHVzZSB0aG9zZSBu
dW1iZXJzKSBvcg0KeW91J2xsIG5lZWQgdG8gbWFrZSBzdXJlIHRoZSBUT0MgdGFibGUgaGFzIHRo
ZSByaWdodCBudW1iZXIgb2Ygcm93cyB0byBiZWdpbg0Kd2l0aCAocGVyaGFwcyBieSBub3QgZW1w
dHlpbmcgaXQgZnJvbSB0aGUgcHJldmlvdXMgbW9udGgpIG90aGVyd2lzZSB5b3VyDQpwYWdlIG51
bWJlcnMgd2lsbCBiZSBpbmNvcnJlY3QgZHVlIHRvIHRoZSBUT0MgcHVzaGluZyB0aGluZ3MgYXJv
dW5kLiAgVGhpcw0KaXNuJ3QgYW4gaXNzdWUgaWYgeW91ciBUT0MgaXMgb25seSBvbmUgcGFnZSBs
b25nIGFuZCBoYXMgUGFnZUJyZWFrQXRFbmQsDQpob3dldmVyLg0KDQo=--Xananews.1.2.3--|||You would think that with day and age, Microsoft would be smart enough to
figure out that people might want to create a table of contents.
Thanks Chris, I'll give it a try.
--
Message posted via http://www.sqlmonster.com|||Yeah, but I cut the MS guys some slack on RS features. They have created a
tremendous reporting platform from the ground up in just a couple of years.
I hear they were going to release it with SQL 2005, but bumped it up due to
early demand. Gotta like that. In any case, I'm having fun with it. All
in all, even with its limitations, it's better than any other reporting
solution I've used to date.
</rahrah>
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b01f2ab3065d4d0bb7b57874d7543d59@.SQLMonster.com...
> You would think that with day and age, Microsoft would be smart enough to
> figure out that people might want to create a table of contents.
> Thanks Chris, I'll give it a try.
> --
> Message posted via http://www.sqlmonster.com|||I second that. I ditched the last CR report from our organisation two
weeks ago. I even had a pint or two to celebrate!
I always thought CR was overpriced for what it was, funnily enough now
I don't use it I seem to be getting free upgrades from them! Do you
think they are a little worried? ;)
Chris
Jeff A. Stucker wrote:
> Yeah, but I cut the MS guys some slack on RS features. They have
> created a tremendous reporting platform from the ground up in just a
> couple of years.
> I hear they were going to release it with SQL 2005, but bumped it up
> due to early demand. Gotta like that. In any case, I'm having fun
> with it. All in all, even with its limitations, it's better than any
> other reporting solution I've used to date.
> </rahrah>|||Chris...
Do you have some sample code or can you point me to something in the online
reference on how to add rows to a table from a custom class.
Dave
"Chris McGuigan" wrote:
> I was given a 'sleazy hack' to do this in RS, it requires the report to
> be run twice once so it can work out the page numbers, second time it
> can use them in the table of contents. If the table is going at the end
> you may be able to change to a run once version! Good luck!
> I never said it would be easy!
> I've attached the file, and copied it below for those viewing via web.
> -- --
> Unfortunately, there's currently no good way to build a table of
> contents.
> And even adding defined execution order isn't going to help, since
> pagination happens as a completely separate step from the rest of the
> report. This is because you can request the same report with different
> page
> size/margin settings (and even an entirely different rendering target,
> which
> could radically change pagination). Under normal circumstances, we don't
> want to force reexecution of the entire report just because you change
> your
> margins.
> To handle table of contents type operations, we're going to (eventually)
> have to implement an entirely new mode wherein we know that the body of
> the
> report contains references to page numbers, which will force the entire
> report to reexecute if pagination information changes.
> In the mean time, there's no general solution for the table of contents
> problem.
> But... Since you're doing this in a batch once a month, you may not
> need a
> fully general solution.
> Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> Contents
> in Batch Reporting Scenarios"):
> 1. Write a custom assembly containing a class that can write rows to a
> TableOfContents table in your database.
> Have shared methods for both initializing the TOC and writing a new
> row
> to the TOC.
> Note: Don't forget to give it database permissions in the CAS file
> when
> you deploy it to your server.
> See the documentation for details on custom assemblies.
> 2. In the Code section of the report, call the TOC initialization in
> the
> OnInit() event.
> 3. At the start of each section you want to appear in your TOC, put a
> hidden textbox that contains the label you want to appear in your TOC.
> 4. In the page header, put a hidden textbox with something like the
> following expression:
> =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
> & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
> from step 3 (since only one will appear on any given page, the rest
> will be
> empty)
> 5. Add a dataset to your report which selects from the TableOfContents
> table
> 6. Display the results of the TOC data set in a table at the end of
> your
> report*
> 7. Run the report twice. The first time will initialize the TOC. The
> second time will use the values from the previous run.
> * If you want it at the beginning, you'll either need to run the report
> three times (the first to get some rows into the TOC table, the second
> to
> get the numbers populated correctly and the third to use those numbers)
> or
> you'll need to make sure the TOC table has the right number of rows to
> begin
> with (perhaps by not emptying it from the previous month) otherwise your
> page numbers will be incorrect due to the TOC pushing things around.
> This
> isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
> however.
> -- ---
>
> Brian Bischof wrote:
> > Crystal Reports can do it, but it has to put it at the last page (it
> > generates the page numbers as it goes and accumulates them for
> > display when finished printing the records). Not an ideal solution
> > for a TOC.
> >
> >
> > Brian Bischof
> > www.CrystalReportsBook.com
> >
> >
> >
> > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> > news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > any other reporting solution can handle the task of creating a
> > > Table of Contents and document Index?
> > >
> > > --
> > > Message posted via http://www.sqlmonster|||Never mind I misread your solution the first time.
Dave
"Dlloyd" wrote:
> Chris...
> Do you have some sample code or can you point me to something in the online
> reference on how to add rows to a table from a custom class.
> Dave
> "Chris McGuigan" wrote:
> > I was given a 'sleazy hack' to do this in RS, it requires the report to
> > be run twice once so it can work out the page numbers, second time it
> > can use them in the table of contents. If the table is going at the end
> > you may be able to change to a run once version! Good luck!
> > I never said it would be easy!
> >
> > I've attached the file, and copied it below for those viewing via web.
> >
> > -- --
> >
> > Unfortunately, there's currently no good way to build a table of
> > contents.
> >
> > And even adding defined execution order isn't going to help, since
> > pagination happens as a completely separate step from the rest of the
> > report. This is because you can request the same report with different
> > page
> > size/margin settings (and even an entirely different rendering target,
> > which
> > could radically change pagination). Under normal circumstances, we don't
> > want to force reexecution of the entire report just because you change
> > your
> > margins.
> > To handle table of contents type operations, we're going to (eventually)
> > have to implement an entirely new mode wherein we know that the body of
> > the
> > report contains references to page numbers, which will force the entire
> > report to reexecute if pagination information changes.
> >
> > In the mean time, there's no general solution for the table of contents
> > problem.
> >
> > But... Since you're doing this in a batch once a month, you may not
> > need a
> > fully general solution.
> >
> > Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> > Contents
> > in Batch Reporting Scenarios"):
> >
> > 1. Write a custom assembly containing a class that can write rows to a
> > TableOfContents table in your database.
> > Have shared methods for both initializing the TOC and writing a new
> > row
> > to the TOC.
> > Note: Don't forget to give it database permissions in the CAS file
> > when
> > you deploy it to your server.
> > See the documentation for details on custom assemblies.
> > 2. In the Code section of the report, call the TOC initialization in
> > the
> > OnInit() event.
> > 3. At the start of each section you want to appear in your TOC, put a
> > hidden textbox that contains the label you want to appear in your TOC.
> > 4. In the page header, put a hidden textbox with something like the
> > following expression:
> >
> > =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Value
> > & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> > Texbox1, Textbox2 and Textbox3 are the names of the hidden textboxes
> > from step 3 (since only one will appear on any given page, the rest
> > will be
> > empty)
> > 5. Add a dataset to your report which selects from the TableOfContents
> > table
> > 6. Display the results of the TOC data set in a table at the end of
> > your
> > report*
> > 7. Run the report twice. The first time will initialize the TOC. The
> > second time will use the values from the previous run.
> >
> > * If you want it at the beginning, you'll either need to run the report
> > three times (the first to get some rows into the TOC table, the second
> > to
> > get the numbers populated correctly and the third to use those numbers)
> > or
> > you'll need to make sure the TOC table has the right number of rows to
> > begin
> > with (perhaps by not emptying it from the previous month) otherwise your
> > page numbers will be incorrect due to the TOC pushing things around.
> > This
> > isn't an issue if your TOC is only one page long and has PageBreakAtEnd,
> > however.
> >
> > -- ---
> >
> >
> > Brian Bischof wrote:
> >
> > > Crystal Reports can do it, but it has to put it at the last page (it
> > > generates the page numbers as it goes and accumulates them for
> > > display when finished printing the records). Not an ideal solution
> > > for a TOC.
> > >
> > >
> > > Brian Bischof
> > > www.CrystalReportsBook.com
> > >
> > >
> > >
> > > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> > > news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > > any other reporting solution can handle the task of creating a
> > > > Table of Contents and document Index?
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster|||Hi,
I haven't actually used this hack so I don't have an RDL file for you.
Try Tobby Lee, he said he was going to try it out.
If you are stuck creating the custom assembly, have a look at this site;
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx?Pending=true
There are loads of ways to add rows to tables in VB (sorry I don't do
C).
I prefer sending raw SQL commands, but that's just the way I am!
If you're using old ADO (2.6 - 2.8) you will need a connection object
first then you can create a command object and issue a SQL 'insert'
statement. Below is an example BUT it is not 'best practice' nor
particularly efficient in the form I have given you. It should be
enough to get you going though, I'd suggest you get a book on the lines
of SQL for Developers or on ADO.Net (or earlier);
You will need to add a reference to an ADO namespace to your project
for this to work. ADO.Net is significantly different to previous
versions of ADO.
Heres the code;
Public Sub AddLine(Value1 As String, Value2 As String)
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
'Open a connection to the database
'
oCon.ConnectionString = "Provider=SQLOLEDB; Data Source=YourServer;
InitialCatalog=YourDB; User Id=yourid; Password=yourpassword"
oCon.Open
'Set up a command to do the INSERT
'
Set oCon.CommandText = "Insert Into YourTable (Fieldname1,
Fieldname2) Values ('" & Value1 & "','" & Value2 & "')"
'Actually INSERT the record
oCon.Execute
oCon.Close
Set oCon = Nothing
End Sub
Hope it helps ...
Chris
Dlloyd wrote:
> Chris...
> Do you have some sample code or can you point me to something in the
> online reference on how to add rows to a table from a custom class.
> Dave
> "Chris McGuigan" wrote:
> > I was given a 'sleazy hack' to do this in RS, it requires the
> > report to be run twice once so it can work out the page numbers,
> > second time it can use them in the table of contents. If the table
> > is going at the end you may be able to change to a run once
> > version! Good luck! I never said it would be easy!
> >
> > I've attached the file, and copied it below for those viewing via
> > web.
> >
> > -- --
> >
> > Unfortunately, there's currently no good way to build a table of
> > contents.
> >
> > And even adding defined execution order isn't going to help, since
> > pagination happens as a completely separate step from the rest of
> > the report. This is because you can request the same report with
> > different page
> > size/margin settings (and even an entirely different rendering
> > target, which
> > could radically change pagination). Under normal circumstances, we
> > don't want to force reexecution of the entire report just because
> > you change your
> > margins.
> > To handle table of contents type operations, we're going to
> > (eventually) have to implement an entirely new mode wherein we know
> > that the body of the
> > report contains references to page numbers, which will force the
> > entire report to reexecute if pagination information changes.
> >
> > In the mean time, there's no general solution for the table of
> > contents problem.
> >
> > But... Since you're doing this in a batch once a month, you may not
> > need a
> > fully general solution.
> >
> > Without further ado, here's Sleazy Hack #792 ("Simulating Table of
> > Contents
> > in Batch Reporting Scenarios"):
> >
> > 1. Write a custom assembly containing a class that can write rows
> > to a TableOfContents table in your database.
> > Have shared methods for both initializing the TOC and writing a
> > new row
> > to the TOC.
> > Note: Don't forget to give it database permissions in the CAS
> > file when
> > you deploy it to your server.
> > See the documentation for details on custom assemblies.
> > 2. In the Code section of the report, call the TOC initialization
> > in the
> > OnInit() event.
> > 3. At the start of each section you want to appear in your TOC,
> > put a hidden textbox that contains the label you want to appear in
> > your TOC. 4. In the page header, put a hidden textbox with
> > something like the following expression:
> >
> > =Code.MyTOCClass.AddToTOC(Globals.PageNumber,ReportItems!Textbox1.Va
> > lue & ReportItems!Textbox2.Value & ReportItems!Textbox3.Value)
> > Texbox1, Textbox2 and Textbox3 are the names of the hidden
> > textboxes from step 3 (since only one will appear on any given
> > page, the rest will be
> > empty)
> > 5. Add a dataset to your report which selects from the
> > TableOfContents table
> > 6. Display the results of the TOC data set in a table at the end of
> > your
> > report*
> > 7. Run the report twice. The first time will initialize the TOC.
> > The second time will use the values from the previous run.
> >
> > * If you want it at the beginning, you'll either need to run the
> > report three times (the first to get some rows into the TOC table,
> > the second to
> > get the numbers populated correctly and the third to use those
> > numbers) or
> > you'll need to make sure the TOC table has the right number of rows
> > to begin
> > with (perhaps by not emptying it from the previous month) otherwise
> > your page numbers will be incorrect due to the TOC pushing things
> > around. This
> > isn't an issue if your TOC is only one page long and has
> > PageBreakAtEnd, however.
> >
> > -- ---
> >
> >
> > Brian Bischof wrote:
> >
> > > Crystal Reports can do it, but it has to put it at the last page
> > > (it generates the page numbers as it goes and accumulates them for
> > > display when finished printing the records). Not an ideal solution
> > > for a TOC.
> > >
> > >
> > > Brian Bischof
> > > www.CrystalReportsBook.com
> > >
> > >
> > >
> > > "Tobby Lee via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> > > message news:7601e8aa56a14beba11468cd61e2ac41@.SQLMonster.com...
> > > > Thanks for your response Jeff. Do you know if Crystal Reports or
> > > > any other reporting solution can handle the task of creating a
> > > > Table of Contents and document Index?
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster