cancel
Showing results for 
Search instead for 
Did you mean: 

Cross Tab Calculations, min,max,average

former_member1173617
Participant
0 Kudos

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.

StockFRI 01/01/2012FRI 08/01/2012FRI 16/01/2012TOTALAverageMinMax
1 Pint Whole10080602108060100
1 Pint Semi200250300750250200300
1 PInt Skimmed605040150504060

Mike

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Mike,

What version of Crystal Reports are you using?

Go to help > about and paste the version number.

-Abhilash

former_member1173617
Participant
0 Kudos

Hi Abhilash,

I am running 2008 version 12.2.0.290.

MIke

abhilash_kumar
Active Contributor
0 Kudos

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

Answers (2)

Answers (2)

former_member1173617
Participant
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

Thanks Mike, please mark the thread as Answered!

-Abhilash

former_member1173617
Participant
0 Kudos

NO Problem I have been trying to find out where that is but need help with that also sorry.

former_member1173617
Participant
0 Kudos

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

abhilash_kumar
Active Contributor
0 Kudos

Hi Mike,

That's not an error message. It's just a informative text saying you just added a new column.

Don't worry about the message.

-Abhilash