RaqForum 22 No.
1 Reply  •  1,507 View  • 
     ...

Implement intra-group cross-row calculation In Birt

 

【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:

   undefined

  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.

  undefined

  Step 2: Create data set. Edit query text.

  undefined

  Step 3: Create a report. The report is designed as follows:

  undefined

  Step 4: WEB preview. The preview result is as follows:

  undefined


 
 •  1,507 View  •