CODEWITHSUNDEEP
crystal-reportscrosstab
Annette
Annette

Reputation: 29

Crystal Crosstab need help adding columns to highlight average and difference

I have a crosstab which shows a count of a callid for this month and previous month by customer id this is showing a top 10 group. My problem is I need to have a column that shows the difference between this month and previous month and then I need a column for the 12 month average

i.e. My crosstab currently looks like this

                             Sept        Aug
Customer1                     80         20
Customer2                     56         58
Customer3                     60         88
Customer4                     71         54
Customer5                     98         45

I need it to look like this

                            Sept        Aug       Difference     12mthAvg
Customer1                     80         20        60              65
Customer2                     56         58        -2              73
Customer3                     60         88       -28              52
Customer4                     71         54        17              48
Customer5                     98         45        53              56

Is this possible?

Thanks,

Annette

Upvotes: 1

Views: 6553

Answers (1)

craig
craig

Reputation: 26262

The monthly average is easy. Right click the first row in the 'Total' column (by default, this is the left-most column), select 'Edit Summary...', and choose 'Average' from the 'Calculate this summary' combo-box. This assumes that the cross-tab always includes 12 months of data, which can be easily done by setting an appropriate record-selection formula.

I'll do some research on the monthly deltas and post my results.

** edit **

If you have Crystal Reports 2008, you can add a calculated column. You will need to have 11 of these.

Here are the steps:

  1. Select the second column's header and choose Calculated Member | Insert Column.
  2. Right click the first cell in the new column and select Calculated Member | Edit Calculation Formula

  3. Enter the following in the formula's text:

    //find the difference in values of the current row in the two columns to the left of the //current (calculated) column GridValueAt(CurrentRowIndex,CurrentColumnIndex-1,CurrentSummaryIndex) + GridValueAt(CurrentrowIndex,CurrentColumnIndex-2,CurrentSummaryIndex)

  4. Change the column header's text by right clicking the header and choosing Calculated Member | Edit Header Formula'.

  5. Repeat steps 1-4 for each calculated column. In step 3, change the offset from -2 to -3 to account for the presence of the calculated column.

Upvotes: 1

Related Questions