The TVARVC table serves very handy when we have to do a dynamic selection in BEx queries based on some condition which can change in future.
Usually we write customer exit variable in the query which is filled when the query is being executed.
This customer exit variable can take up any value depending upon the user requirement like current fiscal year or last 6 fiscal months based on the system date.
However there are some cases where the condition which populates the variable also can change.
For example, lets say there is a plan version for each data record, this plan version value can change next year.
Sample data record in the cube-
|Field1||Field2||Time||Plan Version (ZPVER)|
The BEx query based on this cube should show the data of the current plan version. Thus, if the user is executing the report according to PLAN_16, he/she should see the first record in the query.And if the query is executed while PLAN_17 is active, the second record should be displayed.
To make it more clear, many companies do planning based on planning versions. They change the plan version every year or every half-year depending upon their needs.When these users execute the BI reports, they want to see data according to the latest plan version.
Here TVARVC table helps. The users can store the plan version (like PLAN_17) in this table and the stored value is dynamically picked up by the customer exit variable at query run time.
Here are the step by step instructions to achieve this-
- In the query designer, for field Plan Version (Technical name ZPVER), define a characteristic value variable, lets say ZVAR – Set type of variable as ‘characteristic value’ and Processing by as ‘Customer exit’ and reference characteristic as ‘ZPVER’ (your characteristic technical name)
- In the ‘Details’ tab of the variable, Variable represents ‘Single value’ and Variable is ‘Mandatory’. Leave other settings as default.
- Drag this in the rows or column section as new selection in the query designer, save your query
- Logon to the BW system and go to SM30 transaction, type the table name as TVARVC and click on ‘Maintain’ or you can also go to transaction SE16/SE11.
- Add new entry in the table with below values-
Client – your client id
Variable name – give a suitable variable name e.g. Z_EXT_CURR_PLAN
Selection type – P
Number – leave as blank
INCL/EXCL – I
Option – EQ
Field Value – PLAN_17
Save your entry
- Now go to CMOD transaction.
Give the project name here and click on components. Double click on EXIT_SAPLRRS0_001, then double-click on ZXRSRU01.
- Go to change mode, and write the code as below-
* Get Current Year Plan from tvarvc table when 'ZVAR'.
clear tvarvc. select single * from tvarvc into tvarvc where name = 'Z_EXT_CURR_PLAN'. if sy-subrc eq 0. l_s_range-low = tvarvc-low. l_s_range-sign = 'I'. l_s_range-opt = 'EQ'. append l_s_range to e_t_range. endif.
- Save and activate the code
- Run the query, go to info button. You can see that the characteristic variable ZVAR is populated with value ‘PLAN_17’
Hence, if the plan version changes after some time to lets say PLAN_18, there is no need to change the query. The user or BW support team has to simply edit the value in TVARVC table in Production system and save the entry. The next time the same query is executed, it displays all the data based on PLAN_18 !!