Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

CR11 and Table of Contents

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
is77dia2
Asked:
is77dia2
  • 9
  • 8
  • 4
  • +1
3 Solutions
 
peter57rCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
is77dia2Author Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
GJParkerCommented:
Good idea Ido

Gary
0
 
is77dia2Author Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
 
is77dia2Author Commented:
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
 
GJParkerCommented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
GJParkerCommented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Thanks  :o)
0
 
GJParkerCommented:
Split points  GJParke, IdoMillet, Pete57r

Gary
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now