Shared array variable vs recurring subreport

Wednesday 16 March 2016 @ 11:33 pm

Sometimes a subreport has to be run for each group. For instance you might need to run a subreport once for each employee in a report. If you have a large number of employees the subreport is slow, it can make the report crawl. Customers often ask if we can run the subreport once for ALL employees and then pass all of those value to the main report.

In theory you can. You could write a subreport to read all employees at once, and then use an array in the subreport to store a value for each employee. You could then pass the entire array back to the main report by using a shared array variable. The challenge comes when you try to use those values in the main report. There is no simple way for the main report to match up the elements in the array to individual employee records in the main report.

The complicated solution is to create two arrays with the same number of elements. One stores the employee IDs, while the other stores the value for each ID. Each employee ID in the first array should line up with the corresponding value in the second array. To retrieve an employee’s value in the main report you have to write a ‘looping’ formula. This formula will loop through the first array and count how many elements it has to check to find the matching employee ID. By using that count Crystal knows where to find the corresponding value in the second array. This is an example what the formula looks like, courtesy of Gordon Portanier of Crystalize in Toronto:

//PURPOSE OF FORMULA:           Parse through the shared arrays to pick out the information
    shared stringVar array CustomerId;
    stringVar array OrderId;
    stringVar array OrderAmount;
    stringVar array OrderDate;
    numberVar PositionOfOrderId; 
    numberVar PositionOfOrderAmount; 
    numberVar PositionOfOrderDate;
    numberVar i;
    redim OrderId[1]; 
    redim OrderAmount[1];
    redim OrderDate [1];
    //Loop through first array            
    for i := 1 to Count(CustomerId) do
        if instr(CustomerId[i],"|CustomerId:" & ToText({Customer.Customer ID},0,"")&"|")>0 then
            PositionOfOrderId := Instr(CustomerId[i], "OrderId:"); 
            redim preserve OrderId [if Len(OrderId[1])>0 then Count(OrderId)+1 else 1];
            OrderId [Count(OrderId)] := Mid (CustomerId[i], PositionOfOrderId +8 , Instr(Mid(CustomerId[i], PositionOfOrderId + 8) ,"|") - 1);
            numbervar PositionOfOrderAmount := Instr(CustomerId[i], "OrderAmount:"); 
            redim preserve OrderAmount [if Len(OrderAmount[1])>0 then Count(OrderAmount)+1 else 1];
            OrderAmount [Count(OrderAmount)] := Mid (CustomerId[i], PositionOfOrderAmount + 12 , Instr(Mid(CustomerId[i], PositionOfOrderAmount + 12) ,"|") - 1);
            //Order Date
            PositionOfOrderDate := Instr(CustomerId[i], "OrderDate:"); 
            redim preserve OrderDate [if Len(OrderDate[1])>0 then Count(OrderDate)+1 else 1];
            OrderDate [Count(OrderDate)] := Mid (CustomerId[i], PositionOfOrderDate + 10 , Instr(Mid(CustomerId[i], PositionOfOrderDate + 10) ,"|") - 1)

Not only is it complex to write, but it takes the report a long time to process, especially if you have hundreds of employees.
But, sometimes you get lucky and you can use a simpler method. I recently had a customer with a recurring subreport and that had to run over a thousand times, once per employee. The report took hours to run. As we talked about our options I realized that the employee ID was a number. This meant that I didn’t need to create two arrays in the subreport and I didn’t have to write a looping formula in the main report. Here’s why:

Say the first employee in the subreport has an ID of 78. I could create an array of 1000 elements and put his value in element 78, based on his ID. If the next ID is 95 I can put that value into element 95, and so on. I use the ID as the position in the array. In the main report when I get to employee 78 I don’t have to loop to find the right element, I just retrieve element 78.

The one challenge that we had to work around was that the numeric ID went up to 9000. In Crystal an array can only hold 1000 values. So I created 9 different arrays, each with 1000 elements. When I went to put a value into the array I took off the first digit. I used that digits to determine which array to use. I used the other 3 digits to specify the correct element to store or retrieve the value.

So if you have numbers under 10,000 as your ID (or if you can create an ID like this) you can use the simpler method for passing a list of values from the subreport to the main report.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro