Develop BIRT reports efficiently with esProc: August 2019

【Source】

https://forums.opentext.com/forums/discussion/61542/compare-current-row-with-previous-row-value#latest

【Problem】

I am new to developing reports in BIRT. can someone help me in determining how to obtain previous row value. I want to compare current row value with previous row value. There are three columns in my report. I have grouped the data on first column and the detail rows has first, second and third column. It is the second column on which I want to compare current row with previous row value. Lets call the second column as Shifttime and it's a date time data type attribute

…

Thanks I did that, but it returned some random values (It did not return previous row values but random values from the dataset). I have used a group by on one of the columns and have sorting applied on that column in ascending manner. Did that cause this to return random results. The expression I used in the computed column is below: (Day is my base column for which I want previous values to return, it's a integer data type). Appreciate your response!
Prevval (computed column) has following expression:

    var currentRow = row["Day"]; //Get current data row</span>
    var previousRow = reportContext.getGlobalVariable("previousRow"); //Get global variable value
    if(currentRow==null){reportContext.setGlobalVariable("previousRow",null);} //Check for null and set null
    else{reportContext.setGlobalVariable("previousRow",currentRow);} //Or assign global variable to current row value<br>previousRow;

【Answer】

In Birt, to realize cross-row computation within a group, it is usually implemented by SQL window function or report script, but the code is complex. In this case, SPL can be used to assist the reporting tool. Here's an example.
In this example, there are three fields, of which SELLERID is a grouping field, DAY is a field that needs to be compared, and the field type is date-time type. We need to design a grouping table. SELLERID is a grouping field. Detailed fields are DAY, AMOUNT and calculation column SHIFTTIME. The difference between the current DAY and the Day in the previous record within the group is stored in SHIFTTIME. The source data are as follows:

Use esProc to arrange the data：

	A	B
1	=connect("db").query("select SELLERID, DAY,AMOUNT,0 as SHIFTTIME from SALES order by SELLERID, DAY")	//Query the database, create an additional column of constants for backup.
2	=A1.group(SELLERID).run(~.run(interval(DAY[-1],DAY):SHIFTTIME))	//Grouping by SELLERID and modifying the computed column SHIFTTIME in each group of data
3	=A2.union()	//Merge each group of data
4	return A1	//Return the calculation results to the reporting tool

Save the above SPL code as sales.dfx, and then introduce it into the Birt report. For Birt calling SPL please refer to 《How to Call an SPL Script in BIRT》

Step 1: Add esProc JDBC data source.