Variable Creation on user entry FISPER

[#1]

Hi All,

I need help to design a BEx Query , currently I am facing a challenge. The client require the report query design as per the screen shot.

Hence I have

1. I have calculated YTD using 0P_FPER (User Entry Value).

2. But the rest figures , I need to display over the periods , for example user entered 003.2009 , the Key Figure should be displayed under three column 001 till 003 and YTD Key figure in single column cumulative of all three periods.

3. Please help as I am unable to create variable which will give me 002.2009 and another variable 003.2009 ..

The number of column should be dynamic in nature depending upon user entry.

Regards,

Subhasish

[former_member194898 · Accepted solution]

Hi,

Create a variable of type "value range".

Insert in "from" a exit counting first period of give year.

Inser in "to" any variable ready for input / single value.

In BEx insert 0FISCPER in columns of the query.

Hope that helps, Leszek

An example of exit code for counting first period of given year:

   WHEN 'ZFI_1STPERIOD.

     IF i_step = 2.
       LOOP AT i_t_var_range INTO l_s_var_range
         WHERE vnam = 'ZFI_CURR_YEAR'.
         EXIT.
       ENDLOOP.
       CLEAR: l_s_range.
       CONCATENATE  l_s_var_range-low(4) '001' INTO l_s_range-low.
       l_s_range-sign = 'I'. "loc_var_range-sign.
       l_s_range-opt = 'EQ'. "loc_var_range-opt.
       APPEND l_s_range TO e_t_range.

     ENDIF.

[Former Member · Accepted solution]

Hi Thank you very much for your response.

I have achieve the calculation of YTD , taking reference from

My second requirement is to calculate the  Previous YTD ( Depending on the Current YTD period ) with out CMOD using the same reference , but the chanagene is that the Offset option is not appearing for 0FISCPER \ 0FISCYEAR.

Please guide me with OffSet Variable as I have never created an offset variable.

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

Double click first your variable before doing the OFFSET thing..

Also, if your variable has an INTERVAL option, OFFSET will not work..

Regards,

Loed

,

[former_member194898 · Accepted solution]

Hi,

I don't think doing this with offset functionality is a good solution.

Hardcoded offset will work for exact month. eg. for period 3 you define offset -3, but if you have defined offset -3 it will not work in april (you need offset -4 for it).

Try to implement variable with exit (like I wrote before).

I'm sure you will find some good documents on SCN how to do this.

All you have to do is to paste the code you see in my first message in SE37.

Only change the name of variable counting first period (after WHEN .. ) and name of your variable in query's filter (after WHER vnam = ...)  

Regards, Leszek

[Former Member · Accepted solution]

Hi Leslaw,

The user enters the fiscal period , assume 003.2009

The YTD would be calculated as per my previous used solution for the year 2009 and period ( <= 003)

But for Previous Year YTD , I need to implement the solution variable with exit , as per your recommendation. But for this do I need to create a variable range.

Please suggest.

Thanks again in advance.

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

If I understood your scenario correctly, you can still get the YTD for previous year by using an offset of -12..Isn't it?

Regards,

Loed

[former_member194898 · Accepted solution]

Nice to hear that

Here is some code for counting the first period of previous year:

  WHEN 'ZFI_1STPERIOD_PREV'.

     IF i_step = 2.
       LOOP AT i_t_var_range INTO l_s_var_range
         WHERE vnam = 'ZFI_PREV_YEAR'.
         EXIT.
       ENDLOOP.
       CLEAR: l_s_range.
       CONCATENATE  l_s_var_range-low(4) '001' INTO l_s_range-low.
       l_s_range-sign = 'I'. "loc_var_range-sign.
       l_s_range-opt = 'EQ'. "loc_var_range-opt.
       APPEND l_s_range TO e_t_range.

     ENDIF.

Again you can use it in data range variable.

Regards, Leszek

[Former Member · Accepted solution]

Hi Loed,

What I have done , is YTD without Offset

I have consider an the use input variable (0P_FPER) and the calculate the KF  using restriction on period <= 0P_FPER and Fiscal Year = Replacement Variable on 0P_FPER to get the YEAR using offset setting as describe in below SCN solution

but just now I have get one variable 0I_BA106 ( Function Module : RSVAREXIT_0I_BA106) which states

0I_BA106

Cumulated Up To Prior FY Period (SAP Exit for 0P_FPER

hence I was thinking to use this variable to calculate the Prior YTD.

Please suggest.

Regards,

Subhasish

[Former Member · Accepted solution]

Hi Leslaw ,

Thank you very much for the code. Just out of curiosity , I have managed to identify a sap standard variable 0I_BA106 ( Function Module : RSVAREXIT_0I_BA106) which states

0I_BA106

Cumulated Up To Prior FY Period (SAP Exit for 0P_FPER

Please suggest if I could use to to calculate the Prior YTD.

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

So if you used FISCAL PERIOD and FISCAL YEAR, just use and OFFSET of -1 in FISCAL YEAR and offset of -12 in FISCAL PERIOD..

Regards,

Loed

[Loed · Accepted solution]

Hi Subhasish,

I did not use that variable yet but you may refer here for the description of other variables:

http://scn.sap.com/community/business-explorer/blog/2013/09/20/standard-bex-variables-in-sap-bi-for-...

Regards,

Loed

[Former Member · Accepted solution]

Actually , I am using DSO as a source (BW 7.4 on Hana) . The FISCAL YEAR in derived as a formula using Variant and Fiscal Period at DS level.

Hence , I am unable to get the Offset Option for the Fiscal Year InfoObject.

Regards,

Subhasish

[Former Member · Accepted solution]

So , I just have to use it as restriction (filter) = 0I_BA106 for the Previous Year YTD.

Do I need to consider any other variable for Fiscal Year restriction ?

The Function module states :

FUNCTION RSVAREXIT_0I_BA106 .

*"----------------------------------------------------------------------

*"*"Lokale Schnittstelle:

*"  IMPORTING

*"     VALUE(I_VNAM) LIKE  RSZGLOBV-VNAM

*"     VALUE(I_VARTYP) LIKE  RSZGLOBV-VARTYP

*"     VALUE(I_IOBJNM) LIKE  RSZGLOBV-IOBJNM

*"     VALUE(I_S_COB_PRO) TYPE  RSD_S_COB_PRO

*"     VALUE(I_S_RKB1D) TYPE  RSR_S_RKB1D

*"     VALUE(I_S_RKB1F) TYPE  RRO01_S_RKB1F

*"     VALUE(I_THX_VAR) TYPE  RRO01_THX_VAR

*"     VALUE(I_STEP) TYPE  I DEFAULT 0

*"  EXPORTING

*"     VALUE(E_T_RANGE) TYPE  RSR_T_RANGESID

*"  EXCEPTIONS

*"      NO_PROCESSING

*"      NO_VALUE

*"----------------------------------------------------------------------

***********************************************************************

* Purpose:

* get actual period from variable 0P_FPPER

* and compute interval from first period to corresponding period

* in previous fiscal year

***********************************************************************

* returning parameter

  DATA: l_s_range TYPE rsr_s_rangesid.

* work area for I_THX_VAR and Period

  DATA: l_wa_thx_var   LIKE LINE OF i_thx_var

      , l_ta_range     LIKE l_wa_thx_var-range

      , l_wa_range     LIKE LINE OF l_ta_range

      , l_period(3)    TYPE n

      , l_year(4)      TYPE n

      , l_prev_year(4) TYPE n

      .

*******************************************

* some checks to start with

  IF i_vnam <> '0I_BA106'.

    RAISE no_processing.

  ENDIF.

  IF i_step <> 2.

    RAISE no_processing.

  ENDIF.

  REFRESH e_t_range.

  CLEAR l_s_range.

  CLEAR l_period.

  READ TABLE i_thx_var INTO l_wa_thx_var

    WITH TABLE KEY vnam   = '0P_FPER'.

  l_ta_range = l_wa_thx_var-range.

* only one value allowed

  READ TABLE l_ta_range INTO l_wa_range INDEX 1.

  l_year   = l_wa_range-low(4).

  l_period = l_wa_range-low+4(3).

  IF  ( l_period IS INITIAL )

   OR ( l_year   IS INITIAL ).

    CLEAR l_s_range.

    RAISE no_value.

  ELSE.

* get previous year

    l_prev_year = l_year - 1 .

    CONCATENATE l_prev_year con_first_period INTO l_s_range-low.

    CONCATENATE l_prev_year l_period         INTO l_s_range-high.

    l_s_range-sign = 'I'.

    l_s_range-opt  = 'BT'.

  ENDIF.

  APPEND l_s_range TO e_t_range.

ENDFUNCTION.

Please suggest.

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

Try to test first its function in DEV..Sorry I am not familiar with that variable..

But based on its code, I think you need no more the FISCAL YEAR since it has an INTERVAL output..

So if you enter 003.2009..

The values of the variable (if I'm not mistaken) will be:

001.2008 - 003.2008

Regards,

Loed

[former_member194898 · Accepted solution]

Hi,

Here is an exaple of an exit usage:

Regards, Leszek

[Former Member · Accepted solution]

Yes , I have just developed that using 0I_BA106 for the Previous Year YTD.

and for current YTD , using 0P_FPER and a replacement variable for to extract Year.

Need to test the Data.

But Thank you very much for your help and support.

Regards,

Subhasish

[Former Member · Accepted solution]

Hi Leszek,

Thank again for the sample.

I am currently using the 0P_FPER and 0I_BA106 for the Previous Year YTD to calculate the YTD and Previous Year YTD.

If the Data Test Fails then I will use your sample.

Thank you very much.

Regards,

Subhasish

[former_member194898 · Accepted solution]

Of course.

I don't use 0P_FPER that's why I showed you sth else. 

Different objects but the same usage
Regards, Leszek

[Former Member · Accepted solution]

Hi Leslaw ,

Thank you very much for your help. Just a quick question , I am using this variable 0P_FPER , so for example if user enter 003.2009 , then I need to derive a KF based on 003.2008.

Please suggest to achieve it as I am not getting the OffSet Option.

Regards,

Subhasish

[former_member194898 · Accepted solution]

Hi,

Something like this (I didn't tested it)

   WHEN 'ZFI_PREWYEARSAMEMONTH'.

     IF i_step = 2.
       LOOP AT i_t_var_range INTO l_s_var_range
         WHERE vnam = '0P_FPER'.
         EXIT.
       ENDLOOP.
       CLEAR: l_s_range.

YEAR = l_s_var_range-low(4)-1.
MONTH = l_s_var_range-low+4(3).

       CONCATENATE  YEAR MONTH INTO l_s_range-low.
       l_s_range-sign = 'I'. "loc_var_range-sign.
       l_s_range-opt = 'EQ'. "loc_var_range-opt.
       APPEND l_s_range TO e_t_range.

     ENDIF.

Regards, Leszek

[Former Member · Accepted solution]

Hi Leslaw,

Thank you very much for the sample code , one more favor , please verify my code , that is mend to drive the Last Year Period from 0P_FPER

so if user has entered 005.2009 then my customer exit variable "Z_0FISCPER_CCSM_V001" will give me 005.2008.

***********************************************************************

* Purpose:

* get actual period from variable 0P_FPER

* and extract the period (00P) from user entered value in 0P_FPER

* in previous fiscal year

***********************************************************************

* returning parameter

  DATA: l_s_range TYPE rsr_s_rangesid.

* work area for I_THX_VAR and Period

  DATA: l_wa_thx_var   LIKE LINE OF i_thx_var

      , l_ta_range     LIKE l_wa_thx_var-range

      , l_wa_range     LIKE LINE OF l_ta_range

      , l_period(3)    TYPE n

      , l_year(4)      TYPE n

      , l_prev_year(4) TYPE n

      .

*******************************************

* some checks to start with

  IF i_vnam <> 'Z_0FISCPER_CCSM_V001'.

    RAISE no_processing.

  ENDIF.

  IF i_step <> 2.

    RAISE no_processing.

  ENDIF.

  REFRESH e_t_range.

  CLEAR l_s_range.

  CLEAR l_period.

  READ TABLE i_thx_var INTO l_wa_thx_var

    WITH TABLE KEY vnam   = '0P_FPER'.

  l_ta_range = l_wa_thx_var-range.

* only one value allowed

  READ TABLE l_ta_range INTO l_wa_range INDEX 1.

  l_year   = l_wa_range-low(4).

  l_period = l_wa_range-low+4(3).

  IF  ( l_period IS INITIAL )

   OR ( l_year   IS INITIAL ).

    CLEAR l_s_range.

    RAISE no_value.

  ELSE.

* get previous year

    l_prev_year = l_year - 1 .

    CONCATENATE l_prev_year con_first_period INTO l_s_range-low.

    l_s_range-sign = 'I'.

    l_s_range-opt  = 'EQ'.

  ENDIF.

  APPEND l_s_range TO e_t_range.

*************************

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

Just another option..

If you already have 0P_FPER as variable prompt and you need to get the last year fiscal period using the entered value in 0P_FPER, why don't you use OFFSET?

Just use the 0P_FPER variable then OFFSET it with -12..No need for CMOD..

Regards,

Loed

[former_member194898 · Accepted solution]

Hi,

Not exactly.

It's Fiscal period (not month !!!). In Poland for example we have 16 fiscal periods in a year  (12 + 4special periods). That mean that value 12 not necessary might be correct. The exit should work all right. Unfortunately now I'm away from SAP and I can't test it for you.

Just copy/paste the code and first check syntax then if it works all right. May be something will has to be changed. Don't worry, just change and try.

Regards, Leszek

[Former Member · Accepted solution]

Thanks for your response.

Actually Offset is not working for me. I guess it depends on the source as well.

I have use this code

*&------------------------------------------------------------------------------------------------------------------------

*&   Include            ZXRSRU01

*&------------------------------------------------------------------------------------------------------------------------

*************Customer Exit Variable Z_0FISCPER_CCSM_V001 *************

**DATA:  l_s_var_range LIKE LINE OF e_t_range.

DATA: l_s_var_range TYPE rrrangeexit.

DATA: l_s_range TYPE rsr_s_rangesid.

DATA:  period_var(3)   TYPE n

      ,year_var(4)     TYPE n

      ,p_year_var(4)     TYPE n.

******* At variable Z_0FISCPER_CCSM_V001

CASE i_vnam.

  WHEN 'Z_0FISCPER_CCSM_V001'.

  IF i_step = 2.

       LOOP AT i_t_var_range INTO l_s_var_range

         WHERE vnam = '0P_FPER'.

         EXIT.

       ENDLOOP.

       CLEAR: l_s_range.

  year_var   = l_s_var_range-low(4).

  period_var = l_s_var_range-low+4(3).

*** Get the Last Year

  p_year_var = year_var - 1.

    CONCATENATE period_var p_year_var INTO l_s_range-low.

    l_s_range-sign = 'I'."loc_var_range-sign.

    l_s_range-opt  = 'EQ'. "loc_var_range-opt.

    APPEND l_s_range TO e_t_range.

   ENDIF.

ENDCASE.

Regards,

Subhasish

[Former Member · Accepted solution]

Hi Loed,

Well the BA says to consider the Offset as it is better for the current Business scenario.

Regards,

Subhasish

[Loed · Accepted solution]

Hi Subhasish,

Did you manage to make it work?

Regards,

Loed

[Loed · Accepted solution]

Hi Leszek,

Yes you are right that offset will not work for 16 fiscal periods..However he didn't mention what variant he is using so I assumed that his variant is K4 that's why I said OFFSET is another option..

But your suggestion is more flexible with any variant..

Regards,

Loed

[former_member194898 · Accepted solution]

Yes,

I wrote this just to be sure we all understand one another.

Regards,

Leszek

[former_member194898 · Accepted solution]

Hi,

I managed to test what has been suggested here.

Below you find code that work all right.

Regards,

Leszek

may be variables YEAR and PERIOD have to be declared

like this:

DATA:    YEAR(4) TYPE C,

          PERIOD(3) TYPE C,

WHEN 'ZPREVYEARSAMEFPERIOD'.

* PERIOD(3) TYPE C.

* YEAR(4) TYPE C.

       IF i_step = 2.

         LOOP AT i_t_var_range INTO l_s_var_range

           WHERE vnam = '0P_FPER'.

           EXIT.

         ENDLOOP.

         CLEAR: l_s_range.

         YEAR = l_s_var_range-low(4).

         YEAR = YEAR - 1.

         PERIOD = l_s_var_range-low+4(3).

         CONCATENATE YEAR PERIOD INTO l_s_range-low.

         l_s_range-sign = 'I'. "loc_var_range-sign.

         l_s_range-opt = 'EQ'. "loc_var_range-opt.

         APPEND l_s_range TO e_t_range.

       ENDIF.

[Former Member · Accepted solution]

Thank you very much.

[Former Member · Accepted solution]

Yes It will work as there is no transnational data for special periods in current business.

[Loed · Accepted solution]

Hi Subhasish,

Ok..Because you said "Actually Offset is not working for me"..

Anyway, just use any of the suggestions to you..

Regards,

Loed

[yasemin_kilinc]

Hi Subhasish,

I am not sure if this will meet your requirement with the exact view. Though you can design your query like this: To the columns add fiscper and key figures (actual nad planned), select to show the totals for fiscper. Add cost group to the rows. Then in the characteristic restrictions filter fiscper. Select the filter value as lower or equal to the manual input variable. Add fiscyear to the characteristic restrictions and filter it with a replacement type. Select Replace with variable and add the manual input variable name you have added for fiscper.Set the offset length as 4.

I hope it gives an idea.

Regards

Yasemin...

[Former Member]

Thank you for your response.