1202772 - How to create a manual running totals in a cross-tab

How do I create a manual running total in a cross-tab?

For Crystal Reports XI, Crystal Reports 10, and Crystal Reports 9 users, there is a business need to create manual running totals in a cross-tab. The methods used to create a manual running total in a regular report do not work in a cross-tab.

The following example assumes 12 columns, one for each month of the year, are used. If less than 12 columns are used, adjust the following formula accordingly.

Here are the steps:

1. Add a summary value to the cross-tab report in the Cross-tab Expert.
   It can be any value, but to make troubleshooting and documenting the report easier, it's best if it is the value used in the manual running total.
2. Right-click the value > click Format Field > click Formula (X+2) across from Suppress.
3. Enter the following formula:
   
   // Create one variable for each column in the cross-tab.
   // There are 12 array variables in this example, one for each calendar month.
   // Use "numbervar" instead of "currencyvar" if the field is a number field.
   currencyvar array a;
   currencyvar array b;
   currencyvar array c;
   currencyvar array d;
   currencyvar array e;
   currencyvar array f;
   currencyvar array g;
   currencyvar array h;
   currencyvar array i;
   currencyvar array j;
   currencyvar array k;
   currencyvar array l;
   
   numbervar x;
   
   // If columns are based on something other than calendar months, create a method to define the number of
   // columns and populate variable z with the appropriate column number.
   numbervar z:= month(GridRowColumnValue("date"));
   
   // Replace {Customer.Country} with the field that defines the appropriate rows in the cross-tab.
   if remainder(x,distinctcount({Customer.Country})) = 0 then
   x:= 1
   else
   x:=x + 1;
   
   // The number of if statements must match the number of columns in the report.
   if z=1 then (redim preserve a[x]; a[x]:= CurrentFieldValue)
   else
   if z=2 then (redim preserve b[x]; b[x]:= CurrentFieldValue)
   else
   if z=3 then (redim preserve c[x]; c[x]:= CurrentFieldValue)
   else
   if z=4 then (redim preserve d[x]; d[x]:= CurrentFieldValue)
   else
   if z=5 then (redim preserve e[x]; e[x]:= CurrentFieldValue)
   else
   if z=6 then (redim preserve f[x]; f[x]:= CurrentFieldValue)
   else
   if z=7 then (redim preserve g[x]; g[x]:= CurrentFieldValue)
   else
   if z=8 then (redim preserve h[x]; h[x]:= CurrentFieldValue)
   else
   if z=9 then (redim preserve i[x]; i[x]:= CurrentFieldValue)
   else
   if z=10 then (redim preserve j[x]; j[x]:= CurrentFieldValue)
   else
   if z=11 then (redim preserve k[x]; k[x]:= CurrentFieldValue)
   else
   if z=12 then (redim preserve l[x]; l[x]:= CurrentFieldValue);

    

   // This false entry merely assures that the value will not be suppressed.
   false
   
   

4. Save and close the formula.
5. Click Formula (X+2) across from Display String.
6. Enter the following formula:
   
   WhilePrintingRecords;
   // Create one variable for each column in the cross-tab.
   // In this example, there are 12 array variables, one for each calendar month.
   // Use numbervar instead of currencyvar if the field is a number field.
   currencyvar array a;
   currencyvar array b;
   currencyvar array c;
   currencyvar array d;
   currencyvar array e;
   currencyvar array f;
   currencyvar array g;
   currencyvar array h;
   currencyvar array i;
   currencyvar array j;
   currencyvar array k;
   currencyvar array l;
   
   // Variables x and z populate the Suppress formula above.
   numbervar x;
   numbervar z;
   
   // Create as many case statements as there are columns.
   select z
   case 1: totext(a[x])
   case 2: totext(a[x] + b[x])
   case 3: totext(a[x] + b[x] + c[x])
   case 4: totext(a[x] + b[x] + c[x] + d[x])
   case 5: totext(a[x] + b[x] + c[x] + d[x] + e[x])
   case 6: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x])
   case 7: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x])
   case 8: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x] + h[x])
   case 9: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x] + h[x] + i[x])
   case 10: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x] + h[x] + i[x] + j[x])
   case 11: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x] + h[x] + i[x] + j[x] + k[x])
   case 12: totext(a[x] + b[x] + c[x] + d[x] + e[x] + f[x] + g[x] + h[x] + i[x] + j[x] + k[x] + l[x])
   
   

====================
NOTE:
If no data exists for a given column and row (for example, one country has no sales for a given month, and so there is no matching record in the recordset), one of the arrays (a[x] through l[x]) will have no values. Additional code will need to be added to handle this possibility.
====================

cross-tab crosstab manual running total , 6362411 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 10.0 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2