Creating an "in memory" database

Hi everyone,
I am writing an application for which there are several large internal
collections whose contents have to be filtered and sorted in various ways.
To really take the cake, the contents of the collections may change during
the process (objects coming from other threads).
The number of objects in each collection is fairly high (up to several
thousand) but not so high that I don't anticipate being able to have the
whole model in memory at one time. But it is large enough that I'm going t
o
have to use some kind of indexed access to avoid having to re-sort the
collections all the time.
So a database seems to be warranted here, but there isn't really a
requirement to persist the collections to disk (at least not in this part of
the app) and I don't want to incur the overhead of reading and writing
records to disk. So my questions...
Is there a way to tell SQL Server to create an "in memory" database? I've
looked at "temporary" files, but these look like regular SQL Server database
files that just are automatically dropped at the end of the session. I want
tables/indexes/views that are only contained in memory and get dropped at th
e
end of the session.
Do I really need to worry about this? It's my impression that SQL Server
only writes records to disk when it really needs to anyway, so is my
perception that disk read/writes would slow down the app unfounded?
Thanks for your help.
BBMyuo cant create "in memory" database...
but, you can create "in memory" tempdb
or you can use "in memory" tables - variables
or... optimize your query and use regular db
"BBM" wrote:

> Hi everyone,
> I am writing an application for which there are several large internal
> collections whose contents have to be filtered and sorted in various ways.
> To really take the cake, the contents of the collections may change during
> the process (objects coming from other threads).
> The number of objects in each collection is fairly high (up to several
> thousand) but not so high that I don't anticipate being able to have the
> whole model in memory at one time. But it is large enough that I'm going
to
> have to use some kind of indexed access to avoid having to re-sort the
> collections all the time.
> So a database seems to be warranted here, but there isn't really a
> requirement to persist the collections to disk (at least not in this part
of
> the app) and I don't want to incur the overhead of reading and writing
> records to disk. So my questions...
> Is there a way to tell SQL Server to create an "in memory" database? I've
> looked at "temporary" files, but these look like regular SQL Server databa
se
> files that just are automatically dropped at the end of the session. I wa
nt
> tables/indexes/views that are only contained in memory and get dropped at
the
> end of the session.
> Do I really need to worry about this? It's my impression that SQL Server
> only writes records to disk when it really needs to anyway, so is my
> perception that disk read/writes would slow down the app unfounded?
> Thanks for your help.
> BBM|||SQL Server doesn't support memory-only databases but that isn't really
a problem in terms of performance as SQL Server makes extensive use of
cacheing. If your data is small enough and created only during a single
session then most reads will probably be from cache anyway.
For optimum performance it doesn't make much sense to create and drop
databases and tables at runtime. Create the empty tables you need at
installation and then populate them a runtime. If required you can
always delete the data afterwards but there's no real reason to drop
the tables since you will presumably only have to create them again
later.
As you only need a small-footprint DB, you could consider using MSDE
for this.
http://www.microsoft.com/sql/msde/
David Portas
SQL Server MVP
--|||If these records are to be updated often, then your biggest concern will not
be time it takes to query but rather record locking and other concurrency
issues. Just start off with the idea of an ordinary table, and implement
minimal indexing, becuase several thousand records is actually not a lot, it
depends on the total length of the record, and updating indexes could cause
more concurrency issues. Also, read up on options for transaction isolation
level in BOL. Using "set transaction level read uncommitted" will result in
the least record locking.
What will be the maximum number of records in this table assuming growth
over the next year? When a process queries the table, is it important that
they pull the absolute most recent updates from other processes? Perhaps one
process will not have a need to query across another processes updates?
"BBM" <bbm@.bbmcompany.com> wrote in message
news:FC96FA9F-0ED6-4D26-9B66-0B02A576EB2A@.microsoft.com...
> Hi everyone,
> I am writing an application for which there are several large internal
> collections whose contents have to be filtered and sorted in various ways.
> To really take the cake, the contents of the collections may change during
> the process (objects coming from other threads).
> The number of objects in each collection is fairly high (up to several
> thousand) but not so high that I don't anticipate being able to have the
> whole model in memory at one time. But it is large enough that I'm going
to
> have to use some kind of indexed access to avoid having to re-sort the
> collections all the time.
> So a database seems to be warranted here, but there isn't really a
> requirement to persist the collections to disk (at least not in this part
of
> the app) and I don't want to incur the overhead of reading and writing
> records to disk. So my questions...
> Is there a way to tell SQL Server to create an "in memory" database? I've
> looked at "temporary" files, but these look like regular SQL Server
database
> files that just are automatically dropped at the end of the session. I
want
> tables/indexes/views that are only contained in memory and get dropped at
the
> end of the session.
> Do I really need to worry about this? It's my impression that SQL Server
> only writes records to disk when it really needs to anyway, so is my
> perception that disk read/writes would slow down the app unfounded?
> Thanks for your help.
> BBM|||Thanks to all the responders. You all had good input. Right now I'm going
to proceed just using regular SQL Server Tables/Indexes until I prove to
myself that performance is an issue. I was hoping that there was some way t
o
tell SQL Server to keep a table in memory, but I guess there's not.
Is there a way to tell SQL Server to keep it's cache at a certain size? I'm
familiar with DB2 and in DB2 you can do that by table. Essentially you can
set the cache size for a table so large that the entire table becomes memory
resident.
I am intrigued by some of Aleksandar's responses. Could you elaborate on
what you had in mind with "in memory" temporary tables?
Thanks again for your responses.
BBM
"JT" wrote:

