Calculate Running Sum in BODS/SAP Data services

[malinisomu]

Hi,

I want to calculate the running sum of certain column values in BODS/SAP Data Services.

As mentioned in the below example LRP value needs to be sum up for each row(Running_Sum_LRP) and the group by LOB.Can anyone help me with custom function as far I know there is no built-in-function available.

Thanks,

Malini

[jmuiruri · Accepted solution]

Greetings malinisomu,

If you have fewer distinct LOBs you can do the following

In the above example, you only have two LOBs

• Create two environment variables to store the cumulative sum of the LRP column for the two LOBs

$LRP_ABC
$LRP_IRST

• create a simple Job of the format

• In the script SC_GV_Innitialization, initialize the two global variables to Zero

# cumulative sum of the ABC LOB
$LRP_ABC = 0;

# cumulative sum of the ABC IRST
$LRP_IRST = 0;

• Inside the dataflow RUNNING_SUM map the columns as shown below

• Create a new column Running_sum_LRP and map the column to func_running_sum(RUNNIG_SUM_SOURCE.LOB,RUNNIG_SUM_SOURCE.LRP)
• This function will take in two parameters, the LOB name and the LRM value for the current row.

begin
	# check if LOB name = 'ABC'
	if ($LOB = 'ABC')
	# If Yes, set add the current value of the ABC 's LOB value and the previous value
	# previous in this case being $LRP_ABC
    	begin
		$LRP_ABC = $LOB_CURRENT_VAL + $LRP_ABC ;
	    	print($LOB || ', ' || $LOB_CURRENT_VAL || ', ' || $LRP_ABC);
		# set the current value equals to the sum of current and previous
		$LOB_CURRENT_VAL = $LRP_ABC;
		
        end
	# check if LOB name = 'IRST'
	if ($LOB = 'IRST')
	# If Yes, set add the current value of the IRST 's LOB value and the previous value
	# previous in this case being $LRP_IRST
    	begin
		$LRP_IRST = $LOB_CURRENT_VAL + $LRP_IRST ;	
	    	print($LOB || ', ' || $LOB_CURRENT_VAL || ', ' || $LRP_IRST);
		# set the current value equals to the sum of current and previous
		$LOB_CURRENT_VAL = $LRP_IRST;
        end
# Return the current 
Return $LOB_CURRENT_VAL;
end

• When you now run the Job you should see output that resembles the following

Best Regards,

Joseph