Consume CDS analytical query from ABAP program

This blog introduces Lightweight BICS API, which is a new SAP standard ABAP API to consume analytical query built on CDS and BW.

Refer to SAP note 3269710 for initial shipment information. 3269710 – LBA: Lightweight BICS API – initial shipment – SAP for Me

For functional roundups, please refer to SAP note 3340054 3340054 – LBA: Lightweight BICS API – functional roundups – SAP for Me.

LBA comes with 2 flavours:

for classic analytical queries ( CL_LBA_STD_QUERY )
for CDS based analytical queries ( CL_LBA_CDS_QUERY )

In this blog, we will touch on the latter case as it is more mainstream to use CDS and embedded analytics engine in S4. We will create a demo CDS query and consume it using CL_LBA_CDS_QUERY in demo ABAP program.

Create a demo CDS query

@EndUserText.label: 'Test Query for ABAP consumtion'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
define view entity YTEST_QUERY_FROM_ABAP
  with parameters

    P_CompanyCode : bukrs
    
  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
}
where CompanyCode = $parameters.P_CompanyCode;

Go to tcode RSRT and preview the query with WD grid(or display of your choice).

Enter a company code and run the report. For the demo purpose, I will filter the data on the posting date on 31.12.2016.

In the next step, let’s create an ABAP program to call this query and filter the data so it will get exactly 2,499,996 USD.

ABAP program to consume CDS query

Create below program in ADT or SE80. The comments are self-explanatory but to add explanation,

Instantiation of cl_lba_cds_query requires query name ‘YTEST_QUERY_FROM_ABAP’.
Set all required dimensions and measures in lt_requested_columns
Set all required parameters in lt_parameter_values
Set filter is neessary in lt_filter
Call method get_resultset to consume CDS query

REPORT yay_test_call_cdsquery.
"Calling analytical query
DATA: lt_requested_columns TYPE cl_lba_std_query=>tn_t_columns_req,
      lt_parameter_values  TYPE cl_lba_cds_query=>tn_t_parameter_values,
      lt_filter            TYPE cl_lba_cds_query=>tn_t_filter_values.
FIELD-SYMBOLS:<lw_requested_columns> TYPE cl_lba_std_query=>tn_s_column_req,
              <lw_filter>            TYPE cl_lba_cds_query=>tn_s_filter_value,
              <lw_parameter_values>  TYPE cl_lba_cds_query=>tn_s_parameter_value.

"Create an instance of Lightweight BICS API 
TRY.
    DATA(lr_cds_query) = NEW cl_lba_cds_query( i_view_name = 'YTEST_QUERY_FROM_ABAP'  ).
  CATCH cx_lba_query.
    RETURN.
ENDTRY.

"Requested measures and dimenions.
APPEND INITIAL LINE TO lt_requested_columns ASSIGNING <lw_requested_columns>.
<lw_requested_columns>-name = 'CompanyCode'.
APPEND INITIAL LINE TO lt_requested_columns ASSIGNING <lw_requested_columns>.
<lw_requested_columns>-name = 'PostingDate'.
APPEND INITIAL LINE TO lt_requested_columns ASSIGNING <lw_requested_columns>.
<lw_requested_columns>-name = 'CompanyCodeCurrency'.
APPEND INITIAL LINE TO lt_requested_columns ASSIGNING <lw_requested_columns>.
<lw_requested_columns>-name = 'AmountInCompanyCodeCurrency'.

"Parameters
APPEND INITIAL LINE TO lt_parameter_values ASSIGNING <lw_parameter_values>.
<lw_parameter_values>-name = 'P_CompanyCode'.
<lw_parameter_values>-value = '1001'.
"Filters
APPEND INITIAL LINE TO lt_filter ASSIGNING <lw_filter>.
<lw_filter>-name = 'PostingDate'.
<lw_filter>-sign = 'I'.
<lw_filter>-opt = 'EQ'.
<lw_filter>-value_low = '20161231'.


TRY.
    "Call CDS analytical query
    lr_cds_query->get_resultset(
                   EXPORTING i_t_requested_columns = lt_requested_columns       "dimensions&keyfigures for resultset
                             i_t_parameter_values  = lt_parameter_values        "mandatory and optional variables
                             i_t_filter_values     = lt_filter                  "additional filters
                    IMPORTING e_r_resultset         = DATA(lr_resultset)         "resultset table
                              e_t_column_catalog    = DATA(lt_column_catalog) ). "resultset description
  CATCH cx_lba_query INTO DATA(lo_return).
    DATA(lv_text) = lo_return->get_text( ).
ENDTRY.

BREAK-POINT.

The query output is stored in lr_resultset and can be accessed via ‘LR_RESULTSET->*’. You can see that posting date is filtered to ‘20161231’ and amount 2,499,996 USD has been fetched.