How to use Analytical Formula in ABAP CDS Query -

How to use Analytical Formula in ABAP CDS Query

Analytical Formula in CDS Query is a powerful function that allows you perform advanced calculations. A normal calculations such as addition, deduction, multiplication, division can be done as CDS in-built arithmetic expression. Anlytical formula offers advanced functions such as handling calculation error and catching exception, casting data type, percentage functions, etc. Available functions are(should be) the same in BW query(Bex Query Desginer) and is documented in below page.

SAP Library – XML: BW – OLAP

This blog will introduce a glimpse of how to user analytical formula in CDS query.

Build CDS query

Build your own CDS query. In this blog, I_GLAccountLineItemCube is used as source analytical cube. We are just adding the key fields from the source cube and Amount in Company Code and Company Code Currency. We will also add a parameter Fiscal Period.

@EndUserText.label: 'Test Query to analytical expression'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view entity YTEST_QUERY
with parameters
@EndUserText.label: 'Fiscal Period'
P_FiscalPeriod : poper
as select from I_GLAccountLineItemCube as I_GLAccountLineItemCube
{

@Consumption.filter: {selectionType: #SINGLE, multipleSelections: false, mandatory: true}
@Consumption.derivation: { lookupEntity: 'I_Ledger',
resultElement: 'Ledger', binding: [
{ targetElement : 'IsLeadingLedger' , type : #CONSTANT, value : 'X' } ]
}
@AnalyticsDetails.query.variableSequence : 10
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.display: #KEY_TEXT
Ledger,

@Consumption.filter: {selectionType: #SINGLE, multipleSelections: true, mandatory: true}
@AnalyticsDetails.query.variableSequence : 20
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
CompanyCode,

@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: { selectionType: #RANGE, multipleSelections: true, mandatory: false }
AccountingDocument,

///////////////////////////////////////////////////////////////////////
// Measures - Amounts
///////////////////////////////////////////////////////////////////////
@AnalyticsDetails.query.axis: #FREE
CompanyCodeCurrency,

@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
@AnalyticsDetails.query.axis: #COLUMNS
@DefaultAggregation: #SUM
AmountInCompanyCodeCurrency
};

Add Analytical Formula

Now let’s say we have requirement where we want the average amount of each fiscal period for company code currency. For this purpose, we can use parameter Fiscal Period to divide Amount In Company Code Currency, Fiscal Period will be a user input so the calculation can be performed dynamically. To perform this calculation, first you can try to do this with CDS arithmetic expression.

It returns syntax error, because the paramter Fiscal Period is in Numc data type, which is not supported for arithmetic calculation(yet). To make it work, we can use data function NODIM which will nullify the data type so that the calculation will simply look at the value when calculating. This NODIM, however, is not supported by arithmetic expression so it will give an error if it’s used.

Instead, this should be written inside analytical formula. To use it, below annotations must be defined.

@DefaultAggregation: #FORMULA	‘The value FORMULA indicates, that the element is a formula which has to be calculated after the operands have been determined by aggregation or calculation. It should never be aggregated.
@AnalyticsDetails.query.formula:	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.

Add below annotations and figure and name it Amount_Devided_By_Period. You can division is done in annotation @AnalyticsDetails.query.formula. Here another function NDIV0 is used to catch exception in case 0 is the input fiscal period.

  @AnalyticsDetails.query.decimals: 2
  @DefaultAggregation: #FORMULA
  @AnalyticsDetails.query.formula:  'NDIV0( $projection.AmountInCompanyCodeCurrency / NODIM( $parameters.P_FiscalPeriod ) )'
  @AnalyticsDetails.query.axis: #FREE
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  @EndUserText.label: 'Amount devided by period'
  cast ( 1 as abap.curr( 23, 2 ) ) as Amount_Devided_By_Period

Testing

Activate the query and go to RSRT and open the query with WD grid. Enter Company Code and Fiscal Period as 10 to test the division. You can see that Amount divided by Period measure is the output of Amount in CompanyCodle Currency / Period number in the input prompt.