 |
 |
 |
 |
 |
Advanced programming cross-tab reports using Crystal Reports XI. |
Prepared by Dr. Alexander Werner. KB# 10001. |
Relasoft Crystal Report experts suggested and implemented the following approach, surpassing all these limitations. It includes the following steps: 1. Calculate all subtotals for non-additive functions either in the database, or in VB program. 2. Pass these subtotals to the report using VB.Net by creating Global Arrays in Crystal Reports Formulas. 3. Within Display String function, find cell location in the cross-tab hierarchy, find the correct index for the value within the array and display the corresponding value. Let’s look into details. |
|
To show penetration report, I will start with building a standard cross-tab with fields representing sales person (field ado.Group1Sort) and product classification levels (ado.Group2Sort and ado.Group3Sort fields) given as rows, and reporting periods (months or years) displayed as columns. Market penetration is calculated as a ratio between distinct count of stores assigned to the rep and having sales of more than certain amount divided by the total number of stores in the area assigned to the rep.
On a database level, penetration per SalesRep, ProductDivision, MainItem and Month is calculated and stored in the field CntHdrRatioPerc. We choose fields mentioned above as cross-tab rows and columns, and picking up two summarized field: Max(CntHdrRatioPerc) and Distinct Count of stores. Now lowest level cells show correct values, but all the subtotals and totals are wrong. They need to be calculated outisde and transfered to the report by VB prorgram. |
|
On a report header level, I prepared two functions: Global gcListGroup1_1() As String 2. Function @InitRowTotal1 shows list of penetration ratios according to the reps sequence in formula @InitListGroup1. Global RowTotal1_1() As Number I used Basic Syntax, and not Crystal Syntax, because Basic Syntax has a line continuation character “_”, and I could not find something similar in Crystal Syntax. Arrays initialization statements for long arrays require more than one line. When report is called from the VB.Net program there formulas get over-written with real values. Due to the fact, that arrays in Crystal Reports are limited to only 1000 elements, we may need to pass as many arrays as required, each of them, except for the last one, of 1000 elements. Code for populating columns is similar to rows and is omitted here. |
Below I am accessing non-existing arrays Global RowTotal1_2- RowTotal1_5, which can be defined in the Net program and passed into if the number of sales reps exceed 1000. Here I assume that the number of elements does not exceed 5,000, or even 2000 – good enough for my task. Still more than a 1000. Global RowTotal1_1() As Number Dim CurrentRowTotal1 As Number Dim llFound As Boolean Local lcKey As string ‘ lcKey return the current rep name for the row subtotal cell ‘ Now I need to find index of the rep in the array of reps: If llFound Then Now we are safely finding rep in the list, detecting his/her penetration ratio as passed from the calling program and then display it. Same logic applies to all other horizontal and vertical subtotals. The only difference is that row subtotals on the 3-rd level will rely on arrays, like: Global gcListGroup123_1() As String , where array elements are strings build by concatenation of fields ado.Group1Sort, ado.Group2Sort and ado.Group3Sort with interposing delimiter “~#~”. These strings of course can be quite long, but line continuation symbol still allows assigning exactly 1000 elements to each array, and unlimited number of such arrays can be received in the formula. Crystal Reports does not impose a limit on the size of the formula. |
Calculating subtotals and prepare lists of subtotals and list of rows and columns values for transmission into the cross-tab Crystal Report is not a difficult task and is database specific. For penetration, we wrote stored procedure PreparePenetrationArrayCount with an input parameter of what to find and return: GROUP1COLLIST – for the list of values for field shown as cross-tab first row, GROUP12COLLIST - for the list of combined values for field shown as cross-tab first and second row, GROUP1COLLIST - for the list of combined values for fields shown as cross-tab first row and the column, The stored procedure creates a temporary table Create Table #tmpArrayLine (ArrayLine varchar(8000)) And, running a cursor, populates it with rows storing lists of returned values, for either concatenated fields or measure values. The stored procedure returns results of simple select to the calling VB program: select ArrayLine Notice, that the SP does not impose any limits on list sizes. The procedure PreparePenetrationArrayCount is database specific and its detailed description is outside of the scope of the current article. The full content of #tmpArrayLine is then read into a VB.Net string variable lcTotalLine. This variable stores all list values. As it can contain more than 1000 elements, we cannot pass it to the report directly. Prior to that, we need to split the line into array of strings containing exactly 1000 elements, except for the last one. This is done by the following procedure: ' This procedure splits line of percents or other fields to pass to CR arrays. They have a max size of 1000 for now. Dim lnPos, I, lnLineCount As Integer For I = 0 To UBound(laArrayLine) End Sub Function ChopLineBy2000ForCR inserts continuation character after each 900 characters to comply with Crystal Reports limit of 2024 characters per line and also improves line visibility when printed in VB in debug mode. |
This part is now easy: array laInitTotal stores text of all formulas written in Basic Syntax to pass to the report: For I = 0 To laArrayLine.GetUpperBound(0) And later do the final pass the formula text into the report: |
