Set attributes of dimension by default in ABAP CDS query

In this blog, we will talk about a simple requirement where attributes of dimension need to be displayed as a in the default layout of analytical query.

The key components of CDS analytical query comes are dimension and measure and user can slice and dice the report by adding/removing them so the report displays the certain layer of result as user sees fit.

Attribute is like a sub-dimension within single dimension that drills down the data further down.

Let’s take a look at below example. Right click on dimension GL account -> Drilldown -> Add Attributes. Fields from G/L account master data are available for selection. Add G/L account type, for example, and the report will be little more user friendly as accountant can easily navigate which accounts are balance sheet account or profit&loss account.

It’s a nice feature however, is it possible to bring in the attribute when the report opens? Let’s try it out with the below same ABAP CDS query.

@EndUserText.label: 'Test Query'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view entity YTEST_QUERY

  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.variableSequence: 40  
  @AnalyticsDetails.query.axis: #ROWS  
  _GLAccountInChartOfAccounts.GLAccountType,
  
///////////////////////////////////////////////////////////////////////
  // Measures - Amounts
  ///////////////////////////////////////////////////////////////////////
  @AnalyticsDetails.query.axis: #FREE
  CompanyCodeCurrency,

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

};

Above code will generate a analytical query that allows user to bring in the attribute “G/L account type” when the report opens. It can also be observed that the attribute is not listed on the left side navigation panel.

The highlighted part of the code is the attribute of G/L account which is set by default with annotation “@AnalyticsDetails.query.axis: #ROWS”. The convenient part of attribute is that it comes from foreign Key association defined in the cube level. This way cube does not have to define all the attributes in the CDS cube for query to consume. Query will pull the attributes as needed from foreign Key association.

By ABAP