Extending/Increasing Key figure Length with custom Domain

 

In this Post, We will learn how to increase Key figure length with new domain or custom Domain.

 

Go to RSD1 Tcode and enter NOHDB Tcode to go to create/change Info object mode.

Then Give new Key figure name and click create and enter Description for the new Key figure.

Once New keyfigure name is given we need to set Unit/Currency based on our requirement.

By Default, we will be having Curr Length 17 and decimal place 2. If we want custom length we need to create new Domain in SE11 Tcode.

After creating New Domain and activating it. We need to change the domain in our new key figure.

In change mode of new key figure, Click on the data element and got to change mode and click on change sub object icon.

Then give the new custom Domain or any standard domain name to change the Domain of the key figure. Then activate the key figure. Now we can see the new Domain which is assigned to the new Key figure.

Triggering Process chain through Program based on TVARVC Flag or DSO/ADSO Data

 

In this Post ,We will create a program which triggers a process chain when Flag from TVARVC is X or based on DSO/ADSO value.

 

Let us consider a scenario where we have TVARV flag Variable which we can change in STVARV T-code. Based on that flag we need to trigger.

So, we are reading that flag and trigger the process chain in the program using the below Function Module.

*&---------------------------------------------------------------------*
*& Report ZPC_TRIGGER_FLAG
*&---------------------------------------------------------------------*
*& (PC Trigger) Program
*$ Created on 07/11/2023
*&---------------------------------------------------------------------*
REPORT ZPC_TRIGGER_FLAG.

DATA: V_FLAG TYPE C.

SELECT SINGLE LOW FROM TVARVC INTO V_FLAG WHERE NAME = 'ZV_PC_TRG_FLG' .

IF ( V_FLAG = 'X' ).

  CALL FUNCTION 'RSPC_CHAIN_START'
  EXPORTING
    I_CHAIN             = 'ZPC_TRIGGER'.

  ENDIF.

 

 

Let us consider Another scenario where Process chain needs to be triggered if we have data loaded to any DSO/ADSO.

 

*&---------------------------------------------------------------------*
*& Report ZPC_TRIGGER_FLAG2
*&---------------------------------------------------------------------*
*&  (PC Trigger) Program
*$ Created on 07/11/2023 
*&---------------------------------------------------------------------*
REPORT ZPC_TRIGGER_FLAG2.

DATA: V_COUNT TYPE I.

SELECT COUNT(*) FROM /BIC/AZADSOX2 INTO V_COUNT WHERE /BIC/ZDATE = SY-DATUM.

IF ( V_COUNT > 0 ).

  CALL FUNCTION 'RSPC_CHAIN_START'
  EXPORTING
    I_CHAIN             = ‘ZPC_TRIGGER'.

  ENDIF.

Then after triggering the Program the process chain will gets triggered automatically.

RULE GROUP IN BW TRANSFORMATION

A rule group is a group of transformation rules. It contains one transformation rule for each key field of the target. A transformation can contain multiple rule groups.

Rule groups allow you to combine various rules. This means that for a characteristic, you can create different rules for different key figures.

Each transformation initially contains a standard group. Besides this standard group, you can create additional rule groups.

If you have defined a new rule in rule details, you can specify whether this rule is to be used as a reference rule for other rule groups.  If it is used as a reference rule, then this rule is also used in existing rule groups as a reference rule where no other rule has been defined.  

SCENARIO: Let's consider a scenario where we have multiple keyfigure(SALESQ1-4 in this case) and you need to bring each to new row based on the key(Calyear). Then we can create rule group for this scenario. Below is the source data and we are going to implement rule group and split each Store to 4 records.

Complete the logic for standard group

Create 3 more rule group for each Quarter data, activate the transformation , DTP and  execute the DTP.

Below is the target data we will get after loading 

Input parameter:

 https://sapbwonhanalearning.blogspot.com/2020/06/input-parameters-in-sap-hana.html

DTP in SAP BW WITH DYNAMIC FILTER:

https://sapbwonhanalearning.blogspot.com/2020/08/dtp-in-sap-bw-with-dynamic-filter.html

DTP in SAP BW WITH DYNAMIC FILTER (TVARVC VARIABLES)

 In this Post we learn how to create a dynamic filter DTP using TVARVC Variables

Let us consider a scenario where we need to set Filter for some plants in DTP. And these plants input can be editable. For this we can set variables in TVARVC which can be editable in Production system. 

For creating variables in TVARVC , GOTO STVARV Tcode

1. Select selection Option tab

2. Click on change mode

3. Create variables

Once the required Plants are set in TVARVC then goto DTP filter and select routine

And write the abap code wich fetch the data from TVARVC and add it to plant filter.

data: l_idx like sy-tabix,
      IT_TVARVC TYPE STANDARD TABLE OF TVARVC,
      WA_TVARVC type TVARVC,
      l_s_range like line of l_t_range.
          read table l_t_range with key
               fieldname = 'PLANT'.
SELECT * FROM TVARVC INTO CORRESPONDING FIELDS OF TABLE IT_TVARVC
  WHERE NAME = 'VAR_PLANT'.
