It is a common requirement in BI reporting to do certain calculations
“on the fly” at query run time. Sometimes these calculations have to be
based on query input parameters. Usually formulas and virtual
characteristics are used for calculating values dynamically. In this
post I will look at an example where we have to produce a calculation
for each report line, using query lines as an input parameter together
with query variable values.
Business Case
Let’s look at a business case where we are dealing with a vast volume of
customers signing up for service contracts with an organization. When
we analyze customer contracts one of the analysis objects in BI
reporting is customer’s age. As contracts usually last for several years
customer’s age has to be defined as of a certain date.
Let us imagine we want to build statistical reports for valid service
contracts. End user may wish to run these reports for any validity
periods, and customer’s age should be defined based on the start date of
the period selected at run time. With this approach the same customer
and same contract may be reported under a different customer age,
depending on the start date value.
Define virtual characteristic for customer’s age
The following objects are used in the code below:
- ZCUST – customer infoobject – a reference characteristic to 0BPARTNER with an attribute containing date of birth (Datebirth)
- ZAGE – Virtual characteristic populated with customer’s age at query run time. We should create a new infoobject ZAGE with type N length 3. We have to add this infoobject to the target infoprovider and reporting multiprovider (ZSOIMP). This object should have an initial value assigned in the corresponding transformation.
- ZREP_X - query that requires customer age calculation
- ZSOIMP- reporting multiprovider for the query
Populate internal variables at query run time
In the first step we have to populate an internal variable with the
value entered by end user in the query selections: Startdate ABAP
variable should be declared in the include ZXRSRTOP. The code below
ensures that the value for startdate entered by a user in the query
selections can be used further in the query result calculations (include
ZXRSZZZ).
To access code in the include use Transaction Code CMOD, Components for enhancement RSR00002, assigned to the project with Virtual Characteristics and Key Figures.
Include ZXRSRU01
* Populate start date entered in the variable for age calculation
IF I_STEP = 3.
LOOP AT i_t_var_range into wa_t_var_range
WHERE vnam = 'VAR_STDATE'.
startdate = wa_t_var_range-low.
ENDLOOP.
Include ZXRSRTOP
DATA: startdate type c Length 8.
Calculate customer’s age based on date of birth and start date
The following include (ZXRSRU02) is used for virtual characteristics
declarations. Customer (ZCUST) is declared below as an input parameter
and Age (ZAGE) as an output.
Include ZXRSRU02
* VC Declarations for Customer's age
l_s_chanm-chanm = 'ZCUST'. " Customer
l_s_chanm-mode = rrke_c_mode-read.
append l_s_chanm to e_t_chanm.
l_s_chanm-chanm = 'ZAGE'. " Customer's age
l_s_chanm-mode = rrke_c_mode-no_selection.
append l_s_chanm to e_t_chanm.
In the code below ZAGE virtual characteristic is populated with the
calculated age of the customer based on startdate and customer’s date of
birth defined in the 0BPARTNER characteristic.
It is extremely important to know that the code in this include
(ZXRSRZZZ) is executed for all queries. In order to address query
performance issues we have to check that the code is executed only for
those queries where age calculation is required.
Include ZXRSRZZZ
* Calculate Customer's age for Contract
IF i_s_rkb1d-COMPID = 'ZREP_X'. “Execute this only for query ZREP_X
assign component g_pos_ZSOIMP_ZAGE
of structure c_s_data to <age>.
assign component g_pos_ZSOIMP_ZCUST
of structure c_s_data to <customer>.
SELECT SINGLE DATEBIRTH into dob
FROM /BI0/PBPARTNER WHERE BPARTNER = <customer>.
* Calculate Age
IF dob < '18000101' OR startdate < dob.
a = 0.
ELSE.
a = startdate(4). a = a - DOB(4).
IF startdate+4(4) < DOB+4(4). a = a - 1. ENDIF.
ENDIF.
<age> = a.
ENDIF.
Solution Output
1. Selections
Startdate = 15 Jan 2000
1. Query Result
Customer A Age 20
Customer B Age 22
Customer C Age 32
Startdate = 15 Jan 2003
2. Query Result
Customer A Age 23
Customer B Age 25
Customer C Age 35
No comments:
Post a Comment