topic Re: ABAP CDS View Default Aggregation #FORMULA in Application Development and Automation Discussions

ABAP CDS View Default Aggregation #FORMULA

Former Member — Tue, 23 Jun 2020 08:06:38 GMT

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?

Re: ABAP CDS View Default Aggregation #FORMULA

gasparerdelyi — Tue, 23 Jun 2020 09:43:10 GMT

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 )

Re: ABAP CDS View Default Aggregation #FORMULA

Former Member — Thu, 02 Jul 2020 12:05:14 GMT

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

Re: ABAP CDS View Default Aggregation #FORMULA

gasparerdelyi — Fri, 03 Jul 2020 21:05:49 GMT

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.