| Author |
Topic |
|
tobbylee1
Starting Member
6 Posts |
 Posted - 2005-03-17 : 14:06:40
|
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 would contain a list of doctors and locations, but 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, this was possible by using the print event, in which you could 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-17 : 14:11:31
|
| It sounds like you need two reports that point to different stored procedures. In the first report, you'll have a stored procedure that produces the list of physicians. In the second report, you'll have a stored procedure that accepts the physiciansID or whatever you are using for a PK. Then in the first report, you'll create a jump to on the physician field so that it jumps to this second report and passes in the physicianID. Does that make sense? Have you worked with the jump to option in RS?Tara |
 |
|
|
tobbylee1
Starting Member
6 Posts |
Posted - 2005-03-17 : 14:26:52
|
| I haven't worked with the jump option is RS. Please excuse me, but I'm new to RS and just got it installed and running on a test server on our network a couple weeks ago.Your method sounds interesting, could you please elaborate? How would you go about passing the page numbers and physician ID's from the first report to the second report with the jump option. And, where do I access the jump option. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-17 : 14:30:29
|
| You would first need to create each stored procedure and each report. Then on the first report, right click on the physician field, go to properties, click advanced, go to navigation tab, then check out the Jump to report option. When you configure that option, you tell it the name of the jump to report and what to pass it.Tara |
|
|
|
tobbylee1
Starting Member
6 Posts |
Posted - 2005-03-17 : 15:34:48
|
| Great!!! I see...thanks for your input Tara, you've been very helpful. One more question for you, say I use this method to generate the data for the table of contents. In the first report, is it possible to put in a Sub report (table of contents) before the table object that generates the data for the table of contents and set an execution order so that the data is generated before the sub report for the Table of Contents is run? If so, how do you set the execution order? I couldn't find the option to do so. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-17 : 15:58:00
|
| Yes you can put in a subreport, but I'm not sure in what order RS executes it.Tara |
|
|
|
tobbylee1
Starting Member
6 Posts |
Posted - 2005-03-17 : 16:02:19
|
| Blah, too bad you can't set the execution order of the objects in a RS report.Thanks again for all your input. |
|
|
|
tobbylee1
Starting Member
6 Posts |
Posted - 2005-03-31 : 17:04:07
|
| Hi Again Tara,OK...I've tried it this way but I can't seem to get it to work. Here are my problems.1. When I selected the physician field, properties, and went to the Navigation Tab, I set the Parameters via the Parameters button (in report 1). When I run the report, it doesn't automatically populate the database fields via report2. It only creates links on report1 to report2. If I click on these links, the database gets populated so I know it works.Is there a way to have report1 automatically run report2 for each instance of the field so that the Table of Contents DB table gets populated, and also I don't want to display report2 at all?2. Secondly, I run into the same problem I did before. When I set the parameters from the navigation option to be the Globals!Pagenumber value, I get the error that Globals are only accessible in the header or footer.What am I doing wrong....Can you please explain. Thanks in advance. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-31 : 17:21:32
|
| 1) For that functionality you'd need to use a subreport instead of a jumpto report. Subreport is an object that you can find in the left hand side of Visual Studio in the toolbox.2) Seems as though Reporting Services doesn't allow you to do what you want for globals. Maybe the next version has this.Tara |
|
|
|
tobbylee1
Starting Member
6 Posts |
Posted - 2005-03-31 : 17:42:40
|
| Hmmm...that's too bad....I really need a web solution that can produce a report with a Table of Contents.Do you know if there are any other reporting tools (Cyrstal ?) that I can use to accomplish this task. Or perhaps a way to do this with .Net programming. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-31 : 17:50:57
|
| I don't do any programming except via T-SQL, some VBScript, and very little VB.NET as I'm a DBA. The only reporting tool that I have used so far is Reporting Services. So I can't help you with suggesting an alternative solution.Tara |
|
|
|
|
Create Dynamic Table of Contents