1556176 - How to summarize the columns of the Calculated Members in a Cross-tab 'vertically'

SAP Knowledge Base Article - Public

1556176 - How to summarize the columns of the Calculated Members in a Cross-tab 'vertically'

Symptom

How to summarize the columns of the Calculated Members in a Cross-tab 'vertically'

Environment

Crystal Reports 2008 (All Patch Levels)

Reproducing the Issue

  1. Create a Crystal Report against the Xtreme Sample database
  2. Insert a Cross-tab on the Report Header section (Insert->Cross-tab)
  3. Right click the Cross-tab and go to ‘Cross-tab Expert’
  4. Select ‘Orders.Order Date’ in the ‘Columns’
  5. Select ‘Customer.Country’ in the ‘Rows’
  6. Select ‘Customer.Last Year’s Sales’ in the ‘Summarized Fields’
  7. Click on OK
  8. Now, suppose you want to add a column which calculates the Last Year’s Sales difference between two particular dates in percentage
  9. Suppose you want to find difference between column 1 and column 2, then right click column 2 then select ‘Calculated Member’->’Insert Column’
  10. Now, a column would be inserted which would contain the difference between column 1 and column 2 shown as percentage
  11. Right click the newly added column and select ‘Calculated Member’->’Edit Calculation Formula’
  12. Type in the following formula: 
  13. GridValueAt(CurrentRowIndex, CurrentColumnIndex-2, CurrentSummaryIndex) / GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex) * 100

  14. Save the formula and refresh the report
  15. Observe that the totals for the newly added column are calculated by dividing the Sub-total for Column 1 by the Sub-total for Column 2
  16. We would like to calculate the Sub-total for the Calculated Member ‘vertically’

Resolution

  1. Right click the Sub-total field for the Calculated-Member and click on the formula button of the ‘Display String’
  2. Type in the following formula:  
  3. Totext(CurrentColumnIndex)  

  4. Note down the ‘CurrentColumnIndex’ of the column
  5. For now, let’s assume this value is 3
  6. Go back to the ‘Display String’ formula and type in the following formula: 
  7. if CurrentColumnIndex = 3 then

    totext(gridvalueat(0,3,0)+gridvalueat(1,3,0)+gridvalueat(2,3,0)+gridvalueat(3,3,0))

    else totext(CurrentFieldValue)  

  8. Save the formula and refresh the report
  9. Now, the Sub-total for the Calculated Member is calculated ‘vertically’

Keywords

Cross-tab Calculated Member, summarize vertically , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1