To facilitate the end users to view the report dynamically based on two different fiscal year variants without any data redundancy.
Business Requirement: From October 2010 onwards client has changed their current Fiscal year variant
from JAN-DEC to OCT- SEP. So, the requirement is to facilitate the end users to view the report dynamically
based on two different fiscal year variants (J-D and O-S ) without any data redundancy.
As a result of this, 2010 year will have shortened fiscal periods (only 9).
Assumption: It is being assumed that the user knows that K1 variant is for Jan to Dec, K2 Variant is for
Oct to Sep.
Let us consider user wants to see data for Calendar month October 2010.
User will has the choice to enter the selection in either of the below two ways
Variant JD – Period 010.2010
Variant OS – Period 001.2011
Concept:
· Acquire the fiscal definition (J-D /O-S) and period information from user through input selection screen.
· With the help of mapping table Manipulate the given period in to equivalent period of underline table
Example – If user input as 0012009 we needs to convert in to 0102008).
For this we need to:
- Create new table to maintain the look up information.
- Analyze each and every existing report and customize it.
- Report Customization includes code enhancement in RSR00001 and RSR00002 component.
Purpose of multiprovider is to enhance the structure of cube with virtual fields without disturbing the existing
cube.
Report Level Approach Back End Design:
Shows how report request reach to cube:
Shows how data retrieve from cube:
Complete Report execution flow:
Customer Exit:
Code enhancement in RSR00001:
****************************Begin of FYCP code ***************************************
DATA: it_mtable TYPE STANDARD TABLE OF /bic/azfiscmt00, (This is the table where lookup information i.e. mapping between two fiscal year is maintained which is stored in DSO)
wa_mtable TYPE /bic/azfiscmt00.
********* Determine equivalent fiscal period value from given fiscal period***********
WHEN ‘ZFYCP1’.
CLEAR loc_var_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP13’.
********** Getting Year value from user given low value input parameter***************
CLEAR l_year1.
l_year1 = loc_var_range-high(4).
************** Get Fiscal Period value from mapping table DSO*************************
SELECT * FROM /bic/azfiscmt00 INTO TABLE it_mtable
WHERE fiscper EQ loc_var_range-high
OR fiscper EQ loc_var_range-low.
IF sy-subrc = 0.
CLEAR loc_var_range.
******************** Get the fiscal definition either JD/OS***************************
READ TABLE i_t_var_range INTO loc_var_range
WITH KEY vnam = ‘ZFYCP11’.
flag = loc_var_range-low.
* ZCL_IM_FYCP_VIRT_PERIOD=>flag = loc_var_range-low.
******** If definition is OS select appropriate fiscal period from mapping table******
IF loc_var_range-low = ‘OS’.
SORT it_mtable BY /bic/zfiscper3.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscper3.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1.
l_s_range-low = wa_mtable-/bic/zfiscper3.
l_s_range-high = wa_mtable-/bic/zfiscper3.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscper3.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
******* If definition is JD select appropriate fiscal period from mapping table*******
ELSEIF loc_var_range-low = ‘JD’.
SORT it_mtable BY /bic/zfiscper2.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscper2.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1
l_s_range-low = wa_mtable-/bic/zfiscper2.
l_s_range-high = wa_mtable-/bic/zfiscper2.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscper2.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
ENDIF.
ENDIF.
ENDLOOP.
********** Determine equivalent Fiscal Week from given fiscal week value**************
WHEN ‘ZFYCP15’.
CLEAR loc_var_range
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP14’.
********** Getting Year value from user given low value input parameter***************
CLEAR l_year1.
l_year1 = loc_var_range-high(4).
******************** Get Fiscal Week value from Mapping table DSO*********************
SELECT * FROM /bic/azfiscmt00 INTO TABLE it_mtable
WHERE /bic/zfiscweek EQ loc_var_range-high
OR /bic/zfiscweek EQ loc_var_range-low.
IF sy-subrc = 0.
CLEAR loc_var_range.
********************* Read Fiscal definition from user input**************************
READ TABLE i_t_var_range INTO loc_var_range
WITH KEY vnam = ‘ZFYCP11’.
flag = loc_var_range-low.
* ZCL_IM_FYCP_VIRT_PERIOD=>flag = loc_var_range-low.
********If Fiscal definition id OS select appropriate value from mapping table********
IF loc_var_range-low = ‘OS’.
SORT it_mtable BY /bic/zfiscwek1 /bic/zfiscper3.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscwek1 /bic/zfiscper3.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1.
l_s_range-low = wa_mtable-/bic/zfiscwek1.
l_s_range-high = wa_mtable-/bic/zfiscwek1.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscwek1.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
*******If Fiscal definition is JD select appropriate value from mapping table*********
ELSEIF loc_var_range-low = ‘JD’.
SORT it_mtable BY /bic/zfiscwek2 /bic/zfiscper2.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscwek2 /bic/zfiscper2.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1.
l_s_range-low = wa_mtable-/bic/zfiscwek2.
l_s_range-high = wa_mtable-/bic/zfiscwek2.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscwek2.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
ENDIF.
ENDIF.
ENDLOOP.
************* Determine fiscal period value to calculate MTD value*******************
WHEN ‘ZFYCP2’.
CLEAR loc_var_range.
***************** Check Fiscal period value is available or not***********************
CLEAR loc_var_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP1’.
ENDLOOP.
IF loc_var_range IS INITIAL.
**** Determine equivalent MTD value fiscal period from Fiscal week*****
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP15’.
CLEAR l_year.
CLEAR l_week.
l_year = loc_var_range-high(4).
l_week = loc_var_range-high+4(3).
IF l_year LE l_year1.
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘PW’
AND bdatj = l_year
AND poper = l_week
AND reljr = ‘0’.
IF sy-subrc NE 0.
l_year = l_year – 1.
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘P1’
AND bdatj = l_year
AND poper = l_week
AND reljr = ‘+1’.
ENDIF.
CONCATENATE l_year t009b-bumon t009b-butag INTO l_endofcalday_date.
ELSEIF l_year GT l_year1.
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘PW’
AND bdatj = l_year1
AND poper = l_week
AND reljr = ‘+1’.
CONCATENATE l_year1 t009b-bumon t009b-butag INTO l_endofcalday_date.
ENDIF.
CLEAR l_year.
CLEAR l_week.
CALL FUNCTION ‘DATE_TO_PERIOD_CONVERT’
EXPORTING
i_date = l_endofcalday_date
i_monmit = 00
i_periv = ‘P1’
IMPORTING
e_buper = l_week
e_gjahr = l_year
EXCEPTIONS
input_false = 1
t009_notfound = 2
t009b_notfound = 3
OTHERS = 4.
CONCATENATE l_year l_week INTO l_fiscper.
CLEAR ls_rngstrng.
CLEAR l_s_range.
ls_rngstrng-sign = ‘I’.
ls_rngstrng-opt = ‘EQ’.
ls_rngstrng-low = l_fiscper.
l_s_range = ls_rngstrng.
APPEND l_s_range TO e_t_range.
ENDLOOP.
ELSE.
*********** Determine equivalent MTD value fiscal period from Fiscal Period***********
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP1’.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
l_s_range-low = loc_var_range-high.
APPEND l_s_range TO e_t_range.
ENDLOOP.
ENDIF.
****************** Determine Fiscal week value to calculate YTD Value*****************
WHEN ‘ZFYCP3’.
************Check user given Fiscal Period value is available or not******************
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP1’.
ENDLOOP.
IF loc_var_range IS INITIAL.
CLEAR: l_year.
CLEAR: l_fiscper3.
CLEAR:l_fiscper.
CLEAR ls_rngstrng.
CLEAR l_s_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP15’.
***********Determine YTD fiscal week value from user given Fiscal week****************
l_fiscper = loc_var_range-high.
* l_year = loc_var_range-low(4).
CONCATENATE l_year1 ‘001’ INTO l_fiscper3 .
SELECT * FROM /bic/azfiscmt00
INTO wa_mtable
WHERE /bic/zfiscweek = l_fiscper3.
ENDSELECT.
CLEAR loc_var_range.
READ TABLE i_t_var_range INTO loc_var_range
WITH KEY vnam = ‘ZFYCP11’.
IF loc_var_range-low = ‘OS’.
ls_rngstrng-low = wa_mtable-/bic/zfiscwek1.
ELSEIF loc_var_range-low = ‘JD’.
ls_rngstrng-low = wa_mtable-/bic/zfiscwek2.
ENDIF.
ls_rngstrng-sign = ‘I’.
ls_rngstrng-opt = ‘EQ’.
ls_rngstrng-high = l_fiscper.
l_s_range = ls_rngstrng.
APPEND l_s_range TO e_t_range.
ENDLOOP.
ELSE.
************ Determine YTD fiscal week value from user given Fiscal Period************
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = ‘ZFYCP1’.
CLEAR l_year.
CLEAR l_fiscper.
l_year = loc_var_range-high(4).
l_period = loc_var_range-high+4(3).
IF l_year LE l_year1.
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘P1’
AND bdatj = l_year
AND poper = l_period
AND reljr = ‘0’.
IF sy-subrc NE 0.
l_year = l_year – 1.
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘P1’
AND bdatj = l_year
AND poper = l_period
AND reljr = ‘+1’.
ENDIF.
CONCATENATE l_year t009b-bumon t009b-butag INTO l_endofcalday_date.
ELSEIF l_year GT l_year1 .
SELECT SINGLE bumon butag FROM t009b
INTO (t009b-bumon, t009b-butag)
WHERE periv = ‘P1’
AND bdatj = l_year1
AND poper = l_period
AND reljr = ‘+1’.
CONCATENATE l_year1 t009b-bumon t009b-butag INTO l_endofcalday_date.
ENDIF.
CLEAR l_year.
CLEAR l_period.
CLEAR l_fiscper.
CLEAR l_week.
CLEAR l_fiscper2.
CALL FUNCTION ‘DATE_TO_PERIOD_CONVERT’
EXPORTING
i_date = l_endofcalday_date
i_monmit = 00
i_periv = ‘PW’
IMPORTING
e_buper = l_week
e_gjahr = l_year
EXCEPTIONS
input_false = 1
t009_notfound = 2
t009b_notfound = 3
OTHERS = 4.
CONCATENATE l_year l_week INTO l_fiscper.
CLEAR ls_rngstrng.
CLEAR l_s_range.
CONCATENATE l_year1 ‘001’ INTO l_fiscper3 .
SELECT * FROM /bic/azfiscmt00
INTO wa_mtable
WHERE /bic/zfiscweek = l_fiscper3.
ENDSELECT.
CLEAR loc_var_range.
READ TABLE i_t_var_range INTO loc_var_range
WITH KEY vnam = ‘ZFYCP11’.
IF loc_var_range-low = ‘OS’.
ls_rngstrng-low = wa_mtable-/bic/zfiscwek1.
ELSEIF loc_var_range-low = ‘JD’.
ls_rngstrng-low = wa_mtable-/bic/zfiscwek2.
ENDIF.
ls_rngstrng-sign = ‘I’.
ls_rngstrng-opt = ‘EQ’.
ls_rngstrng-high = l_fiscper.
l_s_range = ls_rngstrng.
APPEND l_s_range TO e_t_range.
ENDLOOP.
ENDIF.
********* Determine appropriate selection parameter based on fiscal definition********
WHEN ‘ZFYCP21’.
CLEAR loc_var_range.
READ TABLE i_t_var_range INTO loc_var_range WITH
KEY vnam = ‘ZFYCP22’.
IF loc_var_range IS INITIAL.
READ TABLE i_t_var_range INTO loc_var_range WITH
KEY vnam = ‘ZFYCP13’.
IF NOT loc_var_range IS INITIAL.
***********Get fiscal year value from mapping table DSO using fiscal period***********
SELECT * FROM /bic/azfiscmt00 INTO TABLE it_mtable
WHERE fiscper EQ loc_var_range-high
OR fiscper EQ loc_var_range-low.
ELSE.
READ TABLE i_t_var_range INTO loc_var_range WITH
KEY vnam = ‘ZFYCP14’ .
IF NOT loc_var_range IS INITIAL.
*********** Get fiscal year value from mapping table DSO using fiscal week************
SELECT * FROM /bic/azfiscmt00 INTO TABLE it_mtable
WHERE /bic/zfiscweek EQ loc_var_range-high
OR /bic/zfiscweek EQ loc_var_range-low.
ENDIF.
ENDIF.
ELSE.
************ Get fiscal year value from mapping table DSO using fiscal year **********
SELECT * FROM /bic/azfiscmt00 INTO TABLE it_mtable
WHERE fiscyear EQ loc_var_range-high
OR fiscyear EQ loc_var_range-low.
ENDIF.
IF NOT it_mtable[] IS INITIAL.
CLEAR loc_var_range.
************************* Get the fiscal definition either JD/OS**********************
READ TABLE i_t_var_range INTO loc_var_range
WITH KEY vnam = ‘ZFYCP11’.
flag = loc_var_range-low.
******* If definition is OS select appropriate fiscal period from maping table********
IF loc_var_range-low = ‘OS’.
SORT it_mtable BY /bic/zfiscyear.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscyear.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1.
l_s_range-low = wa_mtable-/bic/zfiscyear.
l_s_range-high = wa_mtable-/bic/zfiscyear.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscyear.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
******* If definition is JD select appropriate fiscal period from maping table********
ELSEIF loc_var_range-low = ‘JD’.
SORT it_mtable BY /bic/zfiscyr1.
DELETE ADJACENT DUPLICATES FROM it_mtable
COMPARING /bic/zfiscyr1.
LOOP AT it_mtable INTO wa_mtable .
IF sy-tabix = 1.
l_s_range-low = wa_mtable-/bic/zfiscyr1.
l_s_range-high = wa_mtable-/bic/zfiscyr1.
ELSEIF sy-tabix = 2.
CLEAR l_s_range-high .
l_s_range-high = wa_mtable-/bic/zfiscyr1.
ENDIF.
ENDLOOP.
l_s_range-sign = ‘I’.
l_s_range-opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
ENDIF.
ENDIF.
************************ End Of FYCP Code*********************************************
Code enhancement in RSR00002:
If i_s_rkb1d-infocube cp ‘ZCOPA_M02’.
* break-point.
********************** Populate value for virtual fiscal period***********************
READ TABLE i_th_chanm_used
WITH KEY TABLE_LINE = ‘ZFISCPER3’
TRANSPORTING NO FIELDS.
IF SY-SUBRC = 0. “only if virtual characteristic is used in query
l_s_chanm-chanm = ‘0FISCPER’.
l_s_chanm-mode = rrke_c_mode-read.
append l_s_chanm to e_t_chanm.
l_s_chanm-chanm = ‘ZFISCPER3’.
l_s_chanm-mode = RRKE_C_MODE-NO_SELECTION.
append l_s_chanm to e_t_chanm.
ENDIF.
********************** Populate value for virtual fiscalweek *************************
READ TABLE i_th_chanm_used
WITH KEY TABLE_LINE = ‘ZFISCWEK1’
TRANSPORTING NO FIELDS.
IF SY-SUBRC = 0. “only if virtual characteristic is used in query
l_s_chanm-chanm = ‘ZFISCWEEK’.
l_s_chanm-mode = rrke_c_mode-read.
append l_s_chanm to e_t_chanm.
l_s_chanm-chanm = ‘ZFISCWEK1’.
l_s_chanm-mode = RRKE_C_MODE-NO_SELECTION.
append l_s_chanm to e_t_chanm.
ENDIF.
********************** Populate value for virtual fiscal year*************************
READ TABLE i_th_chanm_used
WITH KEY TABLE_LINE = ‘ZFISCYEAR’
TRANSPORTING NO FIELDS.
IF SY-SUBRC = 0. “only if virtual characteristic is used in query
l_s_chanm-chanm = ‘0FISCPER’.
l_s_chanm-mode = rrke_c_mode-read.
append l_s_chanm to e_t_chanm.
l_s_chanm-chanm = ‘ZFISCYEAR’.
l_s_chanm-mode = RRKE_C_MODE-NO_SELECTION.
append l_s_chanm to e_t_chanm.
ENDIF.
ENDIF.
*****************************End of Customer Exit ************************************
Customize the existing report with FYCP solution:
Overview of Existing scenario:
· In the existing scenario YTD, MTD and ITD values are calculating in info provider level. Example – Restricted
key figure. MTD and YTD Key figures are restricted by the variable ZFPABAP7 for Fiscal period and
ZFWABYTD for Fiscal week. Values for these variables are calculated during the report runtime in user exit
with the help of user input values which has given through the variable ZI_FPER for fiscal period and ZFW4USR
for fiscal week.
So, this solution, FYCP solution, is suitable only for the reports which are having the above mentioned variables.
Report customizing Concept with FYCP solution:
· Replace the existing variables with new FYCP variable as mentioned below.
- ZFPABAP7 variable is replace with ZFYCP2
- ZFI_PER variable is replace with ZFYCP1
- ZFW4USR variable is replace with ZFYCP15
- ZFWABYTD variable is replace with ZFYCP3
· Add below mentioned two variables in the dummy key figures, which has to be created locally in BEx-Designer
and later hide those key figures.
- Select ZFYCP11 variable from virtual fiscal definition character to provide the selection option to user to enter the fiscal definition.
- Select ZFYCP13 variable from virtual fiscal period character or ZFYCP14 variable from virtual fiscal week character to provide the selection option to user to enter fiscal period or fiscal week interval (Select ZFYCP13 for period reports and ZFYCP14 for weekly report).
· In the existing report where ever we find the Fiscal Period and Fiscal week character with user input variable
replace with appropriate FYCP variable and keep it in global filter area and also keep the Virtual Fiscal Period
and Virtual Fiscal Week characters on that place from where we removed the Fiscal period and fiscal week.
· In the existing report wherever we find (except global filter area) Fiscal Period, Fiscal week and Fiscal year,
replace it with Virtual Fiscal period, Virtual Fiscal week and Virtual Fiscal year characters.
· The below mentioned Text variables are created in FYCP, use those variable in appropriate place to display
information.
Example – In Report description and Column key figure description
- &FSK& – Shows the fiscal definition
- &FPF& – Fiscal Period From value
- &FPT& – Fiscal Period To value
- &FWF& – Fiscal week From Value
- &FWT& – Fiscal week To Value
How to implement it:
Copy the existing report
How to customize monthly report with FYCP solution:
By seeing the user input variable we can identify that the report is weekly or monthly
Look at the key figures and analyzes how the key figures are defined.
Below Screen shot shows the report which need to customize.
By default in all the reports we need to maintain the info provider character in filter area with
restriction by ZCOPA_C01.
If the existing Key figure having below description with text variable then replace with
appropriate text variable of FYCP.
Add appropriate text variable in the report description to shows the dynamic information which are given in
the user input.
The same steps have to follow for the weekly report also, the only attention point is choosing the
appropriate FYCP weekly variables which has mentioned in the beginning slides.