Column Sum in CDS Queries

[Former Member]

Hi,

I am currently in the process of exploring the possibilities with BeX queries using CDS views ( in S4HANA 1610 on-premise edition ) but I am not able to get around a specific problem, PFB the details of the same.

We need to get the sum of values appearing in specific cells of a column and display the total in the same column.
I am not familiar with designing BeX queries with query designer, but it appears that this is something that was possible using the query designer.

In order to get around this,

1. I created a separate key figure in the Cube ( CDS view with data category as CUBE) where the values were assigned based on a certain criteria or else zero was assigned.

2. Created a CDS query on top of the said cube with a new key figure where each cell would have the sum of the values appearing in the cube key figure.

In order to accomplish step 2, I assigned the below annotation to the query key figure.

@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'SUM( <cube_key_figure> )'
1 as QueryKeyFigure

But I am getting an error around the formula stating 'Standard functions are not supported'.

Request you to kindly help in getting around this problem.
Also, kindly suggest if there exists a better way of solving it.

Thanks,

Sagar J.

[Former Member]

Just to provide an update on this, I was able to solve this using the SUMGT native BW function.

@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: 'SUMGT( <cube_key_figure> )'
1 as QueryKeyFigure