2012-10-24

Crystal Reports - Create index page - Part 1

I can create single or multiple chart, cross-tab and table in the stand-alone reports, and further make those reports become sub-reports and insert into the main report, then create cover page, page number to make whole report become complex booklet. The index function just like the last mile to composite a complete booklet/report.



Here is my starting concept/logic:

1. Create index variables at the beginning of the report.

2. Insert title values into variables while running reports.

3. Extract data from variables at the end of the report, and format these data to make it look like normal index, this is the only way to get value of every page/sub-report/chart executed in the report, this is first the pity on this design. (if you have any better idea or method, please feel free to let me know)

Next step, I need to think more details about the above concept. The normal indexes look like as follows:
Summary                                                                                                       3
1.   Introduction                                                                                           10
   1.1   Background                                                                                        10

Table 1.   Table1_Name                                                                                11

Figure 1.   Figure1_Name                                                                             13
There are 5 elements to composite index:

1. Title Number (e.g. NULL, 1., 1.1)

2. Title Name (e.g. Summary, Introduction, Background)

3. Title Page (e.g. 3, 10, 10)

4. Title Level (e.g. 1, 1, 2): For controlling indentation

In my case, I still have other types index in the report, like List of Tables and List of Figures, so I add one more elements:
5. Title Type (e.g. Chapter, Table, Figure)

Now I have the variable structure of index, I start to design the report:

1. Create a formula for initializing index variables: @Report_Index, inside this formula, type the following script:
WhilePrintingRecords;

Shared StringVar title_name;
Shared StringVar title_number;
Shared StringVar title_type;
Shared StringVar title_page;
Shared StringVar title_level;

//This part depends on title level structure, e.g. 1., 1.1, 1.1.1, 1.1.1.1, 1.1.1.1.1
Shared NumberVar level_1;
Shared NumberVar level_1_old;
Shared NumberVar level_2;
Shared NumberVar level_2_old;
Shared NumberVar level_3;
Shared NumberVar level_3_old;
Shared NumberVar level_4;
Shared NumberVar level_4_old;
Shared NumberVar level_5;

Shared NumberVar table_number;
Shared NumberVar figure_number;

Shared StringVar report_end_flag;
report_end_flag := 'N';

title_name := '';
title_number := '';
title_type := '';
title_page := '';
title_level := '';

level_1 := 0;
level_1_old := 0;
level_2 := 0;
level_2_old := 0;
level_3 := 0;
level_3_old := 0;
level_4 := 0;
level_4_old := 0;
level_5 := 0;

table_number := 0;
figure_number := 0;
2. Drag formula @Report_Index to the Report Header, and suppress the filed.

3. Because I have 3 types of index, once I encounter the place needed to create title for index, then I need to repeatedly insert related value into the variables, so I create 3 reusable sub-reports: SubReportForTitle.rpt, SubReportForTitle_Table.rpt and SubReportForTitle_Figure.rpt

In SubRerpotForTitle.rpt, create the following formulas:

(1) Title_Level1:
WhilePrintingRecords;

Shared StringVar title_name;
Shared StringVar title_number;
Shared StringVar title_type;
Shared StringVar title_page;
Shared StringVar title_level;
Shared NumberVar level_1;
Shared NumberVar level_1_old;
Shared NumberVar level_2;
Shared NumberVar level_2_old;
Shared NumberVar page_number;

//Every time drag this field in the report, and incrementally increase the title number
level_1 := level_1 + 1;
title_number := title_number + TOTEXT(level_1) + '.,';
title_name := title_name + 'Title_Name,';
title_type := title_type + 'Chapter,';
title_page := title_page + TOTEXT(page_number) + ',';
title_level := title_level + '1,';

TOTEXT(level_1) + '.   ' + 'Title_Name';
(2) Title_Level2:
WhilePrintingRecords;

Shared StringVar title_name;
Shared StringVar title_number;
Shared StringVar title_type;
Shared StringVar title_page;
Shared StringVar title_level;
Shared NumberVar level_1;
Shared NumberVar level_1_old;
Shared NumberVar level_2;
Shared NumberVar level_2_old;
Shared NumberVar level_3;
Shared NumberVar level_3_old;
Shared NumberVar page_number;

