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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

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 a 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 a 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.