Vast majority of all expert cross-tab solutions available in Business Objects knowledge base and the ones we could find on the Internet relies on Display String property, especially when cross-tabs have more than one column and one row.
Many expert cross-tabs displaying non-additive functions are based upon some of the following steps:
- Display String function implementing complicated computations and returning a string to be displayed;
- Global Arrays accumulate intermediate results used in Display String functions;
- Global Variables serve as sequential rows and columns indexes;
- Function GridRowColumnValue (Field) returns Field value within the cross-tab and helps locate celll position in the cross-tab hierarchy;
- CurrentFieldValue returns the value of the current cross-tab cell and could be used within Display String function for that cell.
Unfortunately, this approach has severe limitations as of Crystal Reports XI. Here they are listed first, and later described in details:
- Often, arrays are created to store subtotals per report rows. Arrays in Crystal are limited to 1000 elements. Assuming 20 records per page, subtotals for only 50 pages can be stored.
- Global variables do not provide sequential indexes for records in the report in the order of records listed in the report! This happens because cross-tabs are built by columns top-left to bottom right and because pages in report are populated in the order user accesses them, not in the sequence they were created.
- Function GridRowColumnValue changes the order of passing though Display Property fields defined on subtotal level between pages and resets global variables to 0 on some pages!
- Global arrays populated within Display String function may conduct themselves unpredictably on multi-page reports.
To see issue #3, type the following formula into Display String property for the subtotal metrics field on the level Group1 and Group2:
Global NumberVar gnReportIndex2;
gnReportIndex2 := gnReportIndex2 + 1;
ToText(gnReportIndex2, 0)
Run the report and watch index sequentially increasing. Now add a simple call to GridRowColumnValue, and see that the index in reset to 1 on any new page:
Global NumberVar gnReportIndex2;
gnReportIndex2 := gnReportIndex2 + 1;
If GridRowColumnValue ("ado.Group1Sort") = "1" Then
ToText(gnReportIndex2, 0)
Else
ToText(gnReportIndex2, 0)
Some other functions, like Page Number, have the same effect.
To see issue #4 in action, download sample cross-tab report from Business Object site
http://www.insight.businessobjects.com/system/files/Quarterly+Growth+Crosstab+V9.rpt . I got this reference from http://www.insight.businessobjects.com/node/901. Open this report in preview, and resize the height of the cell Sum of @Orders Amount to about seven-eight times of their original height, so that the report would take more than three pages. Then scroll the pages in different order and see the report crashing: either because variable gtctr gets reset to 0, or global array gtctr is not populated in time when it is used in Display String function for Total Avg for Rows field.
To conclude: Crystal Reports does not provide generic and reliable approach to build cross-tab reports on non-aggregate functions. The only way to resolve the task is programming - please see now the solution from Relasoft. |