//If formula field Title_Level1 has been dragged in the report, then reset level_2
IF level_1_old <> level_1 THEN
    level_2 := 1
ELSE level_2 := level_2 + 1;

title_number := title_number + TOTEXT(level_1) + '.' + TOTEXT(level_2) + ',';
title_name := title_name + 'Title_Name,';
title_type := title_type + 'Chapter,';
title_page := title_page + TOTEXT(page_number) + ',';
title_level := title_level + '2,';

level_1_old := level_1;

TOTEXT(level_1) + '.' + TOTEXT(level_2) + '   Title_Name';
Formula (3)Title_Level3, (4)Title_Level4 and (5)Title_Level5 have the same concept for Title_Level2, just need to change target variable.

For title Summary, I insert SubReportForTitle.rpt into the main report. Inside the sub-report, I drag formula Title_Level1 in the report, because there is no title number, so remove variable level_1 in the script.

For title 1.   Introduction and 1.1   Background, insert SubReoportForTitle.rpt into the main report. Inside sub-report, I drag formula Title_Level1 and Title_Level2 in the report, edit them and change "Title_Name" to "Introduction" and "Background" in the script.

In SubRerpotForTitle_Table.rpt, create formula Table:
WhilePrintingRecords;

Shared StringVar title_name;
Shared StringVar title_number;
Shared StringVar title_type;
Shared StringVar title_page;
Shared StringVar title_level;
Shared NumberVar table_number;
Shared NumberVar page_number;

table_number := table_number + 1;
title_number := title_number & 'Table ' & TOTEXT(table_number) & '.,';
title_name := title_name & 'Table_Name,';
title_type := title_type & 'Table,';
title_page := title_page & TOTEXT(page_number) & ',';
title_level := title_level & '1,';

'Table ' & TOTEXT(table_number) & '.   ' & 'Table_Name';

In SubRerpotForTitle_Figure.rpt, create formula Figure:
WhilePrintingRecords;

Shared StringVar title_name;
Shared StringVar title_number;
Shared StringVar title_type;
Shared StringVar title_page;
Shared StringVar title_level;
Shared NumberVar figure_number;
Shared NumberVar page_number;
Shared StringVar financial_flag;

figure_number := figure_number + 1;
title_number := title_number & 'Figure ' & TOTEXT(figure_number) & '.,';
title_name := title_name & ' Figure_Name,';
title_type := title_type & 'Figure,';
title_page := title_page & TOTEXT(page_number) & ',';
title_level := title_level & '1,';

'Figure ' & TOTEXT(figure_number) & '.   ' & 'Figure_Name';
Next, I need to check page number, so I create extra formulas to debug those variables, for example, I created formula @Report_Index_Title_Name:
WhilePrintingRecords;
Shared StringVar title_name;
title_name;
, same concept for other variables, create a new Report Footer (debug page), and drag these formulas to see what those variables look like. I also drag a new created formula @Report_End_Flag in this page:
WhilePrintingRecords;
Shared StringVar report_end_flag;

report_end_flag := 'Y';
Because page number displays in every Page Footer, I need to consider about two parts:

1. Because retrieve those variables at the end of the report (the last section of report footer), but index page normally shows before introducing chapter, so I reserved page area in the report to make sure the following chapter show correct page number. For most of reports, I reserved three pages for Chapter/ Table/Figure index, and one page for each of them. I met a situation is that Figure index over two pages, then I have to manually reserved two pages for figure, that's second pity for this design. (once again, if you have any better idea or method, please feel free to let me know)

2. Instead of displaying current page number of index retrieving page, index itself also need to show correct/reserved page number. I created formula @PageNumber_TableOfContents:
WhilePrintingRecords;
Shared NumberVar tc_pagenumber;

IF tc_pagenumber = 0 THEN
    tc_pagenumber := PageNumber;

tc_pagenumber;
, drag this formula in the first page of reserved area. Instead of using built-in Page Number in the report footer, I created formula @Page_Number:
WhilePrintingRecords;
Shared NumberVar page_number;
Shared NumberVar tc_pagenumber;
Shared StringVar report_end_flag;

IF report_end_flag = 'N' THEN
    page_number := PageNumber
ELSE
(
    //Because I need catch current page number and pass it to the subreport, I also put this formula in the Page Header
    //After I catch which page is the last page of report, the formula show twice, that's why deduct 1
    page_number := tc_pagenumber - 1;
    tc_pagenumber := tc_pagenumber + 1;
);

page_number;
 , drag it in the Page Header for passing value to the sub-report, let sub-report continue page number , and also drag it in the Page Footer.

Last step is try to retrieve value from those variables and display it. I created a stored procedure dbo.uspReportIndex to split those string value into table value, the query as follows:
USE [Target_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspReportIndex]
--DECLARE   
    @arr_title_name        VARCHAR(MAX),
    @arr_title_number    VARCHAR(MAX),
    @arr_title_type        VARCHAR(MAX),   
    @arr_title_pgae        VARCHAR(MAX),
    @arr_title_level    VARCHAR(MAX)
AS   
    SET NOCOUNT ON;   

    --For Testing
    /*       
        SET @arr_title_name        = 'Summary,INTRODUCTION,BACKGROUND,...,...,'
        SET @arr_title_number     = ',1.,1.1,Table 1,...,Figure 1,...,...,'
        SET @arr_title_type          = 'Chapter,Chapter,Chapter,Table,...,Figure,...,...,'
        SET @arr_title_pgae         = '3,9,9,9,...,10,...,...,'
        SET @arr_title_level         = '1,1,2,1,...,2,...,,...,'
    */

    DECLARE @tmp_report_index TABLE
            (
                title_name        VARCHAR(500),
                title_number    VARCHAR(30),
                title_type        VARCHAR(10),               
                title_page        INT,
                title_level        INT
            )

    IF RIGHT(@arr_title_name, 1) = ','
        SET @arr_title_name = SUBSTRING(@arr_title_name, 1, LEN(@arr_title_name)-1)
   
    IF RIGHT(@arr_title_number, 1) = ','
        SET @arr_title_number = SUBSTRING(@arr_title_number, 1, LEN(@arr_title_number)-1)
   
    IF RIGHT(@arr_title_type, 1) = ','
        SET @arr_title_type = SUBSTRING(@arr_title_type, 1, LEN(@arr_title_type)-1)
   
    IF RIGHT(@arr_title_pgae, 1) = ','
        SET @arr_title_pgae = SUBSTRING(@arr_title_pgae, 1, LEN(@arr_title_pgae)-1)
   
    IF RIGHT(@arr_title_level, 1) = ','
        SET @arr_title_level = SUBSTRING(@arr_title_level, 1, LEN(@arr_title_level)-1)
           
    DECLARE @title_counts    INT,
            @atna_pos        INT,
            @atnu_pos        INT,
            @att_pos        INT,
            @atp_pos        INT,
            @atl_pos        INT

    SET        @title_counts    = 1
    SET        @atna_pos        = 0
    SET        @atnu_pos        = 0
    SET        @att_pos        = 0
    SET        @atp_pos        = 0
    SET        @atl_pos        = 0

    WHILE CHARINDEX(',', @arr_title_name, @atna_pos) > 0
    BEGIN
        SET @atna_pos        = CHARINDEX(',', @arr_title_name, @atna_pos) + 1
        SET @title_counts    = @title_counts + 1
    END

    --SELECT @title_counts
    SET @atna_pos = 0

    WHILE @title_counts > 0
    BEGIN
        IF @title_counts = 1 --Get the last item.
        BEGIN
            INSERT INTO @tmp_report_index
                        (   
                            title_name,
                            title_number,
                            title_type,
                            title_page,
                            title_level
                        )
            SELECT    RTRIM(LTRIM(SUBSTRING(@arr_title_name, @atna_pos, LEN(@arr_title_name) - @atna_pos + 1))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_number, @atnu_pos, LEN(@arr_title_number) - @atnu_pos + 1))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_type, @att_pos, LEN(@arr_title_type) - @att_pos + 1))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_pgae, @atp_pos, LEN(@arr_title_pgae) - @atp_pos + 1))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_level, @atl_pos, LEN(@arr_title_level) - @atl_pos + 1)))
           
            --PRINT 'Last Item OK'
        END
        ELSE
        BEGIN
            INSERT INTO @tmp_report_index
                        (   
                            title_name,
                            title_number,
                            title_type,
                            title_page,
                            title_level
                        )
            SELECT    RTRIM(LTRIM(SUBSTRING(@arr_title_name, @atna_pos, CHARINDEX(',', @arr_title_name, @atna_pos)- @atna_pos))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_number, @atnu_pos, CHARINDEX(',', @arr_title_number, @atnu_pos)- @atnu_pos))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_type, @att_pos, CHARINDEX(',', @arr_title_type, @att_pos)- @att_pos))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_pgae, @atp_pos, CHARINDEX(',', @arr_title_pgae, @atp_pos)- @atp_pos))),
                    RTRIM(LTRIM(SUBSTRING(@arr_title_level, @atl_pos, CHARINDEX(',', @arr_title_level, @atl_pos)- @atl_pos)))
            --SELECT * FROM @tmp_report_index
            --PRINT 'Item Insert OK'
        END
               
        SET @atna_pos    = CHARINDEX(',', @arr_title_name, @atna_pos) + 1
        SET @atnu_pos    = CHARINDEX(',', @arr_title_number, @atnu_pos) + 1
        SET @att_pos    = CHARINDEX(',', @arr_title_type, @att_pos) + 1
        SET @atp_pos    = CHARINDEX(',', @arr_title_pgae, @atp_pos) + 1
        SET @atl_pos    = CHARINDEX(',', @arr_title_level, @atl_pos) + 1
       
        SET @title_counts = @title_counts - 1
        --SELECT @title_counts
    END
   
    SELECT * FROM @tmp_report_index
