SAP Extensibility 101

Analytical variable in ABAP CDS query and restricted measures

This blog will demonstrate how to create analytical variable in ABAP CDS and leverage to easily create dimension- restricted / time-restricted measures.

Scenerio

An ABAP CDS query is created based on I_GLAccountLineItemCube that exposes financial documents. Two measures are required in this query:

• Year-to-date amount in company code currency(time restricted)
• Balance Sheet amount in company code currency(dimension restricted)

To start with the exercise, you must create the ABAP CDS query below.

@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

    @Consumption.defaultValue: 'K4'
    @Consumption.hidden: true
    P_FiscalYearVariant : fc_periv

  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,

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

  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
  ProfitCenter,

  @AnalyticsDetails.query.axis: #FREE
  PostingDate,

  @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

};

Year-to-date amount in company code currency(time restricted)

Create Year-to-date range in CDS view

Create a new CDS entity view as below, which extracts year to date range based on the current system date. I_FiscalCalendarDtePrevPeriods contains year-to-date start date(FiscalYearStartDate ) and year-to-date end date(FiscalYearEndDate), depending on the base date field ‘CalendarDate’.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.representativeKey: 'FiscalYearVariant'
@ObjectModel: {
  supportedCapabilities: [#DERIVATION_FUNCTION],
  modelingPattern: #DERIVATION_FUNCTION
  }
@ObjectModel.derivationFunction: {
  applicableFor.dataType:#DATS,
  result: {
    type: #INTERVAL,
    multipleRecords: false,
    element: 'StartDate',
    elementHigh: 'EndDate'
  }
}
@VDM.viewType: #DERIVATION_FUNCTION
@EndUserText.label: 'YTD Range'
define view entity Y_YTD_RANGE
  with parameters

    P_FiscalYearVariant : fis_periv

  as select from I_FiscalCalendarDtePrevPeriods
{

  key CalendarDate,
  key FiscalYearVariant,
      FiscalYearStartDate as StartDate,
      FiscalYearEndDate   as EndDate
}

where
      FiscalYearVariant = $parameters.P_FiscalYearVariant
  and CalendarDate      = $session.system_date

When you preview the data with current date of 27.06.2024, the year-to-date start/end date are correctly mapped.

Create analytical variable and Year-to-date restricted measure

In the ABAP CDS query YTEST_QUERY, add below code as paramter which uses the year-to-date range CDS we just created. This is a variable contianing the interval of year-to-date, therefore it requires two elements(StartDate and EndDate) from Y_YTD_RANGE. We are also passing Fiscal Year Variant, as it is a key field to determine year to date start and end. As a reference element, PostingDate is refered as this is the dimension we will filter with this variable.

    @Consumption.hidden :true
    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'PostingDate', mandatory: true, selectionType: #INTERVAL, multipleSelections: false }
    @Consumption.derivation: {
       lookupEntity: 'Y_YTD_RANGE',
       resultElement: 'StartDate',
       resultElementHigh: 'EndDate',
       binding : [ { targetParameter : 'P_FiscalYearVariant', type: #PARAMETER, value: 'P_FiscalYearVariant' } ] }

    P_YTD_RANGE         : fis_budat

Now let’s create the restricted measure that only shows the amount if the pointg date is within the range of Year-to-date. Add below code as query fields.

The query field ‘PostingDate’ is filtered with variable range of Year-to-date and ‘AmountInCompanyCodeCurrency’ that meets this condition will be aggregated with annotation ‘@Aggregation.default: #SUM’.

  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'CC Amount(restricted to YTD)'
  case
    when PostingDate      = $parameters.P_YTD_RANGE
    then curr_to_decfloat_amount( AmountInCompanyCodeCurrency )
  end as AmountCompanyCodeYTD

Activate all the objects and preview the query in tcode RSRT with WD Grid. You can see that CC amount (restricted to YTD) is displayed. It is a sum of current year postings for each GL account.

Balance Sheet amount in company code currency(dimension restricted)

Now with the same principle, create another CDS view that will contain only Balance Sheet account. This is achived by filtering ska1-xbilk(Balance Sheet flag).

@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.representativeKey: 'saknr'
@ObjectModel: {
  supportedCapabilities: [#DERIVATION_FUNCTION],
  modelingPattern: #DERIVATION_FUNCTION
  }
@VDM.viewType: #DERIVATION_FUNCTION
@EndUserText.label: 'Balance Sheet Account'
define view entity Y_BSAccount

  as select from ska1
{
  key ktopl,
  key saknr
}

where
      ktopl = 'CHVS' //chart of account
  and xbilk = 'X' //BalanceSheet account

Now go to YTEST_QUERY and add another analytical variable using Y_BSAccount. This time it is not time range but rather list of single values therefore selection Type = SINGLE and multipleSelections = true.

    @Consumption.hidden :true
    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'GLAccount', mandatory: true, selectionType: #SINGLE, multipleSelections: true }
    @Consumption.derivation: {
       lookupEntity: 'Y_BSAccount',
       resultElement: 'saknr' }

    P_BS_ACCOUNT        : saknr,

WIth the same principle, add another restricted measure using variable P_BS_ACCOUNT.

  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'CC Amount(restricted to Balance Sheet)'
  case
    when GLAccount      = $parameters.P_BS_ACCOUNT
    then curr_to_decfloat_amount( AmountInCompanyCodeCurrency )
  end as AmountCompanyCodeBS  

Now the whole code looks this in CDS query.

@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

    @Consumption.defaultValue: 'K4'
    @Consumption.hidden: true
    P_FiscalYearVariant : fc_periv,

    @Consumption.hidden :true
    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'GLAccount', mandatory: true, selectionType: #SINGLE, multipleSelections: true }
    @Consumption.derivation: {
       lookupEntity: 'Y_BSAccount',
       resultElement: 'saknr' }

    P_BS_ACCOUNT        : saknr,

    @Consumption.hidden :true
    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'PostingDate', mandatory: true, selectionType: #INTERVAL, multipleSelections: false }
    @Consumption.derivation: {
       lookupEntity: 'Y_YTD_RANGE',
       resultElement: 'StartDate',
       resultElementHigh: 'EndDate',
       binding : [ { targetParameter : 'P_FiscalYearVariant', type: #PARAMETER, value: 'P_FiscalYearVariant' } ] }

    P_YTD_RANGE         : fis_budat

  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,

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

  @AnalyticsDetails.query.axis: #FREE
  @AnalyticsDetails.query.display: #KEY_TEXT
  ProfitCenter,

  @AnalyticsDetails.query.axis: #FREE
  PostingDate,

  @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,

  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'CC Amount(restricted to YTD)'
  case
    when PostingDate      = $parameters.P_YTD_RANGE
    then curr_to_decfloat_amount( AmountInCompanyCodeCurrency )
  end as AmountCompanyCodeYTD,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'CC Amount(restricted to Balance Sheet)'
  case
    when GLAccount      = $parameters.P_BS_ACCOUNT
    then curr_to_decfloat_amount( AmountInCompanyCodeCurrency )
  end as AmountCompanyCodeBS  
};

You will see that two restricted measures are add in the report. For ‘CC Amount(restricted to Balance Sheet)’, no value is shown if the GL account is not Balance Sheet.