Analytical Queries based on CDS are a powerful way to model analytical data sources that can be used in a variety of user interfaces (SAP Analytics Cloud, Analysis for Office, Smart Business KPIs, Web Dynpro Grid, …). Just by reading the technical documentation it is not always easy to transform the business use case into actual coding. Read this blog post in case you are interested to get a rather use case driven access to Analytical Query Design.

(all coding samples are based on CDS Analytical Projection Views – but they should work as well for CDS View (Entities) with sligh adoptions )

Contents:

• Use Case Description
• Used Syntax Elements
• Sample Query
• Runtime Preview
• Links

Use Case Description

During the runtime of a report it is not always visible which data has been selected. Either because the filter bar is hidden, variable values are set via a variant, are derived or hard coded in the query.

For important selections it can make sense to show them as part of the labels of the report elements – Dimensions or Measures.

To make it concrete – lets define a query that selects the following Finance Data:

• Actual vs. Plan Data – for the Plan Data lets show the actually selected Plan Category in the Label of the measure
• Cost Centers based on a given query key date – we show the key date as part of the label of the Cost Center

Used Syntax Elements

• Annotation: “@Semantics.businessDate.at: true”
  Marks a parameter of type date as the query key date which is then used to select time dependent master data and hierarchies as of that key date.
• Annotation: “@Consumption.dynamicLabel”
  Allows to define dynamic labels. You can use placeholders in the label that can be replaced by parameter values during runtime. Just like normal labels, also dynamic labels get translated.

Sample Query

@EndUserText.label: 'Query with Dynamic Labels'
@AccessControl.authorizationCheck: #NOT_ALLOWED
define transient view entity ZJF_C_DynLabels_Q01
  provider contract analytical_query
  with parameters

    @Semantics.businessDate.at: true
    @Environment.systemField: #SYSTEM_DATE    
    P_KeyDate : vdm_v_key_date,

    @EndUserText.label: 'Plan Category'
    @AnalyticsDetails.query.variableSequence: 20
    @AnalyticsDetails.variable: { usageType: #FILTER, referenceElement: 'PlanningCategory', mandatory: true, selectionType: #SINGLE, multipleSelections: false, defaultValue: 'BUDGET03' }    
    P_PlanningCategory : fcom_category    
  
  as projection on I_ActualPlanJrnlEntryItemCube
{

  @AnalyticsDetails.query.axis: #ROWS
  @UI.textArrangement: #TEXT_LAST
  @Consumption.dynamicLabel: { label : 'Cost Center (&1)' ,
                               binding : [ { index : 1 , parameter : 'P_KeyDate'  } ] }
  CostCenter,

  @AnalyticsDetails.query.axis: #FREE
  GlobalCurrency,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'GlobalCurrency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'Actual Amount'
  case
    when ( PlanningCategory = 'ACT01') then curr_to_decfloat_amount( AmountInGlobalCurrency )
  end as ActualAmountInGlobCurrency,

  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'GlobalCurrency'
  @Aggregation.default: #SUM
  @Consumption.dynamicLabel: { label : 'Plan Amount (&1)' ,
                               binding : [ { index : 1 , parameter : 'P_PlanningCategory'  } ] }
  case
    when ( PlanningCategory = $parameters.P_PlanningCategory ) then curr_to_decfloat_amount( AmountInGlobalCurrency )
  end as PlanAmountInGlobCurrency,
  
  @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'GlobalCurrency'
  @Aggregation.default: #FORMULA
  @EndUserText.label: 'Plan Actual Delta'
  $projection.PlanAmountInGlobCurrency - $projection.ActualAmountInGlobCurrency as DeltaAmountInGlobCurrency

} 
  where Ledger = '0L' and LedgerFiscalYear = '2021'

Runtime Preview

This preview is done with the Web Dynpro Data Grid that I started view the backend transaction RSRT with the technical query name “2CZJF_C_DynLabels_Q01”.

The dynamic labels are marked in red showing the Key Date and the Plan Category inside ().

Dynamic Labels (screenshot taken from test-system)

Links

The following sources provide further information: