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

[malinisomu]

joseph_muiruri Here are my finding after your suggestion.

Step 1: Double click Data Flow and define $PREV_LOB and $PREV_LRP as input parameters

Step 2: Go to Job and initiate values to $PREV_LOB and $PREV_LRP

Step 3: Write a custom function as mentioned below, Be careful while defining $PREV_LOB and $PREV_LRP parameters in side Custom function. Its type should be INPUT/OUTPUT

$CURRENT_LOB ==>Input

$PREV_LOB ==>Input/Output

$PREV_LRP ==>Input/Output

$CURRENT_LRP ==>Input

if ($CURRENT_LOB = $PREV_LOB)

$PREV_LRP= $PREV_LRP + nvl( $CURRENT_LRP,0.0);

else

$PREV_LRP = nvl( $CURRENT_LRP,0.0);

$PREV_LOB = $CURRENT_LOB;

Return 0;

Step 4: call the custom function by assigning the parameters as mentioned in the image

Step 5: Execute the job , you will get running sum .

Thanks,

Malini

[jmuiruri]

Greetings malinisomu,

I like your solution, its simple, straight forward and scalable. For performance reasons you should also enable parallel execution of the function.

Also add below to your comment above as it will help others understand the type of function parameters.

# $CURRENT_LOB ==> Input
# $PREV_LOB ==> Input/Output 
# $PREV_LRP ==> Input/Output 
# $CURRENT_LRP ==> Input

Best Regards,

Joseph