In most of the project implementations, business users need to have financial reports capable of analyzing data over different time frames like MTD, YTD and Rolling 12 along with last year MTD, YTD and Rolling 12. These reports are used for budgeting and forecast as well as revenue analysis over historical period.
The general requirements are as follows,
There are many business content variables readily available which can be used as:
But none of them satisfies above requirement of calculating different time interval based on user input.
Prerequisite for such kind of reporting is that data should be available with proper level of granularity, for e.g. Year to Date reporting needs month wise data while week level reporting needs day level data.
For BusinessObjects reporting, it is good to have readily available BEX query with all the calculations done and on top of which direct universe can be created.
In our scenario we are going to have MTD, YTD and R12 level reporting. For this kind of reporting we need month wise data availability at source and we will use 0FISCPER for all the calculations.
Below is the sample data we have used for our analysis.
But before we actually go and start the report development we need to understand how the different time frame values are calculated,
Time frame | Definition | Example |
Period | Current fiscal period | Current Period = March 2010 |
MTD | Current fiscal year Month to Date | March 2010 |
Last MTD | Last fiscal year Month to date | March 2009 In this case the metrics will always display last fiscal year month end data for selected Month as it’s historical irrespective of when the report is executed. |
YTD | Current fiscal Year to Date | July 2009 - March 2010 If the report is executed for March 2010; the metrics will display data from July09-March10. This means beginning of current fiscal period till current fiscal period. |
Last YTD | Last fiscal Year to Date | July 2008 - March 2009 Same as above but for last fiscal year. |
Rolling 12 | Current Rolling 12 Months | April 2009 - March 2010 This time frame is independent of fiscal year. The metrics data is displayed for rolling 12 months means for Mar'10, the metrics will display data from Apr09-Mar'10 and in Apr'10 it will be from May09-Apr'10. |
Last Rolling 12 | Last Rolling 12 Months | April 2008 - March 2009 Same as above but for last year |
Now first step towards report designing is, we will have to create different customer exit variables. Each variable representing a particular time frame.
In the same fashion all other variables will be created. MTD customer exit variable will be available to accept the input from customer.
After all the customer exit variables are created, we need to create restricted key figure for each time frame as shown below.
Note: For reusability, global RKF or global structure can be created so that the same key metrics can be used across multiple reports.
Final report will have layout as shown below.
Upon execution we will have following selection screen displayed,
The value of fiscal period is defaulted to current fiscal period. In our current scenario, change the default value to 004.2011 and execute the report.
There are few precautions to be taken while creating such reports,
1) Do not include 0FISCPER or other time characteristic in filter section.
If we include 0FISCPER in filter section, then data aggregation for other key figures will not happen and values for other key figures, for e.g. YTD will not be shown properly.
But on the other hand you can include the characteristic with lower granularity i.e. 0FISCYEAR can be put as filter but the value should satisfy the range.
Example: We are running report for period 001.2010 then so as to display the MTD and LMTD value, year has to be restricted to 2010 and 2009. This will also restrict the query processing data and improve the performance when multiple key metrics are analyzed.
2) Do not include time characteristic in drilldown.
If we include 0FISCPER in filter section then data aggregation for other key figures will not happen and values for other key figures e.g. YTD will not be shown properly.
3) Mandatory inclusion of MTD Revenue key figure in all the reports.
Though we don’t want to display MTD key metrics in the report, still we have to include MTD key metric and then hide it. This is required to capture the fiscal period input in the query for other calculations. We can create a dummy variable also and restrict it with MTD customer exit variable.
Customer Exit Logic for all the used variables is given below,
WHEN 'TST_MTD'.
DATA: lv_buper TYPE poper,
lv_gjahr TYPE bdatj.
o Get current fiscal period from system date.
IF i_step = 1.
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'
EXPORTING
i_date = sy-datum
i_periv = 'V6'
IMPORTING
e_buper = lv_buper
e_gjahr = lv_gjahr
EXCEPTIONS
input_false = 1
t009_notfound = 2
t009b_notfound = 3
OTHERS = 4.
IF sy-subrc = 0.
o Current period is nothing but equal to Month to Date.
CLEAR l_s_range.
CONCATENATE lv_gjahr lv_buper INTO l_s_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
ENDIF.
WHEN 'TST_LMTD'.
IF i_step = 2.
DATA:l_vy7(4) TYPE c,
l_vm7(3) TYPE c.
CLEAR: l_s_range,loc_var_range.
o Read current MTD value.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = 'TST_MTD'.
o In Last MTD calculation the month will remain same as that of
current MTD but it will be from previous year.
l_vy7 = loc_var_range-low+0(4).
l_vy7 = l_vy7 - 1.
l_vm7 = loc_var_range-low+4(3).
CONCATENATE l_vy7 l_vm7 INTO loc_var_range-low.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
ENDLOOP.
ENDIF.
WHEN 'TST_R12'.
IF i_step = 2.
DATA: l_vy8(4) TYPE c,
l_mn8(3) TYPE c,
l_mn9(1) TYPE c.
CLEAR: l_s_range,loc_var_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = 'TST_MTD'.
o Rolling12 will be a range including current month and previous
11 months. The higher limit will be current MTD and lower
limit will be calculated by subtracting one from current year and
adding one in current month value.
l_s_range-high = loc_var_range-low.
l_vy8 = loc_var_range-low+0(4).
l_vy8 = l_vy8 - 1.
l_mn8 = loc_var_range-low+4(3).
l_mn8 = l_mn8 + 1.
o Append required number of zeroes based on the month value as the
fiscal period format is MMMYYYY.
IF l_mn8 GE '10'.
CONCATENATE l_vy8 '0' l_mn8 INTO loc_var_range-low.
ELSE.
l_mn9 = l_mn8+1(1).
CONCATENATE l_vy8 '00' l_mn9 INTO loc_var_range-low.
ENDIF.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'BT'.
APPEND l_s_range TO e_t_range.
EXIT.
ENDLOOP.
ENDIF.
WHEN 'TST_LR12'.
IF i_step = 2.
DATA: l_vy10(4) TYPE c,
l_mn10(3) TYPE c,
l_mn11(1) TYPE c.
CLEAR: l_s_range,loc_var_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = 'TST_MTD'.
o In Last Rolling12 calculation, the higher and lower limits are same as that of
Rolling12 but they are from previous year so we subtract 1 from year value.
l_vy10 = loc_var_range-low+0(4).
l_vy10 = l_vy10 - 1.
l_mn10 = loc_var_range-low+4(3).
CONCATENATE l_vy10 l_mn10 INTO loc_var_range-low.
l_s_range-high = loc_var_range-low.
l_vy10 = l_vy10 - 1.
l_mn10 = l_mn10 + 1.
IF l_mn10 GE '10'.
CONCATENATE l_vy10 '0' l_mn10 INTO loc_var_range-low.
ELSE.
l_mn11 = l_mn10+1(1).
CONCATENATE l_vy10 '00' l_mn11 INTO loc_var_range-low.
ENDIF.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'BT'.
APPEND l_s_range TO e_t_range.
EXIT.
ENDLOOP.
ENDIF.
WHEN 'TST_YTD'.
IF i_step = 2.
CLEAR: l_s_range,loc_var_range.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = 'TST_MTD'.
o In YTD calculation the lower limit will be always 1 and the higher limit will be
current period, which is calculated based on the MTD value.
IF loc_var_range-low+4(3) = '001'.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
ELSE.
l_s_range-high = loc_var_range-low.
loc_var_range-low+4(3) ='001'.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'BT'.
APPEND l_s_range TO e_t_range.
ENDIF.
EXIT.
ENDLOOP.
ENDIF.
WHEN 'TST_LYTD'.
IF i_step = 2.
DATA: l_vy(4) TYPE c,
l_pe(3) TYPE c.
CLEAR: l_s_range,loc_var_range.
o In the last year to date, lower month will always be 1 and higher
month will be MTD value. But the year for both of these calculations will be
(MTD year) - 1.
LOOP AT i_t_var_range INTO loc_var_range WHERE vnam = 'TST_MTD'.
l_vy = loc_var_range-low+0(4).
l_pe = loc_var_range-low+4(3).
l_vy = l_vy - 1.
CONCATENATE l_vy l_pe INTO loc_var_range-low.
l_s_range-high = loc_var_range-low.
CLEAR loc_var_range-low.
CONCATENATE l_vy '001' INTO loc_var_range-low.
l_s_range-low = loc_var_range-low.
l_s_range-sign = 'I'.
l_s_range-opt = 'BT'.
APPEND l_s_range TO e_t_range.
EXIT.
ENDLOOP.
ENDIF.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |