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.
Consider Granularity When Creating Calculated Columns
In the figure, Consider Granularity when Creating Calculated Columns, 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
By analyzing your reporting requirements, you can arrive at a decision at which precise stage, the calculation should be performed.
CautionTry to minimize calculations before aggregation and always define calculations after aggregations to minimize the impact on performance.
Calculation Before Aggregation
When data is calculated at the correct level of granularity, the total sales measure is correctly calculated - 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.
