Calculated column in SAP HANA

If you have made it this far into the tutorial, I commend your will to continue striving to be the best in SAP HANA. This website will continue to focus on teaching you everything that you need to know to be an excellent SAP HANA consultant. Moving on, this tutorial will help you understand how to create new fields in views which are calculated based on some logic which may involve already existing fields in the table. Such a field is called Calculated Column in SAP HANA.

Let’s build a Calculated Column with an example

To start, open the calculation view we are working on and click on Projection_1 node. Let’s create a new field here that would be called SALE_TYPE and the logic would be to concatenate fields AUART and WAERK separated by a dash symbol. Right click on the “Calculated Columns” folder and click “New”.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

The below dialog box opens up where you need to give the new field a technical name, a datatype and the logic inside the “Expression Editor”.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

After adding the name and datatype as shown below, double click on the AUART field to add it to the “Expression Editor”.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

It gets added as shown below.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW



1

Concatenate more strings to it using the + operator. The Plus (+) operator works as a string concatenator for string fields and an arithmetic plus for numerical datatypes like decimal and integers. We needed the AUART and WAERK to be separated by a dash. Hence after the plus symbol, we add a dash surrounded by single quotes since it is a character value. Then add another plus for the next field WAERK. The end result should resemble the below. Double click WAERK to add it at the end too.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

Once the expression is complete as below, press the “Validate Syntax” to verify the syntax of your code.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

The below information message confirms that the code is correct.

Press OK and save and you would come out of this editor. Now as you see below, a new Calculated column SALE_TYPE has been created successfully.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

Now, as you might have realized, this field is only available in Projection_1. It needs to be propagated to all nodes above it. To do this, click on the node above it which is the Join_1 node in our case. Find the field and right click on it and select “Propagate to Semantics” from the context menu.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

You get an information message outlining the nodes to which this field has been propagated to. Press OK.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

Activate the view and run a data preview. As seen below, our SALE_TYPE field has appeared and seems to be running as per our calculation logic.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

Let’s try another one. This time let’s create a new calculated column at the “Aggregation Node” for a numerical field- NETWR. The new requirement is to create a field NORM_VAL which would be equal to NETWR divided by 12 showing up to 2 decimal places. Once again, right click on the “Calculated Columns” folder and click on “New Calculated Column” from the context menu.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW



A new window pops up where we fill the details as we before. The name, A description and datatype need to be filled. Since the value when divided by 12 will surely return decimal values, we mark Datatype as Decimal with a maximum length of 10 before the decimal. A scale of 2 tells the system to only display the result up to 2 decimal places. Also, this time, make sure you switch the Column type to “Measure” since this is a transaction field. If you don’t remember what a measure is and how it is different from an attribute, revisit the Master data vs Transaction data tutorial. In the expression editor, enter the formula as NETWR/12 as required and press OK.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

As seen below, the new field has been created successfully.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

Since this is on the last aggregation node, there is no need to propagate it further. Save and activate this view. Press data preview to check the values as shown below.

SAP HANA CALCULATED COLUMN CALCULATION VIEW GRAPHICAL VIEW

This concludes our tutorial on SAP HANA Calculated columns. Hope you guys found it interesting. Comment below with your thoughts.

Please show your support for this website by sharing these tutorials on social media by using the share buttons below. Stay tuned for the next ones.

Happy learning!

<<Previous Tutorial                                                                                                              Next Tutorial>>

Content Protection by DMCA.com
Tagged , .

8 Comments

    • Yes you can but that only adds the field into the particular node. Propagate to semantics enables the field from the node where it is clicked in to the final semantics node. Saves a lot of time if you need that field in the final output.

      • Thanks for the explanation. This is really very helpful and thanks again for wonderful work done . Request you to add hierarchies and analytic privilege topic also.

  1. Hi,

    Can newly created column set to MAX/MIN functionality in HANA view.

    In my case i have created a calculated column and that HANA view is being used in BW .
    composite provider is built on top of the hana view. It is summing up the calculated column value because for same PO number there are 2 entries in HANA view.
    As BI property it just sum up the key figures/measures for same characteristics.

  2. Hi Shyam,

    Have a doubt, in terms of Performance is it better to add Calculated columns at the projection level or aggregate node?

    • Depends. You can add them at the final aggregation node. Or else if you need that calculation somewhere in between also then add it in between.

  3. Hi Shyam,

    I have recently started working on the concepts of HANA. I wanted to have the aggregate of qty at Schedule line level,for the particular Order, Item.

    For example below is my data preview.

    Order Item Schedule line no Qty
    123 10 1 1
    123 10 2 1
    123 20 1 2
    123 20 2 3
    456 10 1 1
    456 10 2 1

    I wanted the output as below
    Order Item qty
    123 10 2
    123 20 5
    456 10 1

    Can it be done in the Analytical views?

    This is because I wanted to make use of this analytical view in calculation view for other calculations.

    Could you please share your ideas on this?

    Thank you.

    Best regards,
    Karthik.

Leave a Reply