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

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

Crystal Reports 2008 (All Patch Levels)

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:　

GridValueAt(CurrentRowIndex, CurrentColumnIndex-2, CurrentSummaryIndex) / GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex) * 100

13. Save the formula and refresh the report
14. 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
We would like to calculate the Sub-total for the Calculated Member ‘vertically’

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: 　

Totext(CurrentColumnIndex) 　

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

if CurrentColumnIndex = 3 then

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

else totext(CurrentFieldValue) 　

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

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