Crosstab not calculating correctly from array

[Former Member]

J. Wiseman created an array to help me summarize the incentives assigned to a drive based on a projected/performed field in the database. And then showed me how to add a second column in which the incentives would be totaled based off another column that contained the incentive or unit cost.

And it is working beautifully on the first cross-tab that looks at the pre-drive numbers.  But I'm trying to add a second cross-tab that performs the same calculations based off procedures performed, instead of projected procedures but it seems like the second cross-tab is still using the the array or variables that were assigned to the pre-drive cross-tab and I'm not sure how to change it.

The array used to build the values is:

whileprintingrecords;

stringvar d:= {OBI_EquipmentMaster.Description};

numbervar p:= {DriveProjectionAndCollectedTotals.ProcedureProjection};

numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};

stringvar array ad;

numbervar array ap;

numbervar array ape;

numbervar c2:= 1;

numbervar n:=0;

// check to see if the description has been added to the string array

// if not, add it plus add the initial projection value to the number arrays

if not (d in ad) then

    (

        numbervar c:= c + 1;

        redim preserve ad[c]; ad[c]:= d;

        redim preserve ap[c]; ap[c]:= p;

        redim preserve ape[c]; ape[c]:= pe;

    )

else

// if the description is already in the array, find its position

// then add the new projection as a running total to the appropriate number array values

(

    while c2 <= count(ad) do

        (

            if d = ad[c2] then (n := c2; exit while);

            c2 := c2 + 1

        );

    ap[n]:= ap[n] + p;

    ape[n]:= ape[n] + pe;

);

// grand running totals

numbervar gp:= gp + p;

numbervar gpe:= gpe + pe;

And then on the display string for the cell where I summed the values of the incentives:

whileprintingrecords;

stringvar t:= GridRowColumnValue ("rpt_EquipmentMaster.Description");

stringvar array ad;

numbervar array ap;

numbervar c3:= 1;

numbervar n2;

while c3 <= count(ad) do

    (

        if t = ad[c3] then (n2 := c3; exit while);

        c3 := c3 + 1

    );

numbervar apn:= ap[n2];

totext(apn,0)

And the display string where I calculated the total cost of the incentive:

whileprintingrecords;

numbervar apn;

numbervar apnrt:= apnrt + apn*currentfieldvalue;

"$"+totext(currentfieldvalue * apn,2)

My guess is that because I used the same formula for the second cross-tab as the two directly above for the display string, it is causing me to display the same numbers twice. But how this array was created and the formulas used to display the records is above my understanding.

Any suggestions on how to correct this second cross-tab?

[JWiseman · Accepted solution]

hi Trey,

the array builder formula is already set up to include the procedures performed values. see these lines...

     numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};

&

   ape[n]:= ape[n] + pe;

this means that in your second cross-tab you need to ensure that you reference the variables created in that formula. ape[n] is the array of those procedures performed. you do need to ensure that there are unique variables for any counters in each of the formulas though. also unique variables are required for running totals within those display strings and any other variables.

steps:

on your second cross-tab for the first summary, change the display string formula to this:

whileprintingrecords;

stringvar t:= GridRowColumnValue ("rpt_EquipmentMaster.Description");

stringvar array ad;

numbervar array ape;

numbervar c4:= 1;

numbervar n3;

while c4 <= count(ad) do

    (

        if t = ad[c4] then (n3 := c4; exit while);

        c4 := c4 + 1

    );

numbervar apne:= ape[n3];

totext(apne,0)

on that second cross-tab for the second summary, change the display string formula to this:

whileprintingrecords;

numbervar apne;

numbervar apnrte:= apnrte + apne*currentfieldvalue;

totext(currentfieldvalue * apne,0)

finally on the second cross-tab for the second grand total, change the display string to this:

whileprintingrecords;

numbervar apnrte;

totext(apnrte, 0)

[Former Member · Accepted solution]

Thanks again Jamie,

I don't understand how it works (yet) but it does and I thank you for your help.