Skip to Content

Cross Tab Calculations, min,max,average

Hi All,

I am trying to create a cross tab report showing sales via product via day of week. I have managed to do this but would like to add some further calculations to my cross tab report Average, Min and Max. It it possible to do this in a as calculation in the cross tab report. Many thanks for any advise and guidence given.

Stock FRI 01/01/2012 FRI 08/01/2012 FRI 16/01/2012 TOTAL Average Min Max 1 Pint Whole 100 80 60 210 80 60 100 1 Pint Semi 200 250 300 750 250 200 300 1 PInt Skimmed 60 50 40 150 50 40 60

Mike

Add comment
10|10000 characters needed characters exceeded

3 Answers

  • Best Answer
    Posted on Nov 14, 2012 at 03:14 PM

    Hi Mike,

    What version of Crystal Reports are you using?

    Go to help > about and paste the version number.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      Please follow these steps:

      1) While in the Preview Mode, right-click the last date and select Calculated Member > Insert Column. Do this until you see three blank columns with zero values.

      2) Right-click the first zero value and select Calculated Member > Edit Calculation Formula and use this code:

      local numbervar i;  local numbervar min := GridValueAt(CurrentRowIndex,0,0);  for i := 1 to CurrentColumnIndex-1 do  (      if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) < min then      (          min := GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex);      )  );  min; 

      3) Then, right-click the blank cell beside this value and select Calculated Member > Edit Header formula and write this:

      "Minimum"

      That's your Minimum Column


      4) To find the Maximum follow the same procedure, however use this code:

      local numbervar i;  local numbervar max := GridValueAt(CurrentRowIndex,0,0);  for i := 1 to CurrentColumnIndex-1 do  (      if GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex) > max then      (          max := GridValueAt(CurrentRowIndex,i,CurrentSummaryIndex);      )  );  max;  


      Name this column "Maximum"

      5) The last column will be the Average, so get to the Edit Calculation Formula and use this code:

      local numbervar i;  local numbervar avg;  local numbervar cnt; //I had to use this variable since currentrowindex was acting funny for some reason  for i := 1 to CurrentColumnIndex-1 do  (      avg := avg + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);      cnt := cnt + 1;  );  (avg+GridValueAt(CurrentRowIndex, 0, CurrentSummaryIndex))/(cnt+1);



      Let me know how this goes!

      - Abhilash

      Follow us on Twitter

      Got Enhancement ideas? Try the SAP Idea Place

      Share Your Knowledge in SCN Topic Spaces

  • Posted on Nov 15, 2012 at 01:25 PM

    Hi Abhilash,

    Many thanks for your help this will be fantastic if I can get this working. Unforntunatly I have stumbled at the first hurdle.

    1) While in the Preview Mode, right-click the last date and select Calculated Member > Insert Column. Do this until you see three blank columns with zero values.

    While trying to do this I get an error message saying:

    You have just added a calculated row/colum.

    The insertion formula and the header text can be modified through the context menu of this row/colum label:

    the calculation formula can be modified through the context menu of the summaries within this row/column.

    PLease help..

    Many thanks

    MIke

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 15, 2012 at 03:55 PM

    Hi Abhilash,

    You are an absolute star thank you so much this has worked perfectly. You have saved me a lot of work and headache.

    Add comment
    10|10000 characters needed characters exceeded