Skip to Content
Technical Articles

SAP HANA Calculation View – SQL Script

In this blog, I’m going explain how to create SAP HANA Calculation View using SQL Code. i.e. SQL Scripted Calculation View.

In general we create Script-based calculation views to depict complex calculation scenarios by writing SQL script statements. It is a viable alternative to depict complex business scenarios, which you cannot achieve by creating other information views (Attribute, Analytical, and Graphical Calculation views).

Generally we can create Calculation Views in two ways like…

GUI

SQL Script

To explain one simple scenario, I tool MARA, MAKT, KNA1, VBAK and VBAP tables. Using all these tables, I will create a SQL Scripted Calculation View. It will not contain any additional calculation, it just displays OUTPUT without taking any INPUT parameters.

i.e. It contains only OUT parameter, and no IN parameter.

I selected few fields from above all tables and JOINED all above tables using Left Outer Join.

STEP: 1

Create a Calculation View i.e. Type is SQL Script.

Under Columns, create all OUTPUT fields which are required to display in Output.

See the below screenshot, once I created all fields, it looks like below.

Note: We can create additional fields under columns, and we can calculate them using SQL Code.

STEP: 2

Write SQL Code to get all above fields form different tables and display final OUTPUT.

In the below code, I just joined all tables using Left Outer Join, this is just to explain simple scenario.

STEP: 3

Check the Semantics and see all fields.

STEP: 4

Save & Activate Calculation View.

STEP: 5

Display the data.

In upcoming blogs, I will explain the Procedures, Functions and also extend the Calculation View with Parameters and additional fields under Columns.

4 Comments
You must be Logged on to comment or reply to a post.
  • Thank you for clear explanation.

    Have a question that in Future, does the  creation of Calcuation view using Graphical view will become obselete alike AV and ANV views

    Thanks,Shyamala

  • Hello Shyamala,

    Might be possible or still SAP keeps it until all customers migrate to new technology because it helps to support the existing developments. Eventually we need to walk towards current technology if we want to use new features.

    We are getting new releases, and few customers are going for new and few customers are still using their older versions, I know it is all in confusion.

    It is good to prepare for future, so I advice, it is always good to update our skills as per current trend.

    See below link, I have given some more information on your question.

    https://blogs.sap.com/2019/02/03/table-functions-in-sap-hana/comment-page-1/#comment-449918

     

    -Surendra

    • Hi Surendra,

      I need some help, because in my script CV I need use other view , but I couldn’t do it well because the base graphical view has parameter input.

      I created the parametrer and used, but didn’t work.

      Really I need some help with this, please.

       

      Sincerely,

      Ana

       

      /********* Begin Procedure Script ************/
      BEGIN

      var_out = SELECT “PROMO_OFFER_ID”
      , COUNT (DISTINCT (CASE WHEN “DIA_SEM” BETWEEN 5 AND 7 THEN “DATE_SQL” END)) AS “DIAS_FDS”

      FROM
      (
      SELECT P.”PROMO_OFFER_ID”
      , P.”PROMO_OFFER_START_DT”
      , P.”PROMO_OFFER_END_DT”
      , C.”DATE_SQL”
      , C.”DAY_OF_WEEK_INT” + 1 as “DIA_SEM”

      FROM “_SYS_BIC”.”VIEW_PROMOTIONS.FUENTES/FTE_PROMO_OFFER”
      (‘PLACEHOLDER’ = (‘$$Fecha_Fact$$’, ‘$$Date$$’)) as P
      JOIN “_SYS_BIC”.”VIEW_PROMOTIONS/DIM_CALENDARIO” as C
      ON C.”DATE_SQL” BETWEEN P.”PROMO_OFFER_START_DT” AND P.”PROMO_OFFER_END_DT”

      ) as T

      GROUP BY “PROMO_OFFER_ID”
      , “PROMO_OFFER_START_DT”
      , “PROMO_OFFER_END_DT”

      ;

      END /********* End Procedure Script ************/