Overriding Individual Cell Values
In some cases, a cell in a grid defined by a measure structure on one axis and a characteristic structure on the opposing axis, should display a different value than what is derived from the definition of the elements of the structures. For example, where formulas collide at an intersection cell, you may choose not to generate a value, or you might want to display a fixed value. This is often the case when the formula result would not make sense in either the horizontal or vertical direction. For example, when the formula would calculate a percentage of a percentage.
In that case, you can define an exception cell which will replace the standard result of that cell.
The element requires the annotation @AnalyticsDetails.query.isCell: true to indicate that it is a definition of an exception cell and not a member of the measure structure or the characteristic structure.
The definition of a cell follows the rules for members of the measure structure. The operands of a formula must be cells.
To overwrites an existing cell, you need to define an exception cell using the annotation @AnalyticsDetails.query.cellReference.measureStructureElement and characteristicStructureElement to specify the position where the cell should be placed.
Here is the code to implement an exception cell:
12345678910 // Exception cell (overwrite the formula collision)
@AnalyticsDetails.query: {
isCell: true,
cellReference: {
measureStructureElement: 'OccupationRate' ,
characteristicStructureElement: 'MonthDiff'
}
}
case when FlightYear = '0000' then MaximumSeats else null end as emptyCellIn our example, the exception cell position is defined using the intersecting elements OccupationRate and MonthDiff. We derive a null value when the FlightYear = 0000. Since we know that year 0000 is not part of the data, the condition doesn't return data.
Depending on the front-end tool, the empty cell might be displayed using difference symbols. In our case, we see a - symbol.
Here is the query result showing the cell exception:
In our case, we define emptyCell as the result because no value makes sense. But in some cases you might want to define a constant value, such as zero. As well as empty cell and defining constant values, it is also possible to use a special formula that should be used to override the value of a cell. This means you can calculate an individual cell value in a different way to the other cells.
Defining calculations by referencing cells
You can reference individual cells of the grid in a formula.
Using the scalar function GET_CELL_REFERENCE_VALUE you can retrieve the value of any cell in the grid. In the settings of the scalar function you choose the measure structure element and the characteristic structure element.
Often the new formula will replace an existing cell in the grid, but sometimes you might want to include the new formula in the result as an additional cell. In that case, you need to provide a place in the results for the new formula. To do this, we need to define a new member of the characteristic structure. You see in the code extract below, we have defined a new dummy element with the label % on Max Seats.
12345// dummy element to get space in the grid for exception cells
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: '% on Max Seats'
@AnalyticsDetails.query.onCharacteristicStructure: true
case when FlightYear = '0000' then 1 else null end as Dummy, Next, we need to define the formula ShareOccupied.
Here is the code to define the formula which references two existing cells of the result. We also see in this code where the calculation fits into the grid (the intersection of OccupiedSeats / Dummy):
12345678910111213141516171819
@AnalyticsDetails.query.isCell: true
abap.unit'%' as unitPercent,
@Aggregation.default: #FORMULA
@AnalyticsDetails.query: {
isCell : true,
cellReference : {
measureStructureElement : 'OccupiedSeats',
characteristicStructureElement : 'Dummy'
}
}
@AnalyticsDetails.query.decimals: 2
@Semantics.quantity.unitOfMeasure: 'unitPercent'
ratio_of( portion => GET_CELL_REFERENCE_VALUE( measure_struc_element => $projection.occupiedseats ,
characteristic_struc_element => $projection.Year2024 ),
total => GET_CELL_REFERENCE_VALUE( measure_struc_element => $projection.maximumseats ,
characteristic_struc_element => $projection.Year2024 ) ) * 100
as ShareOccupied,And here is the result:
Notice in our code we define the calculated element ShareOccupied by dividing the value of occupied seats / Year2024 by maximumseats / Year2024 and multiplying by 100, to give us the percentage value 71.19%.
If you want to reuse the result of a referenced cell in multiple formulas, instead or writing out the formula each time, it is better to define a reusable cell, as you can see in this code extract below. In our code, the reusable cell has the label Max2024. Since it should not be positioned in the grid we do not need to include the cell reference annotation. Instead, we must include the annotation @Consumption.hidden: true.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 // Measure Structure
@AnalyticsDetails.query.axis: #ROWS
MaximumSeats,
@AnalyticsDetails.query.axis: #ROWS
OccupiedSeats,
@AnalyticsDetails.query.axis: #ROWS
@EndUserText.label: 'Available Seats'
MaximumSeats - OccupiedSeats as FreeSeats,
// Characteristic Structure
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: '2024'
@AnalyticsDetails.query.onCharacteristicStructure: true
case when FlightYear = '2024' then 1 else null end as Year2024,
// dummy element to get space in the grid for exception cells
@AnalyticsDetails.query.axis: #COLUMNS
@EndUserText.label: '% on Max Seats'
@AnalyticsDetails.query.onCharacteristicStructure: true
case when FlightYear = '0000' then 1 else null end as Dummy,
// exception cells (in cells of column 'dummy')
// hidden cell for later reuse
@Aggregation.default: #FORMULA
@Consumption.hidden: true
@AnalyticsDetails.query.isCell : true
GET_CELL_REFERENCE_VALUE( measure_struc_element => $projection.maximumseats , characteristic_struc_element => $projection.Year2024 ) as Max2024,
@AnalyticsDetails.query.isCell: true
abap.unit'%' as unitPercent,
@Aggregation.default: #FORMULA
@AnalyticsDetails.query: {
isCell : true,
cellReference : {
measureStructureElement : 'OccupiedSeats',
characteristicStructureElement : 'Dummy'
}
}
@AnalyticsDetails.query.decimals: 2
@Semantics.quantity.unitOfMeasure: 'unitPercent'
ratio_of( portion => GET_CELL_REFERENCE_VALUE( measure_struc_element => $projection.occupiedseats ,
characteristic_struc_element => $projection.Year2024 ),
total => $projection.Max2024 ) * 100 as ShareOccupied,
@Aggregation.default: #FORMULA
@AnalyticsDetails.query: {
isCell : true,
cellReference : {
measureStructureElement : 'FreeSeats',
characteristicStructureElement : 'Dummy'
}
}
@AnalyticsDetails.query.decimals: 2
@Semantics.quantity.unitOfMeasure: 'unitPercent'
ratio_of( portion => GET_CELL_REFERENCE_VALUE( measure_struc_element => $projection.FreeSeats ,
characteristic_struc_element => $projection.Year2024 ),
total => $projection.Max2024 ) * 100 as ShareFree
}
Now we see how we define the referenced cell Max2024 that has been hidden, and then we use the reference cell in two formulas: ShareOccupied and ShareFree. Defining and reusing a referenced cell is more efficient than referring to the intersection for each formula.
Let's take a look at the final result:
Watch the video to learn how to create calculation from reference cells.
There are two more related use-cases that we could consider:
In our code we generated a new calculated measure ShareOccupied that uses the reusable cell Max2024. You could also use the ShareOccupied in other formulas of cells which are also hidden.
- Since we cannot define restricted measures within a formula, you should define these restricted measures as separate cells which are hidden. You then can use these cells in a formula.