It is possible to create additional calculated columns in any type of calculation view. Calculated columns are generated on the fly at runtime and do not persist the result.
An example of a calculated column might be as follows: you have two columns containing the first and last name of the customer, but you would like to combine the first and last name in a single column. You can do this by creating a calculated column based on a string function. Alternatively, you could use an 'If / then / else' expression to check the value of a column and decide what to do if it is empty, perhaps by providing a fixed value 'Other' if it is empty.
The calculation can be arithmetic using measures, or a character string manipulation using attributes. You can even use this feature to generate a simple, constant value when one data source does not provide a column that is required. For example, you know a table only contains data for year 2022. However, there is no column for that year, and that column is needed in a join; you could generate a calculated column using a constant value '2022'.
It is possible to nest calculated columns, so that one calculated column is based on other calculated columns.
When to Use Calculated Columns
When you include calculations in your calculation views using calculated columns, you take advantage of the speed of SAP HANA Cloud by letting the database engine perform calculations instead of doing these calculations in your client reporting tool. So it is recommended to define calculations within the calculation view and not in the reporting front end tool.
Having ready-made calculations in calculation views can also help simplify reporting by re-using calculations in calculation views and not having to define the calculation in each report. Besides, you can also hide complex and/or sensitive calculations by defining them in the calculation view. For example, (If 'Employee Region' = "South" and 'Manager' = "Roland" then 'New Salary' = 'Current Salary' - 8%).
Defining a Calculated Column
When defining a calculated column, the first step is to choose the column type, measure, or attribute.
- A calculated column is defined within an information model and can use the string functions, mathematical functions etc. available in the editor.
- You can define a calculated column as measure or attribute.
- Double-click or drag-and-drop Elements, Operators and Functions to build the expression.
- When you type, use Ctrl+Space to autocomplete the name of functions, columns, and input parameters.
Consider Granularity When Creating Calculated Columns
For certain measures, it is not possible to perform the calculations when the measures are already aggregated. The aggregated granularity of, for example, Price does not mean anything.
In the figure, in the sum line highlighted in red, the units as well as the price have been aggregated. Multiplying these to aggregates does not give a meaningful result.
Triggering Calculations at the Right Level
For measures where calculation needs to be done before aggregation, you need to be wary of the different stages of your calculation view.
Ensure that you create the calculated column in a node prior to where aggregation is performed.
By analyzing your reporting requirements, you can arrive at a decision at which precise stage, the calculation should be performed.
Caution
Try to minimize calculations before aggregation and always define calculations after aggregations to minimize the impact on performance.
Calculation Before Aggregation
•
When defining calculation on the right level of aggregation you end up with a correct sum as the calculations are performed on the correct granular level.
When data is calculated at the correct level of granularity, the total sales measure is correctly calculated from units and prices- that is, 98,750.
Client Side Aggregation
Watch this video to learn about the Client Side Aggregation.
Consider an example where you have created the measure MAXIMUM_GROSS_AMOUNT, giving you the maximum value of the gross amount. By selecting the Enable client side aggregation checkbox, you propose to the reporting client to also apply a maximum aggregation on the client side as defined in the Client Aggregation dropdown list.