Technical Articles
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!
I create one report using cube Journal Entry Item - Cube (I_JOURNALENTRYITEMCUBE) but i don´t see field Cost Center Group and Cost Element Group how i add this fields
That's what I've been waiting for for ages.
Thank's a lot!