Skip to Content

This blog post is for those that have a need to create a true table of contents (TOC) on a report. In particular, you need to print to paper in a specific order and an index style report won’t work for you. For those that don’t need the ordered paper option but still need an end user guide showing page numbers and providing hyperlinks to those pages, please see this post on creating an index report which is easier to build. Untitled.gif

This particular table of contents method is a bit of extra work as it involves the creation of a stored procedure that will perform a write back to a new table which houses the report and page information. You may have seen some older SAP Notes that told you that you could do a write-back using a Command object in Crystal Reports…while this is still possible in MS SQL Server (at least it was the last time I checked) it is no longer possible in other databases such as MS Access or MySQL that previously allowed this. This is not really due to a change in the way Crystal works, but, as I have been told, due to a change in what’s allowed through the ODBC layer by these particular database manufacturers. However, that really shouldn’t deter you as writing the stored procedure should be quite similar to what you’d put in the Command object anyways. You may have to bribe your DBA to create the stored procedure and the new table though if you don’t any create rights on your database.

Please open up the attachment to this blog and change the .txt extension to .rpt and you will then have the sample report that this blog post is based on. It is based on a HANA database and so the syntax used to create the stored procedure, the table, etc. are based on HANA SQL. Please consult your database help for syntax applicable to your database type.

(1) The first step is to create a database table that will house the TOC data. It should have one column for each level that you want to show in the TOC…in my case, there are two columns to hold the group levels, one for Company and the other for Product. Other columns that need to be added are a Report Name column (in case you’ve got multiple reports that need a TOC or multiple versions of the same report), a date-time stamp (that will be used later to ensure that you’ve only got the latest records for the TOC in your database), and a column for the Page Number.

In my case i created the table using this syntax and column types:

     CREATE TABLE TOC (RPTNAME VARCHAR(50), GROUP1 VARCHAR(50), GROUP2 VARCHAR(50), REVDATE SECONDDATE, PAGENUM INT);

(2) The second step is to create the stored procedure that will populate the table you just created. The stored proc should have input parameters / variables for all of the columns and should also have a method of deleting the old grouping and page number information for a report so that the table doesn’t get larger than necessary.

CREATE PROCEDURE BUILDTOC (IN INRPTNAME TEXT, IN INGROUP1 TEXT, IN INGROUP2 TEXT, IN INREVDATE TEXT, IN INPAGENUM INT)

LANGUAGE SQLSCRIPT AS

BEGIN

DELETE FROM TOC WHERE RPTNAME = :INRPTNAME AND TO_SECONDDATE (:INREVDATE, ‘YYYY-MM-DD HH24:MI:SS’) > REVDATE;

    INSERT INTO TOC

    SELECT

        :INRPTNAME,

        :INGROUP1,

        :INGROUP2,

        TO_SECONDDATE (:INREVDATE, ‘YYYY-MM-DD HH24:MI:SS’),

        :INPAGENUM

    FROM DUMMY;

    SELECT * FROM TOC WHERE RPTNAME = :INRPTNAME;

END

(3) The third step is one that’s done in your report. You will want to Insert a Subreport that will be placed in the Report Header of your report. This subreport will be the table of contents and will be based on the new table that you’ve added to your database.

Untitled.gif

In this subreport you’ll want to add groups for each of the grouping columns that you added to your table. In the sample case, Company and Product would be Group 1 & Group 2. You will also want to add the page number to the lowest level group that you have and then Underlay the highest level groups so that you end up with a TOC like the first pic on this blog post.

Untitled.gif

You will need to create a new Formula so that you can link this subreport to your main report. In the sample, look at the main report’s Formula list and you’ll see a linkReportName formula. This formula will consist simply of the Report Name that you want to use as a reference in the database and will be used again in the next step. The link below will ensure that the records in this new TOC subreport will be the ones for the current report.

Untitled.gif

(4) The next step involves Inserting another Subreport…this one will be connected to the Stored Procedure that you wrote and will have the job of writing the report information to your table. In the sample report, Group Footer 2b has been suppressed…the reason is so that you can open up the report and view each page without being prompted for a database connection that the write-back stored proc in the subreport would be looking for.

This subreport is going to be placed in the lowest level grouping (in a group footer) for the table of contents. This means that for every product, a subreport will run the stored procedure and add a record to the database table. There is a way to roll up all of the information for the entire report and then write back all of this information in a Report Footer, but that’s more complicated and so we won’t get into that in this post.

Untitled.gif

This subreport, as mentioned before is connected to the BuildTOC stored procedure.

Untitled.gif

