AT END of in Custom CDS S4HANA CLOUD

[shashankagg]

Can we use AT END OF functionality in custom CDS view where i need to sum up results of columns based on keys defined in CDS.

[Harish_Mangtani]

Hello, 

Yes, we can use the AT END OF functionality in a custom CDS view to sum up the results of columns based on keys defined in the CDS.

Take an example of a custom CDS view called ZCDS_SalesData, which has the following key fields: ProductID, SalesDate, and SalesAmount. If we want to create a calculated column that sums up the SalesAmount for each ProductID and SalesDate combination. We can achieve this by using the AT END OF functionality in the CDS view.

Here's an example of how we can do this:

"sql
@AbapCatalog.sqlViewName: 'ZCDS_SalesData'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sales Data'
define view ZCDS_SalesData
as select from SalesData
{
key ProductID,
key SalesDate,
sum( SalesAmount ) as TotalSalesAmount
}
where ProductID = @ObjectKey.ProductID
group by ProductID, SalesDate
with
{
AT END OF ProductID,
key SalesDate,
sum( TotalSalesAmount ) as GrandTotalSalesAmount
}
"

In this example, we are creating a calculated column called TotalSalesAmount using the sum() function to sum up the SalesAmount for each ProductID and SalesDate combination. We then use the AT END OF functionality to create another calculated column called GrandTotalSalesAmount, which sums up the TotalSalesAmount for each ProductID.

This allows you to group and summarize the sales data based on the keys defined in the CDS view, and calculate the total sales amount for each combination of ProductID and SalesDate.

Thank you.