Customer exit for Bex

[Former Member]

Hi Experts,

I am trying to get the Data of previous month based on the user input. For example user enters the date as (01.05.2013 - 31.05.2013) then i need to get the previous month data i.e data of April .

Below is my code

DATA :ZRK1 TYPE SY-DATUM,

      ZRK2 TYPE SY-DATUM,

      ZT_DT1 TYPE SY-DATUM,

      ZT_DT2 TYPE SY-DATUM,

      ZFIDAY TYPE SY-DATUM,

      ZLSDAY TYPE SY-DATUM.



IF I_STEP = 2.

  CASE i_vnam.

WHEN 'ZCDAY'.

LOOP AT i_t_var_range INTO L_S_VAR_RANGE WHERE vnam = 'ZVAR_ADA_PERD2'.
CLEAR: l_s_range.
ZT_DT1 = loc_var_range-low.
ZT_DT2 = loc_var_range-HIGH.

        CALL FUNCTION 'DATE_CREATE'

        EXPORTING

            ANZAHL_JAHRE = 0
            ANZAHL_KALTAGE = 0
            ANZAHL_MONATE = '-1'
            ANZAHL_TAGE = 0
            DATUM_EIN = ZT_DT1
*            DATUM_EIN_ULT = ' '
*            ULTIMO_SETZEN = ' '

        IMPORTING

            DATUM_AUS = ZFIDAY .

*             E_TT =
*             E_ULTKZ =

        CALL FUNCTION 'DATE_CREATE'

        EXPORTING
          ANZAHL_JAHRE = 0
          ANZAHL_KALTAGE = 0
          ANZAHL_MONATE = '-1'
          ANZAHL_TAGE = 0
          DATUM_EIN = ZT_DT2
*           DATUM_EIN_ULT = ' '
*           ULTIMO_SETZEN = ' '

        IMPORTING

            DATUM_AUS = ZLSDAY.
*             E_TT =
*             E_ULTKZ =

        l_s_range-low = ZFIDAY .
        l_s_range-high = ZLSDAY .
        l_s_range-sign = 'I'.
        l_s_range-opt = 'EQ'.
        APPEND l_s_range TO e_t_range.
        ENDLOOP.
        ENDCASE.
        ENDIF.

Note: ZCDAY is my customer exit variable on 0CALDAY.

Points will be rewarded for help.

Regards,

Manjunath

[former_member182470 · Accepted solution]

Hi Manjunath,

No code is required to get previous month.

1) Drag Calmonth and restrict to your KF

2) Create a variable for Calmonth -->Replacement path-->reference char as calmonth-->Details tab-->Replace with Variable( your date range variable)-->external key value-->Use interval as From-->offset start as 1 and offset length as 6. This way you are taking 201305 from 01.05.2013.

3) Finally put offset -1. this will give you 04.2013

Regards,

Suman

[Former Member · Accepted solution]

Could you tel me in detail ... i am unable to follow . I am new to BI wit jus 2 months of experience

[former_member182470 · Accepted solution]

I gave you perfect detailed steps. You can show my reply to your colleagues. You can simply follow my steps..

[Former Member · Accepted solution]

I am getting an error "varuable value should be numeric" wen i try to run the query

[former_member182470 · Accepted solution]

Have you selected "external value display" in details tab of your variable? If you have already tried that, choose "key."

Then, you will not get Numeric data error.

Message was edited by: Suman Chakravarthy K

[Former Member · Accepted solution]

I tried as u said by changing 'Replace with' and 'Use interval' with different offset values. But unable to get .

[former_member182470 · Accepted solution]

Select From in Use interval. You should get the data. I am getting data in my system. Are you getting Numeric data error still?

Keep your main variable in Default values pane but not in Filter pane.

[Former Member · Accepted solution]

I got it. When i run the query in rsrt the value is changed but data is not getting displayed.

But if i remove 07.2013 or 01.08.2013 - 30.08.2013 Data is getting displayed

[former_member182470 · Accepted solution]

I think you are getting confused.

You should keep Issue date variable in default values Pane in the query.

Restrict your KF with calmonth variable what you have created. This wil show you report for sure..

[Former Member · Accepted solution]

I din use any key figure .

Issue date is Char with datatype as  dats

I am entering Issue date as 01.08.2013 - 31.08.2013
When i drag n drop Calandey Year/Month i should get 07.2013 data .

May i know what key figure you are refering to?

[former_member182470 · Accepted solution]