> If these records are to be updated often, then your biggest concern will n
ot
> be time it takes to query but rather record locking and other concurrency
> issues. Just start off with the idea of an ordinary table, and implement
> minimal indexing, becuase several thousand records is actually not a lot,
it
> depends on the total length of the record, and updating indexes could caus
e
> more concurrency issues. Also, read up on options for transaction isolatio
n
> level in BOL. Using "set transaction level read uncommitted" will result i
n
> the least record locking.
> What will be the maximum number of records in this table assuming growth
> over the next year? When a process queries the table, is it important that
> they pull the absolute most recent updates from other processes? Perhaps o
ne
> process will not have a need to query across another processes updates?
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:FC96FA9F-0ED6-4D26-9B66-0B02A576EB2A@.microsoft.com...
> to
> of
> database
> want
> the
>
>|||Please view my response to JT below... Thanks.
"Aleksandar Grbic" wrote:
> yuo cant create "in memory" database...
> but, you can create "in memory" tempdb
> or you can use "in memory" tables - variables
> or... optimize your query and use regular db
> "BBM" wrote:
>|||Please see my reply to JT below...
Thanks.
"David Portas" wrote:

> SQL Server doesn't support memory-only databases but that isn't really
> a problem in terms of performance as SQL Server makes extensive use of
> cacheing. If your data is small enough and created only during a single
> session then most reads will probably be from cache anyway.
> For optimum performance it doesn't make much sense to create and drop
> databases and tables at runtime. Create the empty tables you need at
> installation and then populate them a runtime. If required you can
> always delete the data afterwards but there's no real reason to drop
> the tables since you will presumably only have to create them again
> later.
> As you only need a small-footprint DB, you could consider using MSDE
> for this.
> http://www.microsoft.com/sql/msde/
> --
> David Portas
> SQL Server MVP
> --
>|||You can set minimum and maximum values for the RAM used by SQL Server
(sp_configure or change it in Enterprise Manager). Data will still be
written to disk however - you cannot avoid this. Even creating
temporary tables will cause data to be written to the tempdb log file.
The point is that with adequate RAM you shouldn't have to spend much
time waiting for disk reads and writes.
As JT indicated, however, there are issues that you should consider
much more important than disk usage. Good database design and
well-written code are far more important factors in determining overall
performance. A bad design or poorly written code can kill even a small
database.
David Portas
SQL Server MVP
--|||"BBM" <bbm@.bbmcompany.com> wrote in message
news:8C8DC765-3E23-403B-A206-E4ED44CB5117@.microsoft.com...
> Thanks to all the responders. You all had good input. Right now I'm
going
> to proceed just using regular SQL Server Tables/Indexes until I prove to
> myself that performance is an issue. I was hoping that there was some way
to
> tell SQL Server to keep a table in memory, but I guess there's not.
> Is there a way to tell SQL Server to keep it's cache at a certain size?
I'm
> familiar with DB2 and in DB2 you can do that by table. Essentially you
can
> set the cache size for a table so large that the entire table becomes
memory
> resident.
> I am intrigued by some of Aleksandar's responses. Could you elaborate on
> what you had in mind with "in memory" temporary tables?
> Thanks again for your responses.
>
There are several methods that you can use.
1. Create the tempdb in memory and then use it. (Not necessarily a
preferred solution.)
2. Use table level variables in your stored procedures. (Not necessarily a
preferred solution.)
3. Use DBCC PINTABLE and UNPINTABLE for tables to live in memory once read
from disk. (Not necessarily a preferred solution.)
4. Preferred solution -- If the entire database is has a small footprint,
then just use regular SQL Server tables and indexes to create and work with
everything. Once data and index pages are read in to memory, unless SQL
Server needs more RAM for something, they will not be flushed back to disk.
Ensure that SQL Server has enough memory to keep everything in memory.
Testing will tell, but I have a feeling that all of the extra work for
PINTABLE, and/or table level variables etc. will probably NOT outperform
letting SQL Server manage itself.
Rick Sawtell
MCT, MCSD, MCDBA|||David and Rick,
Thanks again for the clarifications.
I have already taken JT's concerns into account. My original description of
my problem was inaccurate and you guys are justified in worrying about
concurrency. In actuality, records would be added to the database only by
my central process. Other processes notify my process that they have an ite
m
that needs to be inserted, but they that do it by raising an event that is
handled by my running process and it does the update. As it performs the
update, my main process decides whether the insert affects what it has been
doing and possibly starts over.
Lots of interesting stuff in your responses, but I think I'll take your
advice and make sure I have a problem before I start jumping through hoops.
Thanks again.
BBM
"Rick Sawtell" wrote:

> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:8C8DC765-3E23-403B-A206-E4ED44CB5117@.microsoft.com...
> going
> to
> I'm
> can
> memory
> There are several methods that you can use.
> 1. Create the tempdb in memory and then use it. (Not necessarily a
> preferred solution.)
> 2. Use table level variables in your stored procedures. (Not necessarily
a
> preferred solution.)
> 3. Use DBCC PINTABLE and UNPINTABLE for tables to live in memory once rea
d
> from disk. (Not necessarily a preferred solution.)
> 4. Preferred solution -- If the entire database is has a small footprint
,
> then just use regular SQL Server tables and indexes to create and work wit
h
> everything. Once data and index pages are read in to memory, unless SQL
> Server needs more RAM for something, they will not be flushed back to disk
.
> Ensure that SQL Server has enough memory to keep everything in memory.
> Testing will tell, but I have a feeling that all of the extra work for
> PINTABLE, and/or table level variables etc. will probably NOT outperform
> letting SQL Server manage itself.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>

creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

sql

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Page Index and table of contents

This is the problem I am facing with the SQL server reporting services,
I am trying to create a report where in we have to
display Page index and the table of contents Along with the Page
Number. This report contains the list of products under a subcategory
which in turn are under particular Categories. The Page Index should
display the products names in the alphabetical order with the page
number where It falls, this is similar to the appendix at the end of
any textbook and the table of contents display the category and
its subcategories with page numbers Now, the problem is reading the
report dynamically to find out the page numbers where this product
falls and the categories falls . I want a solution for displaying the
Page index and the table of contents in SQL server reporting services
2005 version.
Waiting for quick sujjestions or help in this regardAre you using web service approach?
If so,you can always get page content before displaying it and by analyzing
the underlying HTML get all information you need -
page number, total number of pages, any internal error occurred, etc. Based
on that information you can build your own page header with a custom page
index.
"Aparna" <aparna.cirigiri@.gmail.com> wrote in message
news:1135255357.206805.159890@.g44g2000cwa.googlegroups.com...
> This is the problem I am facing with the SQL server reporting services,
>
> I am trying to create a report where in we have to
> display Page index and the table of contents Along with the Page
> Number. This report contains the list of products under a subcategory
> which in turn are under particular Categories. The Page Index should
> display the products names in the alphabetical order with the page
> number where It falls, this is similar to the appendix at the end of
> any textbook and the table of contents display the category and
> its subcategories with page numbers Now, the problem is reading the
> report dynamically to find out the page numbers where this product
> falls and the categories falls . I want a solution for displaying the
> Page index and the table of contents in SQL server reporting services
> 2005 version.
> Waiting for quick sujjestions or help in this regard
>