Creating a Table of Contents (TOC) in a Crystal Report – some HANA sql, and some info on write-backs

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.

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 this sample report. 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.

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.

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.

(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.

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

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.

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

(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.