Your query design is wrong , it seems. We restrict our KFs with Time char variables to show data as per that time char.

User enters :  01.08.2013 - 31.08.2013. This variable will be in default values pane in query.

Suppose you to want to show Sales KF. You have to restrict your Sales KF with Calmonth variable(which replaces Issue date variable, as I said).

If you want to use calmonth as Drill down, then you have to achieve calmonth in the modeling side in Cube level but not at Bex level. I assumed so far you are trying to view some KF with last month selection automaticallly. Please discuss with your Team lead.

[Former Member · Accepted solution]

My Query Design is as follows:(Jus for an example)

Scenario:

Displaying no of cars and no of days based on the issue date according to different plants

Selection Screen : Sold Date / Issue date(01.05.2013 - 31.05.2013)

In Filter:

     I don have any characterstic restriction

     In default values i have  - Sold Date/Issue date(CHAR-DATS)

                                             -Plant Code

Rows/Col:

    - No of Cars Sold(KF-DEC)

    - No of Days (Restricted key figure based on Issue date - counter of unique dates)

    - Previousmonth No of Cars

    - Previousmonth No of Days

Can u please tell me where did i go wrong?
I would rly appreciate ur help.

[anshu_lilhori · Accepted solution]

Hi Manjunath,

Take any kf and restrict it with variable-- created on Sold Date / Issue date(01.05.2013 - 31.05.2013).Hide this Selection this will act as dummy selection for user input only

Remove it from default pane as well.Because it is acting as global filter and not local filter.

And as you are trying to get the data for previous month but with that date restriction it is not giving any value.

Please give a try and let us know the result.

Regards,

AL

[former_member182470 · Accepted solution]

Ok. I have got your requirement.

Step 1 : Drag Issue date into Default values and create a User entry variable with Interval

Step 2 : For Plant also create a variable(mulitple single values) in Default values pane

Step 3 : Drag No. of Cars Sold KF into KF pane. Hope it is a direct KF from your Cube

Step 4 : Drag No. of Days RKF

Step 5 : You have to copy above two KFs by right click on them and change the edit the descriptions                for Prev Month

Step 6 : Restrict theme with Calmonth field-->Restrict with variable(replacement of Issue date variable which I told you initially) and put offset -1

I think i have explained you very clearly. You should take help of your colleagues if you don't understand my steps..

[Former Member · Accepted solution]

I did exactly as u said not even missing a single point. But wen i run the query it says 'Variable value must be numeric'

[former_member182470 · Accepted solution]

You might have selected Extrnal char value. Please select Key

[Former Member · Accepted solution]

Value 012080 of characteristic 0CALMONTH is not plausible

I am getting above error after changing it to key .

Replace with as key

Interval as from value

Offset start as 1
offset length as 6

[anshu_lilhori · Accepted solution]

Hi,

Offset start should be 0 and length should be 6.

As the value for date is internally stored as 20120501.for calmonth/year we need 201205.

So change it accordingly.

Regards,

AL

[former_member182470 · Accepted solution]

Keep offset start as blank. and length is 6. Thjis will pick 201208 and finally put offset -1 to the calmonth variable to get previous month.

Message was edited by: Suman Chakravarthy K

[Former Member · Accepted solution]

Finally i got it. The problem was global restriction to Issue date. I removed it and finally got it.
Thank you Suman and ANshu

[former_member182470 · Accepted solution]

Yeah. That' what I was saying to remove from filte. Congrats

[anshu_lilhori · Accepted solution]

Ok So i believe you must have kept it in local filters as i told in my last reply.Because default value also act as global dynamic filter as far as i know.

Regards,

AL

[former_member182470 · Accepted solution]

My Mistake. Default values can also act like global but dynamic in nature after the result comes.

[former_member182470 · Accepted solution]

We tend to forget silly things some times..

[anshu_lilhori · Accepted solution]

No Issues suman we are here to correct at times

[Former Member · Accepted solution]

This query runs well in DEV but throwing error in PRD

Diagnosis

You wish to move the value of a variable. The result is consequently no longer

in the permitted value range

System Response

Procedure

Procedure for System Administration

Notification Number BRAIN 617

[former_member182470 · Accepted solution]

Have you transported the query properly to PRD after proper testing in Qua? Please check the query definition thoroughly and follow my steps again. If it works in Dev, then it should work in PRD as well.

You may check my blog : How to derive Calmonth,Calyear from Calday easily