ABAP CDS View Default Aggregation #FORMULA

[Former Member]

Dear SAP consultants,

I am trying to use the ' @DefaultAggregation: #FORMULA ' aggregation in a CDS View but I cannot make it work.

The business requirement is the following: We have the quantity ordered and the quantity sold and we have to provide the percentage of the goods sold out of the ordered quantity.

For the Quantity Ordered and Quantity Sold I used '@DefaultAggregation: #SUM' and for the calculated column I used the below code snippet taken from another post:

@Aggregation.default:#FORMULA
@AnalyticsDetails.query.formula:'NDIV0(QtaSold / QtaOrdered ) * 100'
@AnalyticsDetails.query.decimals:2
1 as Percentage

While testing the CDS, I am getting '1' across the whole output for the calculated column.

Could you please advise what am I missing?

[Former Member]

Dear SAP consultants,

I am trying to use the ' @DefaultAggregation: #FORMULA ' aggregation in a CDS View but I cannot make it work.

The business requirement is the following: We have the quantity ordered and the quantity sold and we have to provide the percentage of the goods sold out of the ordered quantity.

For the Quantity Ordered and Quantity Sold I used '@DefaultAggregation: #SUM' and for the calculated column I used the below code snippet taken from another post:

@Aggregation.default:#FORMULA
@AnalyticsDetails.query.formula:'NDIV0(QtaSold / QtaOrdered ) * 100'
@AnalyticsDetails.query.decimals:2
1 as Percentage

While testing the CDS, I am getting '1' across the whole output for the calculated column.

Could you please advise what am I missing?

[gasparerdelyi]

As your definition says

1 as percentage

there is no surprise that you see 1 for all rows

Documentation for @AnalyticsDetails.query.formula says:

This annotation allows you to specify the formula expression, which cannot be expressed as an SQL formula (operands required from the element list of the view). Only numerical values (measures) can be used as operands.

Scope: #ELEMEN

Evaluation Runtime (Engine): This annotation will be interpreted as a formula by the analytic manager.

The formula for the annotation will not be evaluated for queries not going through the analytical manager -- which way do you test the view?

You could move the formula to a SQL expression and remove the @AnalyticsDetails.query.formula annotation -- and then of course you would need to deal with zero value for the divisor. The best way could be here a UNION ALL with WHERE conditions on QtaOrdered field in both branches (one branch WHERE QtaOrdered <> 0 and another WHERE QtoOrdered = 0 )

[Former Member]

Hi Gaspar,

Thank you for your reply!

Actually the formula was ok but I`ve just learned that you cannot test an Analytical CDS by just running it with F8. You have to use T-Code RSRT in order to see the Formula aggregated values correctly.

When running the transaction you need to add "2C" in from of the sqlViewName of your CDS.

Have a nice day!

Alex

[gasparerdelyi]

To be precise, you can test the CDS view with F8 / Data Preview but that is not going through the Analytical Manager at all.

If you would like to test via the Analytical Manager from SAP GUI, then you can use RSRTS_ODP_DIS on ODP provider / cube level and RSRT on query level.