, then I created sub-report ReportIndex.rpt:
Data Source: dbo.uspReportIndex
Subreport Links: @arr_title_name = formula @Report_Index_Title_Number, same concept for parameters
Select Expert: for chapter, title_type is equal to Chapter, same concept for other indexes sub-report

I started to format the display style, inside ReportIndex.rpt, drag a new created formula Index_Title in Report Header:
IF {uspReportIndex;1.title_type} = 'Chapter' THEN
    'TABLE OF CONTENTS'
ELSE IF {uspReportIndex;1.title_type} = 'Table' THEN
    'LIST OF TABLES'
ELSE IF {uspReportIndex;1.title_type} = 'Figure' THEN
    'LIST OF FIGURES'
ELSE ''
, drag a new created formula Title_Name in Details section:
StringVar title;
NumberVar i;

title := {uspReportIndex;1.title_number} + '     ' + {uspReportIndex;1.title_name};

IF {uspReportIndex;1.title_number} = '' OR ISNULL({uspReportIndex;1.title_number}) THEN
    {uspReportIndex;1.title_name}
ELSE
(
    IF {uspReportIndex;1.title_level} > 1 THEN
    (
        FOR i := 1 TO {uspReportIndex;1.title_level} DO
        (
            title := '   ' + title;
        );
    );
    title;
)
 , adjust width of Title_Name, and drag database field title_page next to the Title_Name. Format these two formula fields: Format Field → Font → Style → Formula button:
IF {uspReportIndex;1.title_level} = 1 AND {uspReportIndex;1.title_type} = 'Chapter' THEN
    crBold
ELSE crRegular
Here I encountered another problem, one of indexes reach to second page, then I have to identify how many pages in the sub-report, but unfortunately Crystal Report doesn't provide Page Header in the subreport, so I have to simulate Page Header, for doing this, I created formula Page_Header:
WhileReadingRecords;
"";
, added new group in the report, and selected formula Page_Header. Once Group Header and Group Footer show in the report, suppress them, and drag a new created formula Page_Number:
WhilePrintingRecords;
Shared NumberVar tc_pagenumber;

tc_pagenumber := tc_pagenumber + PageNumber - 1;
The last pity of this design is that I have to manually move those index page to reserved area after export to PDF file format.

In Crystal Reports - Create index page - Part 2, I will explain how to pass page number to multi-page sub-report, let sub-report continue page number and some execution performance problem I met and the way I fixed the problem.

No comments:

Post a Comment