You’re not quite done with this subreport though as it must be linked from the main report. If you look at the subreport links, you will see that each of the stored procedure’s input parameters / variables will have a link from the main report. A formula has also been created to provide the stored proc a Time Stamp (so that the time of the write-back is recorded and can be used later by the stored proc to delete the older records) as well as a formula that grabs the page number.

Untitled.gif

Note that this linkPageNumber formula is placed on the group header for the lowest group that you want in the TOC.

Untitled.gif

(5) Now you are more or less done with the creation of the TOC in your report. You will now have to refresh the report several times…by several times, I mean refresh the report 3 times. The first time will write the records to the database but the TOC will be blank. The second time the TOC will have records but if the TOC is more than one page then the TOC values will not be correct. The third time you refresh the report the TOC will have the accurate information.

Hopefully this blog post will help you to create a TOC for your report or it may convince you that an Index report is less work and perhaps is more functional for your case. And for those that are interested in learning more about SAP HANA, please be sure to drop by the SAP HANA Academy to learn more about HANA for free.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Mohamed Ibrahim

    I have done the same using SQL Command in the “Write TOC” subreport that does everything.

    Drops the table if exist, Creates the table, Populates the fields.

    All in one place.

    (0) 
    1. Jamie Wiseman Post author

      hi Mohamed, thanks for your comment.

      Yes, write-backs used to be possible in most databases and some report developers used to do the same thing that you have mentioned with the Create Update Read & Delete all in a single Command object.

      Those were the good old days as you wouldn’t need to write anything at the database end as you’d be writing everything right inside the .rpt Command object instead. In my opinion that was very convenient. So you’re lucky that you’re still using a DB where you can do this for now.

      Now write-backs only work in a handful of databases. Most databases no longer allow the CUD of CRUD operations over the connectivity layer or allow multiple commands in their queries.

      cheers,

      -jamie

      (0) 
        1. Jamie Wiseman Post author

          hey Mohamed,

          If you’ve got the time you may wish to write a blog post including a sample of the Command you did and also mention the DB you’re using.

          It’s an interesting topic and many report developers would probably benefit from this. Plus it’s much more interesting as it’s all done in a single Command right in Crystal vs. having to use a stored proc etc.

          -jamie

          (0) 
          1. Mohamed Ibrahim

            Hi Jamie,

            Thanks for your interest!

            I am not trying to be disappointing but I believe there is no need for a new blog post since you already explained the whole logic here, the only difference would be as follow:

            Using Crystal Reports 2008 on SQL Server 2008

            1- Creating a formula @Evaluation where you declare a string variable in the GF2 that evaluates and stores values in the group

            as following:

            WhilePrintingRecords;

            StringVar TOCRecords := TOCRecords + “(‘ ” + {@Field1} + ” ‘, ‘ ” + {@Field2} + ” ‘, ” +ToText(PageNumber,0) + If OnLastRecord Then “)” Else “), “;

            2- Creating a formula @TOCScript that displays the output of that variable in the RF as following:

            WhilePrintingRecords;

            StringVar TOCRecords;

            “If Object_ID (‘dbo.TOC’) Is Not Null Drop Table dbo.TOC ” + “Create Table dbo.TOC ( Field1 NVarChar(Max), Field2 NVarChar(Max), Page# Int) ” + “Insert TOC Values ” + TOCRecords;

            3- Create a “Write TOC” sub-report where you create a SQL command where you create a string parameter and code the following in the command:

            {?MyParameter} Select * From TOC

            4- Link the main report and “Write TOC” subreport using @TOCScript from the main report and the @MyParameter from the sub-report

            Now there is a very important fact about the concept of creating a table of content in crystal reports which is:

            Logically you will not be able to get the right information for your TOC unless you instruct the user to run the report then jump to last page then jump back to first page and refresh the report once

            There is no way around this because simply this is how crystal reports functions multi pass reporting process

            In other words you can not expect data in the report header that is based on the report footer unless you process the report footer first.

            Hope this will be of any help to anyone!

            (0) 
            1. Pradeep Negi

              Hello,

                I am using Crystal Report 2013 with Oracle 11.2.0, But not able to get TOC. I am able to do this when I used SQL Server 2005 but now we are migrating to Oracle and it does not work Any idea if this does not support Oracle?.

              (0) 
              1. Mohamed Ibrahim

                Hi Pradeep,

                I am not sure where you exactly stuck but in general I believe unless data ain’t available any requirement can be done, therefore, I am sure there is an alternative way in Oracle to accomplish this.

                Good luck!

                (0) 

Leave a Reply