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 3There are 5 elements to composite index:
1. Introduction 10
1.1 Background 10
Table 1. Table1_Name 11
Figure 1. Figure1_Name 13
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;2. Drag formula @Report_Index to the Report Header, and suppress the filed.
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;
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;(2) Title_Level2:
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';
WhilePrintingRecords;Formula (3)Title_Level3, (4)Title_Level4 and (5)Title_Level5 have the same concept for Title_Level2, just need to change target variable.
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';
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;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:
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';
WhilePrintingRecords;, 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:
Shared StringVar title_name;
title_name;
WhilePrintingRecords;Because page number displays in every Page Footer, I need to consider about two parts:
Shared StringVar report_end_flag;
report_end_flag := 'Y';
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;, 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:
Shared NumberVar tc_pagenumber;
IF tc_pagenumber = 0 THEN
tc_pagenumber := PageNumber;
tc_pagenumber;
WhilePrintingRecords;, 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.
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;
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], then I created sub-report ReportIndex.rpt:
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
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, drag a new created formula Title_Name in Details section:
'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 ''
StringVar 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:
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;
)
IF {uspReportIndex;1.title_level} = 1 AND {uspReportIndex;1.title_type} = 'Chapter' THENHere 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:
crBold
ELSE crRegular
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;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.
Shared NumberVar tc_pagenumber;
tc_pagenumber := tc_pagenumber + PageNumber - 1;
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