How To Create Customer Analytical Query With Two Structures

Introduction

In this post I would like to demonstrate creation of custom analytical query with 2 structures. I am using S/4HANA 2020 for this demonstration.

This post is mostly about functionality and technical part. If you would like to know more about Embedded Analytics in general and about its application in financial area in particular, I would recommend to look at Analytics on the Universal Journal blog.

I. Creation of the query in Customer Analytical Query application

1.Name query and select data source

2.Select measures and characteristics

3.Add characteristic structure element

4.Name it and define type as restricted

5.Define restriction conditions

Notes:

• There could be fixed values or parameters in different combinations.
• Restriction could be based on only one field or several ones.

Make the same step for 2020 fiscal year.

6.Add other characteristic structure element with type calculated

7.Define formula

8.Add some filtration in query.

One is based on constant value

Another based on variable

9. Save, Publish and Preview

Enter variable, change default value if needed

10. Query shows data.

It has characteristic structure in a rows and measures structure in columns.

II. ADT: Analyze CDS view code

@AbapCatalog.sqlViewName : 'ZZ1_STRUCTUR_1'
@Analytics.query: true
@EndUserText.label: 'CAQ Structures'

define view ZZ1_STRUCTURES
 as select from I_GLACCOUNTLINEITEMCUBE
{AccountAssignment,
 AmountInCompanyCodeCurrency,
 ControllingArea,
 CostCenter,
 Segment,
 GLAccount,
 @AnalyticsDetails.query.onCharacteristicStructure: true
 @EndUserText.label: '2021'
 case 
when 
 ( 
	FiscalYear	=	'2021'
 ) 
  then 1
end as YEAR_RKF_001,
 @AnalyticsDetails.query.onCharacteristicStructure: true
 @EndUserText.label: '2020'
 case 
when 
 ( 
	FiscalYear	=	'2020'
 ) 
then 1
end as YEAR_RKF_002,
 @AnalyticsDetails.query.onCharacteristicStructure: true
 @DefaultAggregation: #FORMULA
 @AnalyticsDetails.query.formula: '$projection.YEAR_RKF_002 + $projection.YEAR_RKF_001'
 @EndUserText.label: 'Total'
 0 as Total,
 DebitCreditCode,
 
 @AnalyticsDetails.query.displayHierarchy: #OFF
 @AnalyticsDetails.query.hierarchyInitialLevel: 3
 
 @AnalyticsDetails.query.hierarchyBinding : [
{type :#CONSTANT,value :''}
]
 @Consumption.filter :{ selectionType: #SINGLE, multipleSelections: false, mandatory: true, defaultValue: '1710' }
 
 CompanyCode
}
 where 
 ( 
	DebitCreditCode	=	'S'
 )     

Notice that we have this annotation for each element of characteristic structure:

@AnalyticsDetails.query.onCharacteristicStructure: true

III. ADT. Copy ABAP CDS code and modify it

1. Create new ABAP CDS view

2.Entering this code. We just and 2019 as new structure element in ADT.

@AbapCatalog.sqlViewName: 'YY1_ADT_STR_1'
@Analytics.query: true
@EndUserText.label: 'ADT Structures'
define view YY1_ADT_STRUCTURES
  as select from I_GLAccountLineItemCube
{
  AccountAssignment,
  AmountInCompanyCodeCurrency,
  ControllingArea,
  CostCenter,
  Segment,
  GLAccount,
  @AnalyticsDetails.query.onCharacteristicStructure: true
  @EndUserText.label: '2021'
  case
  when
  (
     FiscalYear  =   '2021'
  )  then 1
  end as YEAR_RKF_001,
  @AnalyticsDetails.query.onCharacteristicStructure: true
  @EndUserText.label: '2020'
  case
  when
  (
     FiscalYear  =   '2020'
  )  then 1
  end as YEAR_RKF_002,
  @AnalyticsDetails.query.onCharacteristicStructure: true
  @EndUserText.label: '2019'
  case
  when
  (
     FiscalYear  =   '2019'
  )  then 1
  end as YEAR_RKF_003,
  @AnalyticsDetails.query.onCharacteristicStructure: true
  @DefaultAggregation: #FORMULA
  @AnalyticsDetails.query.formula: '$projection.YEAR_RKF_001 + $projection.YEAR_RKF_002 + $projection.YEAR_RKF_003'
  @EndUserText.label: 'Total'
  0   as Total,
  DebitCreditCode,

  @AnalyticsDetails.query.displayHierarchy: #OFF
  @AnalyticsDetails.query.hierarchyInitialLevel: 3

  @AnalyticsDetails.query.hierarchyBinding : [
  {type :#CONSTANT,value :''}
  ]
  @Consumption.filter :{ selectionType: #SINGLE, multipleSelections: false, mandatory: true, defaultValue: '1710' }

  CompanyCode
}
where
  (
    DebitCreditCode = 'S'
  )

3.Let’s look at result in Query Browser application

New structure element also show data for 2019 year and Total calculation is currently based on 3 years.

Conclusion

We covered in this post:

• creation of customer analytical query with 2 structures
• analysis of generated ABAP CDS view code
• creation modified version of this query directly in ADT

Thank you for attention!