Skip to Content

 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, 

 

  • The financial metrics data should be aggregated based on these time frames.
  • These time frames should be calculated based on the period entered by business user on selection screen while running a report.

 

 There are many business content variables readily available which can be used as:

  1. Last 12 months including current month
  2. Last 12 months excluding current month
  3. Last month of previous year and so on.

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.

 Reference Data

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.

Image1

 

 

 

 

 

 

 

 

 

 

 

Image2

 

 

 

 

 

 

 

 

 

  

Image3

 

 

 

 

 

 

 

 

 

 

In the same fashion all other variables will be created. MTD customer exit variable will be available to accept the input from customer.

Image4

 

 

 

 

 

 

 

 

 

 

Image5

 

 

 

 

 

 

 

 

 

   

After all the customer exit variables are created, we need to create restricted key figure for each time frame as shown below.

Image6 

 

 

 

 

 

 

 

 

 

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.

Image7

 

 

 

 

 

 

 

 

 

Upon execution we will have following selection screen displayed,

Image8

 

 

 

 

 

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.

Image9

 

 

 

 

 

Image10

 

 

 

 

 

 

 

 

There are few precautions to be taken while creating such reports,

 

  1) Do not include 0FISCPER or other time characteristic in filter section.

      

Image11  

  

  

  

  

  

  

 

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.  

 

Image12

 

 

 

 

 

 

 

 

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.

 

Image13

 

 

 

 

 

 

 

 

   

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,

 

  •   MTD Input Variable

 

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.

 

 

  •  Last Month to Date Variable

 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.

 

  •  Rolling12 Variable

 

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.

 

  • Last Rolling12 Variable

 

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.

 

  •   Year to Date Variable

 

 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.

 

  •   Last Year to Date Variable

 

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.

To report this post you need to login first.

2 Comments

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

Leave a Reply