cancel
Showing results for 
Search instead for 
Did you mean: 

Running Sum in CDS Views in SAP HANA

abhimanyu_sharma
Contributor
0 Kudos

Hi Experts,

I am trying to calculate the running sum in CDS view. below cds view is join of VBAK and VBRP table.

Data should like this in output in running sum field.

am trying to achieve it with windows function in SQL but the below statement Sum() is not working in CDS view.

I am getting an error while activating CDS view. Error is : System is expecting ',' after sum.

Could you please do let me know how can we do this in CDS view.

define view ZC_VBAK_VBRP_RUN as select 
from ZI_VBAK_ZVBRP 
 {
  
  SalesDocument, 
  SalesCreationDate, 
  BillingDocument, 
  Billing_Item, 
  BillingAmount, 
  BillingDate,
  currency,
sum(BillingAmount) OVER(partition by ("salesdocument") orderby ("Salesdocument")) as runningsum
   
}

pfefferf
Active Contributor
0 Kudos

Are you taking about a HANA CDS view or an ABAP CDS view?

abhimanyu_sharma
Contributor
0 Kudos

its ABAP CDS

Accepted Solutions (0)

Answers (4)

Answers (4)

shanthi_bhaskar
Active Contributor
0 Kudos

You can do as mentioned by Thorsten or you can also create a table function where you have flexibility in handling aggregation behaviors in SQL(AMDP) way.

ABAP CDS Table Function

0 Kudos

Hi Abhimanyu,

CDS view does not support running sum.So "sum(BillingAmount) OVER(partition by("salesdocument") orderby ("Salesdocument")) as runningsum" is not possible in CDS View. But there is one way out.

1) Create an AMDP view and write down the select statement that you mentioned with a group by clause containing all the dimension .

2) Create a table function and call the AMDP view from there.

3) Create a CDS view on top of that Table Function.

It will work for your scenario.

Regards,

Shawon

D039507
Advisor
Advisor
0 Kudos

Hi,

I think therefore you have to use the group by clause

{
intreno as ContractIntreno,
@Semantics.currencyCode: true
ContractCurrency as SourceCurrency,
@Semantics.currencyCode: true
cast('EUR' as abap.cuky( 5 )) as TargetCurrency,
@Semantics.amount.currencyCode: 'SourceCurrency' 
sum(UnitpriceContractCurr) as Deposit
  
}
group by
    intreno,
    ContractCurrency

Best regards

0 Kudos

CDS view does not support window functions.

You can do it in sql scrip like

Select
SalesDocument,
SalesCreationDate,
BillingDocument,
Billing_Item,
BillingAmount,
BillingDate,
currency,
sum(BillingAmount) OVER(orderby ("Salesdocument")) as runningsum
group by
SalesDocument,
SalesCreationDate,
BillingDocument,
Billing_Item,
BillingAmount,
BillingDate,
currency