How To Create Customer Analytical Query With Two Structures

[former_member205880]

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!