This blog is about how to use cell definition in CDS analytical query as like in BW query. Lets start with brief overview about two structures.

Two Structures

The structure forms the foundational layout of the axes (rows or columns) in a data table, determining how information is organized and presented. 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. More information can be found in CDS : How to define analytical query using two str… – SAP Community

Cell Definition

A cell is the intersection between two structural components. Each cell in grid is defined by an element of the measures-structure and one of the characteristic-structure.

Exception cell: When implicit cell definition overwritten by an explicit definition, cell is called as exception cell. It can be defined using formula or selection. Formulas for a cell must only use operands which are cells.

Help cell: It does not have any direct connection with the corresponding structural elements. It is not embedded in the grid and not displayed, but can be used in formulas of an exception cell.

In BW modelling tool query designer, In ‘Cell’ tab we can specify the cell definition.

In the above screenshot marked,

Red : Exception cell

Green : Formula to calculate the value for the exception cell

Blue : Help cell

In analytical query to define the Exception cell, following annotation is used.

@AnalyticsDetails.query: {

isCell: true,

cellReference: {

measureStructureElement: ‘seatsocc’,

characteristicStructureElement: ‘yearPlaceholder’

}

A cell is specified with “@AnalyticsDetails.query.isCell: true”,

If it should overwrite a cell of the grid, then the coordinates can be specified with @AnalyticsDetails.query.cellReference (Exception Cell)Otherwise, the element is invisible and must be hidden Consumption.hidden:true. (Help Cell)

A value of a cell in the grid can be obtained with CDS function – GET_CELL_REFERENCE_VALUE, which can be used in formula of an exception or help cell.

Example

In the following example, ‘exceptionCellRatioPercent’ is Exception cell, cellReference defines exact coordinate of this cell. ‘helpCellratio’ is a helper cell, it does not have cellReference, it is not part of grid and has Consumption.hidden:true. It is used in formula of an exception cell ‘exceptionCellRatioPercent’ to calculate percentage of occupied seats.

@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Query with structure and cell definition’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
// CDS Query like the normal query ZLR_2STRUCT_CELL_CAL
define transient view entity ZLR_2STRUCT_CELL_REF
provider contract analytical_query
as projection on ZOQ_FLIGHT
{

//Elements of the Key Figure Structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Seats’
seatsmax,

@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Seats’
seatsocc,

//Elements of the Characteristic Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘2020’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘2020’ then 1 else null end as year2020,

@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: ‘Occupied %’
@AnalyticsDetails.query.onCharacteristicStructure: true
case when flyear = ‘0000’ then 1 else null end as yearPlaceholder,

abap.unit’%’ as unitPercent,

//Help cell – used in formula of exception cell
@Aggregation.default: #FORMULA
@AnalyticsDetails.query: {
isCell: true
}
@Consumption.hidden: true
// scalar function with use of cell refernce
ratio_of( portion => GET_CELL_REFERENCE_VALUE(measure_struc_element => $projection.seatsocc, characteristic_struc_element => $projection.year2020 ),
total => GET_CELL_REFERENCE_VALUE(measure_struc_element => $projection.seatsmax, characteristic_struc_element => $projection.year2020) ) as helpCellratio,

// Exception cell
@Aggregation.default: #FORMULA
@AnalyticsDetails.query: {
isCell: true,
cellReference: {
measureStructureElement: ‘seatsocc’,
characteristicStructureElement: ‘yearPlaceholder’

}
}
@AnalyticsDetails.query.decimals: 2
@Semantics.quantity.unitOfMeasure: ‘unitPercent’
$projection.helpCellratio * 100 as exceptionCellRatioPercent

}

Output

More information can be found Cell, Exception Cell and Help Cell | SAP Help Portal

This blog is about how to use cell definition in CDS analytical query as like in BW query. Lets start with brief overview about two structures. Two StructuresThe structure forms the foundational layout of the axes (rows or columns) in a data table, determining how information is organized and presented. 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. More information can be found in CDS : How to define analytical query using two str… – SAP CommunityCell DefinitionA cell is the intersection between two structural components. Each cell in grid is defined by an element of the measures-structure and one of the characteristic-structure. Exception cell: When implicit cell definition overwritten by an explicit definition, cell is called as exception cell. It can be defined using formula or selection. Formulas for a cell must only use operands which are cells. Help cell: It does not have any direct connection with the corresponding structural elements. It is not embedded in the grid and not displayed, but can be used in formulas of an exception cell. In BW modelling tool query designer, In ‘Cell’ tab we can specify the cell definition.In the above screenshot marked,Red : Exception cell Green : Formula to calculate the value for the exception cellBlue : Help cellIn analytical query to define the Exception cell, following annotation is used. @AnalyticsDetails.query: {

isCell: true,

cellReference: {

measureStructureElement: ‘seatsocc’,

characteristicStructureElement: ‘yearPlaceholder’

}

} A cell is specified with “@AnalyticsDetails.query.isCell: true”,If it should overwrite a cell of the grid, then the coordinates can be specified with @AnalyticsDetails.query.cellReference (Exception Cell)Otherwise, the element is invisible and must be hidden Consumption.hidden:true. (Help Cell)A value of a cell in the grid can be obtained with CDS function – GET_CELL_REFERENCE_VALUE, which can be used in formula of an exception or help cell. ExampleIn the following example, ‘exceptionCellRatioPercent’ is Exception cell, cellReference defines exact coordinate of this cell. ‘helpCellratio’ is a helper cell, it does not have cellReference, it is not part of grid and has Consumption.hidden:true. It is used in formula of an exception cell ‘exceptionCellRatioPercent’ to calculate percentage of occupied seats. @AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: ‘Query with structure and cell definition’
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
// CDS Query like the normal query ZLR_2STRUCT_CELL_CAL
define transient view entity ZLR_2STRUCT_CELL_REF
provider contract analytical_query
as projection on ZOQ_FLIGHT
{

//Elements of the Key Figure Structure
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Max Seats’
seatsmax,

@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: ‘Occupied Seats’
seatsocc,

abap.unit’%’ as unitPercent,