?
Solved

CR11 and Table of Contents

Posted on 2005-03-21
25
Medium Priority
?
556 Views
Last Modified: 2011-10-03
Dear Sirs,

As I am going more and more into the CR, I have a problem I coulnd't find a solution by myself: how to create a TOC (table of contents) in a master report?

So, I have a big master report, where I will include 50-60 other subreports. Most of those sub-reports have a fixed length (one page) but some of them have a variable length (from one page to many more).

The idea is that I have to create in the begining of the entire report a table of contents - to say on which page is starting wich report (from the user point of view, the subreports are just sections).

Have any solutions?

Thanks!
Ovidiu
0
Comment
Question by:is77dia2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
  • +1
25 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 172 total points
ID: 13599164
Hi is77dia2,
Not straightforward.
You must be able to create a new table in your database (or somewhere).

http://support.businessobjects.com/library/kbase/articles/c2011950.asp


Pete
0
 

Author Comment

by:is77dia2
ID: 13599330
Hi Pete,

that "solution" from the link you sent, I have already checked but I can not apply it to my report because:
1) I do not have groups in my report
2) I am working with SUB-REPORTS inside of the master report

So, any other solution is welcome to give it a try!
Thanks!

Ovidiu
0
 
LVL 19

Assisted Solution

by:GJParker
GJParker earned 164 total points
ID: 13600057
pete's solution may not suit you r purpose exactly but the underlying principal is the same, you need to insert a TOC subreport which takes it's data from a table which is updated the first time the report is previewed.

You will probably need to create a formula to create the INSERT string and place this in each subreport section to build a final string to link to the subreport.

In short the technique is there but you will need to tailor it to suit your own needs.

Gary

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:is77dia2
ID: 13600164
ok. you might be right... but I couldn't understand from that page how should look like the SQL subreport source to delete/insert the new page number.
Maybe I am not familiar with MS SQL Server. I am working only with Oracle 9i/10g.

Can someone support me?
thansk again

ovidiu
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13602022
You could create a simple table with one record for each subreport.

Create a main report that groups on these records, and insert each subreport in the group footer of its related record/group.

Then, you can simply use the original approach.

Cheers,
- Ido
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13602261
Good idea Ido

Gary
0
 

Author Comment

by:is77dia2
ID: 13602410
sorry for bothering, but i don't know (yet) how to create those formulae that will write back into the database some piece of info.
can you support also me in this?

Thanks.
Ovidiu
0
 
LVL 23

Assisted Solution

by:Ido Millet
Ido Millet earned 164 total points
ID: 13602479
It should also be noted that at least one of the 3rd-party Crystal report managers listed at: http://www.kenhamady.com/bookmarks.html can generate a Table of Contents in PDF exports without needing to write to a database.  

Cheers,
- Ido
0
 

Author Comment

by:is77dia2
ID: 13619390
Dear Ido,
i've check I think all of the links from the  http://www.kenhamady.com/bookmarks.html but I couldn;t find at least one to do what I need to do...

So, i have tried to find the way how to launch a SQL INSERT/DELETE statement from CR, but I couldn;t find that way. In the link http://support.businessobjects.com/library/kbase/articles/c2011950.asp there is a paragraph that says DELETE statement should go on the Add command. When I have tried to create a report having a very simple DELETE statement in the  ADD COMMAND, I got an error, error I am not receiving when I perform the DELETE from the SQL Plus command prompt!

So - I still need your support!

Thanks!

Ovidiu
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13619735
the reason you get the error is because the add command sql must always result in a recordset so you cannot run the delete by itself. If you look at the example you'll see it has 3 parts

{?LinkReceiver}
DELETE FROM "TableOfContents"
WHERE "TableOfContents"."DateTime" <> {?DateTime};
SELECT * FROM "TableOfContents"

{?LinkReceiver}  - this is the INSERT String built from the linkmaker formula in each group header

DELETE FROM "TableOfContents"
WHERE "TableOfContents"."DateTime" <> {?DateTime};  - This deletes any old records from the database

SELECT * FROM "TableOfContents"  - This returns a rcordset of the TOC.

Hope this helps

Gary
0
 

Author Comment

by:is77dia2
ID: 13620412
Dear Gary,
Dear Ido,


I think that I have a biiiig understanding problem:
The ADD COMMAND dialg box DOES NOT allow me to input there MORE than ONE SQL statement, that means:
IF I add in that add command box JUST the next two SQLs:

