KB10001C

3. Solution from Relasoft

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.

3.1 Build a basic cross-tab
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.

3.2 Prepare array placeholder functions
On a report header level, I prepared two functions:
1. Function @InitListGroup1 shows list of sales reps in the order they will appear in the cross-tab.

Global gcListGroup1_1() As String
gcListGroup1_1 = Array(“Dalton Boy”,”Kathlyne Coy”,”Mike Doy”)
formula = 1

2. Function @InitRowTotal1 shows list of penetration ratios according to the reps sequence in formula @InitListGroup1.

Global RowTotal1_1() As Number
RowTotal1_1 = Array(6.9,75.1,29.7)
formula = 1

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.

3.3 Build Display String function
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
Global RowTotal1_2() As Number
Global RowTotal1_3() As Number
Global RowTotal1_4() As Number
Global RowTotal1_5() As Number

Dim CurrentRowTotal1 As Number
CurrentRowTotal1 = 1

Dim llFound As Boolean
‘ This code makes a list of reps in formula @InitListGroup1 available to this formula:
Global gcListGroup1_1() As String
Global gcListGroup1_2() As String ‘ again, any number of arrays can be listed here.

Local lcKey As string ‘ lcKey return the current rep name for the row subtotal cell
lcKey = GridRowColumnValue (“ado.Group1Sort”)

‘ Now I need to find index of the rep in the array of reps:
Dim I As Number
Dim lnPos As Number
llFound = False
lnPos = 0
For I = 1 To UBound(gcListGroup1_1) Step 1
If gcListGroup1_1(I) = lcKey Then
lnPos = I
llFound = True
exit for
End If
Next

If NOT llFound and UBound(gcListGroup1_1) = 1000 Then
For I = 1 To UBound(gcListGroup1_2) Step 1
If gcListGroup1_2(I) = lcKey Then
lnPos = I + 1000
llFound = True
exit for
End If
Next
End If

If llFound Then
‘ If rep index found, I need to find corresponding measure value received in one of the arrays passed to
‘ formula @InitRowTotal1, depending on the rep index:
CurrentRowTotal1 = lnPos
Dim lcFormula As String
If CurrentRowTotal1 < 1001 Then
If CurrentRowTotal1 <= UBound (RowTotal1_1) Then
‘ Element with the given index found, pick the subtotal measure value and return it as a
‘Display String Value.
lcFormula = ToText(RowTotal1_1(CurrentRowTotal1))
Else
lcFormula = “* ” + ToText(CurrentRowTotal1)
End If
ElseIf CurrentRowTotal1 < 2001 Then
If CurrentRowTotal1 – 1000 <= UBound (RowTotal1_2) Then
lcFormula = ToText(RowTotal1_2(CurrentRowTotal1 – 1000))
Else
lcFormula = “* ” + ToText(CurrentRowTotal1)
End If
ElseIf CurrentRowTotal1 < 3001 Then
If CurrentRowTotal1 – 2000 <= UBound (RowTotal1_3) Then
lcFormula = ToText(RowTotal1_3(CurrentRowTotal1 – 2000))
Else
lcFormula = “* ” + ToText(CurrentRowTotal1)
End If
ElseIf CurrentRowTotal1 < 4001 Then
If CurrentRowTotal1 – 3000 <= UBound (RowTotal1_4) Then
lcFormula = ToText(RowTotal1_4(CurrentRowTotal1 – 3000))
Else
lcFormula = “* ” + ToText(CurrentRowTotal1)
End If
ElseIf CurrentRowTotal1 < 5001 Then
If CurrentRowTotal1 – 4000 <= UBound (RowTotal1_5) Then lcFormula = ToText(RowTotal1_5(CurrentRowTotal1 – 4000)) Else lcFormula = “* ” + ToText(CurrentRowTotal1) End If Else lcFormula = “* ” + ToText(CurrentRowTotal1) End If Else lcFormula = “0.0” End If formula = lcFormula 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 gcListGroup123_1 = Array(“Dalton Boy~#~Feline Food~#~Maine Coon”,”Dalton Boy~#~Feline Food~#~Persian”, etc) formula = 1 , 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. 3.4 Formula preparation in SQL and VB.Net 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, GROUP1TOTAL, GROUP12TOTAL, GROUP1COLTOTAL – the corresponding list of calculated measures for the given aggregate level. Cross-tabs with four rows and one column requires 12 option values, cross-tab with three rows and one column requires 8 options, etc. 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 from #tmpArrayLine 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. ‘ So, lnSize = 1000. lcSourceLine – total line, laArrayLine – lines resulted after chopping the source line ‘ into peaces of lnArraySize elements. Assumed: separation by ‘,’. Public Sub SplitLine(ByVal lcSourceLine As String, ByVal lnSize As Integer, ByRef laArrayLine() As String) Dim lnPos, I, lnLineCount As Integer ‘lnPos = InStr(lcSourceLine, “,”) ReDim laArrayLine(0) lnLineCount = 0 Do lnPos = InStrN(lcSourceLine, “,”, lnSize) If lnPos > 0 Then
laArrayLine(lnLineCount) = Mid(lcSourceLine, 1, lnPos – 1)
lnLineCount = lnLineCount + 1
ReDim Preserve laArrayLine(lnLineCount)
If lcSourceLine.Length > lnPos Then
lcSourceLine = Mid(lcSourceLine, lnPos + 1)
Else
lcSourceLine = Mid(lcSourceLine, lnPos + 1)
End If
Else
laArrayLine(lnLineCount) = lcSourceLine
End If
Loop While lnPos > 0

For I = 0 To UBound(laArrayLine)
laArrayLine(I) = ChopLineBy2000ForCR(laArrayLine(I))
Next I

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.

3.5 Passing formulas to cross-tab Crystal Report
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)
laInitTotal(J) = laInitTotal(J) + _
“Global RowTotal” + laLevelPrefix(J) + “_” + CStr(I + 1) + “() As Number” + vbCrLf + _
“RowTotal” + laLevelPrefix(J) + “_” + CStr(I + 1) + ” = Array(” + laArrayLine(I) + “)” + vbCrLf
Next I

And later do the final pass the formula text into the report:
moReport.DataDefinition.FormulaFields(“InitListGroup1”).Text = laInitTotal(cGROUP1LIST)