Skip to Content

How to pass value to a filter variable in a query used in APD

Requirement :

1) A planner works on a workbook to generate forecast data. Once the planning is completed, the planner needs to send the file to the BI application server, from where any target system can read the file.

2) The file to be generated should pick up the forecast version which the planner has currently finalized & published.


An APD (Analysis Process Designer) is created with “Forecast Query” ( Query based on the plan cube) as a source and the File as a target. The filter variable in the query determines the Forecast Version to be sent to the application server.

Two Approaches :

Approach 1:

The Forecast version variable can be made input ready and a user variant can be created. The user can save the selections in this variant & change it before the APD run is triggered via a prior scheduled job.

The only disadvantage is the planner needs to additionally ensure the variant is maintained before the scheduled job runs.

Approach 1.JPG

Approach 2 :

Store the forecast version in a table (e.g.  TVARVC or a custom table) by passing the version from the workbook. This can be achieved by creating a button which triggers a custom planning sequence. The FOX formula can be used to save the value from the workbook into the TVARVC table. Then trigger an event using the function module  “BP_EVENT_RAISE” which will trigger the process chain containing the APD.

In case of non-planning ( non BI IP) scenarios, we can populate the TVARVC variable by a customer exit or class.

i) Create the variable to be populated in TVARVC table.

Approach 2.1.JPG

ii) Create a customer exit variable in the Query to be used in the APD.

Approach 2.2.JPG

iii) Write the select code in CMOD or the function module for this customer exit variable



        INTO lv_leyr


                TYPE = ‘P’ AND

                NUMB = ‘1’.


iv) Create the APD

Approach 2.3.JPG

v) Create an Event via SM64 tcode & add the APD in the process chain with Start Variant as “After an event”

Approach 2.4.JPG

Now when the query is executed in the APD via the process chain, the customer exit will read the value from TVARVC table and restrict the filter accordingly.

Thus we have achieved a single maintenance of the filter variable value & event based triggering of the file generation so that the file will be generated as soon as the planner finalizes the version.

1 Comment
You must be Logged on to comment or reply to a post.