2012-10-30

Crystal Reports - Cross-Tab - Dynamic column number, position and sub-total page number

There is a cross-tab be placed in the Group Footer of sub-report, and base on project's group policy to design its visibility, it initially groups by three columns: period, location and model, and horizontally expands to several pages for each model.



There are two requirements for this report:
1. The maximum number of columns in cross-tab on each page is 5.
2. The title of each model:
Figure [Figure_Number].   [Cross-Tab Name] [location]_[model] - [period] (Part [X] of [Sub Total Pages of each Model])
, for instance, Figure 31.   Summary Table Australia_CAT785 - 2011 FY (Part 1 of 3)

Considering dynamic design for the first request, instead of changing every affected coding part, changing one parameter to affect all. I need to add new column in the original dataset: Row_Number, this is a sequence number for creating virtual group, according this case, the query as follows:
SELECT        ROW_NUMBER() OVER (PARTITION BY period, location, model ORDER BY machine_id) [Row_Number],
                    *
FROM        @tmp_cross_tab_raw_data_set
, then see the following steps in Crystal Reports:
1. Create a parameter: @Column_Number
2. Create a formula: @Page_Break, and input script:
CEILING({usp;1.Row_Number}/{?@Column_Number})
3. Go to Group Expert, click Add, in Group by area field, select @Page_Break
4. Re-locate cross-tab to new Group Footer section.

For the second request:
1. Create a formula: @Horizontal_Page_Number_Total, and type the script:
CEILING(COUNT({usp;1.plantno}, {usp;1.model})/{?@Column_Number})
2. Create a formula: @Title, and type:
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_prefix1;
NumberVar page_number_sub;

Shared StringVar financial_flag;

figure_number := figure_number + 1;
page_number_sub := page_number_prefix1 + PageNumber - 1;

title_number := title_number & 'Figure ' & TOTEXT(figure_number) & '.,';
title_name := title_name & 'Cross-Tab Name ' & {@@Machine_Code} & ' - ' & {usp;1.period} & financial_flag & ' (Part ' & TOTEXT({@@Page_Break}) & ' of ' & TOTEXT({@@Horizontal_Page_Number_Total}) & '),';
title_type := title_type & 'Figure,';
title_page := title_page & TOTEXT(page_number_sub) & ',';
title_level := title_level & '1,';

'Figure ' & TOTEXT(figure_number) & '.   ' & ''Cross-Tab Name ' & {@@Machine_Code} & ' - ' & {usp;1.period} & financial_flag & ' (Part ' & TOTEXT({@@Page_Break}) & ' of ' & TOTEXT({@@Horizontal_Page_Number_Total}) & ')';
The last problem is not every model's machine number could be divisible by 5, most of them are less than 5 columns in the last page, but I still want cross-tab be placed in the center of page. Right- click cross-tab, click Size and Position, click formula of x-axis, and type:
//1cm = 566.929133858 twips
NumberVar not_complete_rows;

not_complete_rows := {usp;1.Row_Number} MOD {?@Column_Number};

IF not_complete_rows > 0 THEN
    ({?@Column_Number} - not_complete_rows) * 700;
, after my countless tests and searches from Internet, I found that Crystal Reports uses twips(1cm = 566.929133858 twips) to control position in formula, but use centimeter in Objects Size and Position properties window.

No comments:

Post a Comment