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

VARIABLES IN SAP HANA CALCULATION VEW

Variables are the dynamic filters which needs to be applied on the final result (i.e. User can provide the filter during the runtime of the execution).

HOW TO CREATE VARIABLES

To Create variables click on the Semantics and go to Parameter/Variable tab and click on the PLUS Sign.

Variable creation

In the new variable creation widow, we need to give the Variable name, Label(Description) and the attribute  the field where the dynamic filter going to be applied and the selection type needs to be mentioned

• Single Value
• Interval
• Range

We can also mention whether multiple entries are allowed for this variable and whether this variable is mandatory based on the requirement.

For our understanding I have created HANA View on top below table with variable on Plant.

Data without variable

    Entering PLANT = 1000,2000 as variable

Now records with plant 1000 and 2000 alone got filtered.

We will see about creation of input parameters and difference between variables and input parameters in SAP HANA in next post.

Compounding in SAP BW

A compound attribute differentiates a characteristic to make the characteristic uniquely identifiable.

Let's say that you have a manufacturing company and you have manufacturing plants in different country(India,USA,Australia,China) and in each country you have 5 storage location for these plant(10,20,30,40,50). Then it is meaningless to Just mention Storage location(10,20,30,40,50) without plant in Report or KPI. Because storage location 10 will be available in all countries and it makes no sense by just mentioning storage location alone in report. So we need to compound Plant with storage location.

In storage location Info object compounding tab we need to include plant as the superior info object(Plant). Once Plant is compounded with storage location whenever we use Storage location info object in creating  DSO, Infocube or BEX Query, automatically Plant info object will be added to the respective Objects.

The Compounding Infoobject Acts as a compounding Primary Key at the Master Data Table.

InfoObjects in SAP BW

InfoObject is the smallest building block. You need to use InfoObjects to design or configure other SAP BW objects such as InfoCubes, DataStore objects (DSOs), MultiProviders, queries, InfoSets, and so on.  

InfoObjects can be classified into the following types:

• Characteristics (for example, customers)
• Key figures (for example, revenue)
• Units (for example, currency, amount unit)
• Time characteristics (for example, fiscal year)
• Technical characteristics (for example, request number)

Characteristic:

• This type of InfoObject represents business entities that are the subjects of analysis, for example, product, customer, and marketing regions.

Key figure

• This type of InfoObject represents numeric measures of business entities, for example, weight, number, quantity, and amount.

 Time characteristic

• This type of InfoObject represents the period of the transaction between entities, for example, date of billing, month of sale, and fiscal year. Time characteristics are delivered by SAP and aren’t customizable.

Unit

• This type of InfoObject represents the unit of measure for the numeric measures of business entities, for example, the currency, unit of weight, unit of volume, and so on.

Technical characteristic

• This type of InfoObject represents entities that are internal to SAP BW and are technical in type, for example, a data load request ID. (The data load request ID helps in the maintenance and administration of the SAP BW system.) Technical characteristics are delivered by SAP and aren’t customizable.