The goal is to get reverse running difference in Crosstab, so that as you go down the rows, you'll end up at zero. Just like a running total, but in reverse.
The logical solution would be: GridValueAt (CurrentRowIndex-1, CurrentColumnIndex, 0) - GridValueAt (CurrentRowIndex, CurrentColumnIndex, 1)
Where every next row subtracts the value from the previous summary.
But it throws an error "Unable to compute the return type because the Embedded Summary is referring to itself."
Reproducing the Issue
- Create a Crosstab within report connected to XTREME database against Customer and Order table
- Add Country as Column Header, Order Date as Row Header (monthly) and Sum of Order Amount as a summary
- Add Embedded Summary with the following calculation: GridValueAt (CurrentRowIndex-1, CurrentColumnIndex, 0) - GridValueAt (CurrentRowIndex, CurrentColumnIndex, 1)
- Refresh report and get the error message
GridValueAt function works better if it refers to actual field values.
The following embedded formula resolves the problem:
local numbervar i;
local numbervar temp; //variable to store sum of all previous row values within the same column
for i:=1 to CurrentRowIndex do //the loop to run as many time as many previous rows in crosstab
temp:=temp + GridValueAt (i, CurrentColumnIndex, 0); //calculate summary
GridValueAt (0, CurrentColumnIndex, 0) - temp // Column Total - temp summary
reverse summary, crosstab , KBA , BI-RA , Reporting, analysis, and dashboards , How To