This blog is about how to use two structures in CDS analytical query as in BW query.
The structure forms the foundational layout of the axes (rows or columns) in a data table, determining how information is organized and presented.
Structural components of key figure structures are always based on the key figure selections (basic key figures, restricted key figures, and calculated key figures). Characteristic structural components cannot contain key figure selections.
The arrangement of the structure affects the order and quantity of key figures or characteristics displayed in the rows and columns of a query or report.
Example: BW Query with Characteristic structure and Key Figure structure
In Analytical query, elements can be assigned to characteristics structure using @AnalyticsDetails.query.onCharacteristicStructure: true. Element with measures get automatically assigned to measure structure.
Formulas can be defined for both characteristic structure and measure structure. The operands of a formula must be elements of the same structure to which the formula belongs. Operands of the other structure are not allowed.
If an analytical query has two structures (a measure and a characteristic structure), then the query result is a grid. Each cell in this grid is defined by an element of the measure structure and one of the characteristic structures. Restrictions are combined with AND. If both elements are formulas, the formula of the measure structure is calculated by default. Also, other properties like scaling, decimals are taken from the element of the measure structure by default. If this should be different, @AnalyticsDetails.query.collisionHandling annotation is used.
collisionHandling:
{ formula : String( 20) enum { DEFAULT; THIS; CONCURRENT; };
decimals : String( 20) enum { DEFAULT; THIS; CONCURRENT; };
scaling : String( 20) enum { DEFAULT; THIS; CONCURRENT; };
semanticObject : String( 20) enum { DEFAULT; THIS; CONCURRENT; };
};
Example:
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘CDS Query with 2 Structures’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY ]
define transient view entity ZLR_2STRUCT_CELL_REF_SIMPLE
provider contract analytical_query
as projection on ZOQ_FLIGHT
{
//—-Elements of Key Figure structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Capacity Economy’
seatsmax,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Economy’
seatsocc,
@AnalyticsDetails.query.axis: #ROWS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Percentage’
@AnalyticsDetails.query.decimals: 5
seatsocc / seatsmax * 100 as percentage,
//—Elements of Characteristics Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2019’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘2019’ then 1 else null end as year2019,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘2020’ then 1 else null end as year2020,
@AnalyticsDetails.query.axis: #COLUMNS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Diffenece 2019-2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.decimals: 2
@AnalyticsDetails.query.collisionHandling: {decimals: #THIS}
$projection.year2019 – $projection.year2020 as difference
}
Output:
To display structure element in a hierarchy, AnalyticsDetials.query.elementHierarchy.parent and @AnalyticsDetails.query.elementHierarchy.initiallyCollapsed annotations are used in CDS.
Example:
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘CDS Query with 2 Structures’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY ]
define transient view entity ZLR_2STRUCT_CELL_REF_HIER
provider contract analytical_query
as projection on ZOQ_FLIGHT
{
//—-Elements of Key Figure structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Capacity Economy’
seatsmax,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Economy’
seatsocc,
@AnalyticsDetails.query.axis: #ROWS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Percentage’
@AnalyticsDetails.query.decimals: 5
seatsocc / seatsmax * 100 as percentage,
//—Elements of Characteristics Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2019’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.elementHierarchy.parent: ‘difference’
case when flyear = ‘2019’ then 1 else null end as year2019,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.elementHierarchy.parent: ‘difference’
case when flyear = ‘2020’ then 1 else null end as year2020,
@AnalyticsDetails.query.axis: #COLUMNS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Diffenece 2019-2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.decimals: 2
@AnalyticsDetails.query.collisionHandling: {decimals: #THIS}
@AnalyticsDetails.query.elementHierarchy.initiallyCollapsed: true
$projection.year2019 – $projection.year2020 as difference
}
Output:
After Expanding Hierarchy
Reference:
This blog is about how to use two structures in CDS analytical query as in BW query.The structure forms the foundational layout of the axes (rows or columns) in a data table, determining how information is organized and presented.Structural components of key figure structures are always based on the key figure selections (basic key figures, restricted key figures, and calculated key figures). Characteristic structural components cannot contain key figure selections.The arrangement of the structure affects the order and quantity of key figures or characteristics displayed in the rows and columns of a query or report.Example: BW Query with Characteristic structure and Key Figure structureIn Analytical query, elements can be assigned to characteristics structure using @AnalyticsDetails.query.onCharacteristicStructure: true. Element with measures get automatically assigned to measure structure.Formulas can be defined for both characteristic structure and measure structure. The operands of a formula must be elements of the same structure to which the formula belongs. Operands of the other structure are not allowed.If an analytical query has two structures (a measure and a characteristic structure), then the query result is a grid. Each cell in this grid is defined by an element of the measure structure and one of the characteristic structures. Restrictions are combined with AND. If both elements are formulas, the formula of the measure structure is calculated by default. Also, other properties like scaling, decimals are taken from the element of the measure structure by default. If this should be different, @AnalyticsDetails.query.collisionHandling annotation is used. collisionHandling: { formula : String( 20) enum { DEFAULT; THIS; CONCURRENT; }; decimals : String( 20) enum { DEFAULT; THIS; CONCURRENT; }; scaling : String( 20) enum { DEFAULT; THIS; CONCURRENT; }; semanticObject : String( 20) enum { DEFAULT; THIS; CONCURRENT; }; };Example: @AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘CDS Query with 2 Structures’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY ]
define transient view entity ZLR_2STRUCT_CELL_REF_SIMPLE
provider contract analytical_query
as projection on ZOQ_FLIGHT
{
//—-Elements of Key Figure structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Capacity Economy’
seatsmax,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Economy’
seatsocc,
@AnalyticsDetails.query.axis: #ROWS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Percentage’
@AnalyticsDetails.query.decimals: 5
seatsocc / seatsmax * 100 as percentage,
//—Elements of Characteristics Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2019’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘2019’ then 1 else null end as year2019,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘2020’ then 1 else null end as year2020,
@AnalyticsDetails.query.axis: #COLUMNS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Diffenece 2019-2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.decimals: 2
@AnalyticsDetails.query.collisionHandling: {decimals: #THIS}
$projection.year2019 – $projection.year2020 as difference
} Output: To display structure element in a hierarchy, AnalyticsDetials.query.elementHierarchy.parent and @AnalyticsDetails.query.elementHierarchy.initiallyCollapsed annotations are used in CDS.Example: @AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘CDS Query with 2 Structures’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [ #ANALYTICAL_QUERY ]
define transient view entity ZLR_2STRUCT_CELL_REF_HIER
provider contract analytical_query
as projection on ZOQ_FLIGHT
{
//—-Elements of Key Figure structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Capacity Economy’
seatsmax,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Economy’
seatsocc,
@AnalyticsDetails.query.axis: #ROWS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Percentage’
@AnalyticsDetails.query.decimals: 5
seatsocc / seatsmax * 100 as percentage,
//—Elements of Characteristics Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2019’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.elementHierarchy.parent: ‘difference’
case when flyear = ‘2019’ then 1 else null end as year2019,
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Calender Year 2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.elementHierarchy.parent: ‘difference’
case when flyear = ‘2020’ then 1 else null end as year2020,
@AnalyticsDetails.query.axis: #COLUMNS
@Aggregation.default: #FORMULA
@EndUserText.label: ‘Diffenece 2019-2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
@AnalyticsDetails.query.decimals: 2
@AnalyticsDetails.query.collisionHandling: {decimals: #THIS}
@AnalyticsDetails.query.elementHierarchy.initiallyCollapsed: true
$projection.year2019 – $projection.year2020 as difference
} Output: After Expanding HierarchyReference:Working with Structures Read More Technology Blogs by SAP articles
#SAP
#SAPTechnologyblog