loop at it_tvarvc into wa_tvarvc.
 If wa_tvarvc-NAME = 'VAR_PLANT'.
  l_s_range-iobjnm = 'PLANT'.
  l_s_range-fieldname ='PLANT'.
  l_s_range-sign = 'I'.
  l_s_range-option = 'EQ'.
  l_s_range-low = wa_tvarvc-low.

  append l_S_range TO l_T_range .
 ENDIF.
ENDLOOP.
          p_subrc = 0.

Then save,activate the DTP and execute the DTP.

The entries which we add in TVARVC variable will be available in DTP which is editable.

If we want to add plant range as filter then we need to slightly modify the code.Set the Range in TVARVC Variable.

 data: l_idx like sy-tabix,

      IT_TVARVC TYPE STANDARD TABLE OF TVARVC,
      WA_TVARVC type TVARVC,
      l_s_range like line of l_t_range.
          read table l_t_range with key
               fieldname = 'PLANT'.
SELECT * FROM TVARVC INTO CORRESPONDING FIELDS OF TABLE IT_TVARVC
  WHERE NAME = 'VAR_PLANT_RANGE'.
loop at it_tvarvc into wa_tvarvc.
 If wa_tvarvc-NAME = 'VAR_PLANT_RANGE'.
  l_s_range-iobjnm = 'PLANT'.
  l_s_range-fieldname ='PLANT'.
  l_s_range-sign = 'I'.
  l_s_range-option = 'BT'.
  l_s_range-low = wa_tvarvc-low.
  l_s_range-high = wa_tvarvc-high.

  append l_S_range TO l_T_range .
 ENDIF.
ENDLOOP.
          p_subrc = 0.

Then save,activate the DTP and execute the DTP.

UNION IN SAP HANA CALCULATION VIEW

Unions are used to combine the result set of two or more data sources.

Let us consider a scenario where we need combine two set of test details of the student. We have two tables , one with internal test details and another with final test. And we are going use union node to combine these two tables.(If you are having common records in two projection then we will get two records in union - Union in HANA act as UNION ALL function)

Below are the tables which we are going to combine.

Now drag Union node and add both projection in the union.

Click on the Auto Map By Name icon for mapping the source field names to target.

Similar to other nodes we can create calculated column, use input parameters and rename the field names.

After mapping the source fields, add all fields to the output and activate the view.

Now we can find the both data combined in the final data preview.

Note: If you are having same records with measure in two projection and we are using union node then we will get two records in union. The Measure value would be doubled. To avoid the doubling issue  what we can do is

1. Create calculated column CA_FLAG1 and CA_FLAG2 in each projection with 'X' in calculated column 

2. Add Aggregation node on top of the union and bring all fields

3. Then create another calculated for measure if CA_FLAG1='X' and CA_FLAG2 = 'X' then measure/2(handle if you are having 0 in measure)else same measure. This will solve our doubling issue in union node

SAP HANA Calculation view Rank node

In this Tutorial we learn how to rank our records and find the highest or lowest of the rank using Rank node in Calculation view.

 

Let us consider a scenario where we need to find the highest mark of a students where each student has taken 3 Test each. Now we use Rank node to display the highest mark  of the student

 

Below is the table which has the test details of the student.

Now creating a Calculation view with the table.

Drag the Rank node and place between Aggregation and Projection.

For our scenario I am going to select sort direction as descending (To pick the top mark) and “order by” should be your field which you wish to rank and get highest or lowest. In our case I am selecting Mark Field. We need mention student field as “Partition by” because it is going to be field which is going to be sort mark based on student.If we Enable Generate Rank column we will get another Rank field which will show the rank of the mark

Now it ll show the student’s mark which is highest of the three exam they have undergone. Below is the final result with Rank

 

Input Parameters in SAP HANA

Input Parameters are used to Filter the data dynamically in any node of the calculation view and also perform calculation based on the dynamic input from the user.

Let us consider a scenario where we need to restrict the CALMONTH (i.e user will define which CALMONTH data should display at the run time). And also the Pass Mark will be decided by the user at the run time of the calculation view.

Please find the below sample data we have without implementing Input parameter.

Now goto Parameter/Variables table in semantics. Click the ‘+’ sign and select create Input parameter.

Input parameter for pass mark:

Input parameter for CALMONTH filter:

When can also mention whether Input parameter whether it is going to be mandatory or not in the new input parameter creation window.

Crating new calculated column to filter on calmonth data

Filtering on calmonth in projection 1

Calculated column for pass/fail status based on passmark_ip

Now activate the Hana view and give display preview in semantics

Below is the output of the view

DIFFERENCE BETWEEN INPUT PARAMETER AND VARIABLE IN SAP HANA CALCULATION VIEW:

• INPUT PARAMETER is used to do any calculation or filter on any node based on the dynamic input given where as the Variables is used to do dynamic filter on the final result of the hana view
• We can set Data type of the Input parameter where as variable is bound to attribute of the view

We will see about usage of RANK Node in the next post