DELETE FROM my_table WHERE key='{?param}';
SELECT Field1, Field1 from my_table

(so ONLY one parameter created: param)

when I press the OK button to accept those TWO only statements I get the next "Invalid Character" ORA-00911 error.
It seems that CR doesn't like AT ALL the ";" character in that "add command" box.

So, I have tried:
DELETE FROM my_table WHERE key={'param'}
SELECT Field1, Field1 from my_table

(w/o the ";" after the first DELETE statement) --> it doesn't work too. Error: SQL not properly ended - an ORA-00933 error

DELETE FROM my_table WHERE key={'param'};
SELECT Field1, Field1 from my_table;

(w/ the ";" after the first DELETE statement) --> it doesn't work too Error: "Invalid Character"  - an ORA-00911 error

So, the question is:
How should i supposed to make CR accept that 3 lines you mentioned:
{?LinkReceiver}
DELETE FROM "TableOfContents"
WHERE "TableOfContents"."DateTime" <> {?DateTime};
SELECT * FROM "TableOfContents"

????

now I am really confused.....

Ovidiu
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13620527
You definitley need the semicolon to separate the statements, What connection method are you using to the oracle database ? this may be a restriction of the connection.

Can you try runnign the SQL from a seperate query analyser tool using different connection methods to check the syntax. I don't really use Oracle so can't help much where the syntax is concerned.

Gary
0
 

Author Comment

by:is77dia2
ID: 13620781
Hi there,

Unfortuntatelly I can not post a screenshot to show it to you.
But shortly: From the "Create New Connection" menu tree item, I choose the "Oracle Server" item.
Dublu clicking on this item, to create a new one, I got a dialog box prompting me for the next info:
1) Service (the Oracle DB name)
2) User ID: the Oracle Schema name
3) Password

That's it!

If would be to run those two SQLs from outside of CR, I should have a ";" after EACH/BOTH statements.
And ofc, I have tested several times the correctness of that two SQLs launching them from other Oracle  query analyser tools. And they are OK!!!!

Finnaly I can not apply the suggested solution 'cause I can not go over that step!

Looking forward for further suggestions!

Ovidiu
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13620869
If this SQL runs in the query analyser

DELETE FROM my_table WHERE key='{?param}';
SELECT Field1, Field1 from my_table

as long as you are using the same connection method and the end result is a recordset, then it should work in CR. make sure you are using the same connection method as you used in the query analyser if you are using ODBC make sure you use ODBC in Crystal.

What version of CR are you using ? make sure you have the latest service packs and hotfixes applied.

Gary
0
 

Author Comment

by:is77dia2
ID: 13620938
I am not using ODBC connection at all! Neither in CR neither elsewhere!

And I have tested BOTH
1) CR Developer XI (No SP or hotfixes of CR XI applied so far...)
2) CR Advanced FULL 9.2.3.970

os: Win XP Pro.


Ovidiu
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13621059
My point was to make sure you are using the same connection for both tools. If the SQL is valid then it must be a problem with teh connection method.

try running the sql with no parameters i.e.

DELETE FROM my_table WHERE (Key = 'abcxyz');
SELECT * FROM my_table

Gary

0
 

Author Comment

by:is77dia2
ID: 13621434
BOTH SQLs launched from outside of CR, either individually, either both, are running w/o any problem.

and anyhow, i have done before tens of other reports. And with lot more complex (single) queries in the ADD COMMAND box - and I have tested all of them in my query analyser tool!

Ovidiu
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13621528
If you can't get it to work using the same connection method then you could look at creating a stored procedure with input parameters to accomplish this.

Gary
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13621532
Unless I'm in a particularly good dream, there is definitly one Report Manager listed under the link I had provided above that can generate a TOC (as well as Bookmarks) in PDF exports from Crystal.  

The 2-statements in a single Command approach should work.  I would start by testing that you can do this against the sample database that comes with Crystal, using a simple ODBC connection. If not, that probably something is wrong with Crystal 11.

Cheers,
- Ido
 
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13621617
EE rules forbid Ido in mentioning his own products, but as I am in no way associated with the company that produce this application, I can tell you that the Report manager Ido talks about is VisualCut

http://www.milletsoftware.com/visualcutBenefit.htm

Like the new website Ido.

Gary



0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 13621644
Thanks  :o)
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13856839
Split points  GJParke, IdoMillet, Pete57r

Gary
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question