Skip to Content

Setup variables for BEx Query

Dear all,

I am working on a BEx query. I would like to show MTD and YTD actual & planned expenses. My questions are as follows:

(1) how can I set up variables or variants which can look up the system date to calculate the MTD and YTD date? For example, I execute the query without input the date and I would like the query automatically look up the date for YTD (e.g. 1/1/2009 u2013 2/20/2009) and MTD (2/1/2009 u2013 2/20/2009) for actual expenses?

(2) Since the planned data is by year and distribute 12 months equally and not by day, how can I set up variables which can look up the system date to calculate the number of the days from the beginning of the year up to today and beginning of the month update to today so I can calculate the MTD and YTD planned expenses based on the annual budget.

Thanks

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    Posted on Feb 24, 2009 at 04:25 AM

    For question (5), is any variable to show the last date of the current month so I know the total number of the month to estimate the plan data month to date? Thanks

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi LaDo,

      wht i tend to Understand is u wanted to have MTD & YTD for a given range.

      it is very simple just drag n drop the Calmonth Above Key Figure & for YTD calyear above Keyfigure.

      just restict them to already availabe variable of type Interval.

      Thats it.

      Regards,

      sandeep

  • Posted on Feb 23, 2009 at 04:44 PM

    Thank you for all your response and they are very helpful but the problem is I am kind of new to BEx query and I am not familar with user exit. I just wonder do you have any link to the documentaion of writing user exit for variables? Thanks again

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 20, 2009 at 11:56 PM

    Hi,

    This can be accomplished by SY DAT feild, either by SAP exit variables or by customer exit variable using SY DAT information.

    In your question:

    for the first part which is actual data , we have readily available SAP exit variable.

    These get processed on their own without user input and using system details. All the necessary logic is already in place for them to work in the right manner.

    On date feild

    For YTD: 0CYTCD

    For plan data, as the granularity is only month, are you trying to split the months plan by number of days to get the daywise plan?

    If yes, then its required to get plan as below.

    1. Plan for MTD = this probably requires to 2 formula variables and 1 SAP exit char variable.

    One of the formula varible is current day and the other is start of current month.. A difference of these two would give no of days in current month till date(X).

    Other char variable is for current month, to confine the value for current month.

    Then divide current month plan with full month days and multiply with X.

    2. For YTD: use char variable to obtain YTD evaluate till last month and then add MTD plan evaluated during 1.

    I was not able to search for SAP exit variable names, this should be an easy exercise, if you can.

    Naveen.A

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 21, 2009 at 05:54 AM

    Hi,

    Check in snd

    Variable Exit

    You need to change some logic in the above given code (in that thread)

    Thanks

    Reddy

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 24, 2009 at 04:12 AM

    Reddy. Thank you for your reply. I checked out the link but it doesn't seem answer my questions. I still have a few questions:

    (1) In CMOD - enhancement. I see

    EXIT_SAPLRSAP_001

    EXIT_SAPLRSAP_002

    EXIT_SAPLRSAP_003

    EXIT_SAPLRSAP_004

    I guess EXIT_SAPLRSAP_001 is for transaction data and EXIT_SAPLRSAP_002 for master data. what aer EXIT_SAPLRSAP_003 & EXIT_SAPLRSAP_004 used for?

    (2) If I create the exit for the variables, should I create under EXIT_SAPLRSAP_001?

    (3) Is any document available to list and explain all standard SAP variable for BEx query? it seems I can use standard SAP variable for YTD as Naveen said.

    (4) service.sap.com\bw is moved to service.sap.com\bi and I can't find the document for exit.

    (5) For the planned data YTD, I should be able to get the current date easily from system date but I also need the total number of day for the current month in order to calcuate the plan data based on the proportion since the planned data I enter by month not posted by day. If current date is Feb 22, I should use 22/28 x Feb monthly planned data to estimate planned data by date in order to compare the actual data since the actual data is posted by day.

    Reddy coding is very helpful. Thanks again.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 24, 2009 at 05:16 PM

    Thanks Reddy,

    Since my variable for planned data is not date variable anymore, it is a factor = current date / number of days in current month and I used this to multiple by the planned monthly data. If I used entire month and the plan data will be overvalue, I can't compare to the MTD actual data since actual date is updated by day. How can I create a variable for this and use it to caluclate in the BEx Query?

    I believe the function will work but I don't know much about ABAP. I try to search sample code for the function. here is what I get but I don't think it is going to work. Can you point out what is missing? I believe I need to put it under 'ZXRSAU01'

    Under CASE I_DATASOURCE

    I put

    WHEN 'ZMTDFACTOR'. #the variable name of the MTD factor

    PERFORM ZMTDFACTOR_EXIT.

    In ZMTDFACTOR_EXIT and here is the code I get so far

    WHEN 'ZMTDFACOTR'.

    DATA: SDT TYPE SY-DATUM,

    MON_FACTOR TYPE N,

    ST_CD(2) TYPE N,

    ST_LD(2) TYPE N.

    l_date_in type sy-datum,

    l_date_out type sy-datum.

    SDT = SY-DATUM.

    *current date

    ST_CD = SDT+6(2).

    CONCATENATE l_v_calmonth '01' into l_t_range-low.

    l_date_in = l_t_range-low.

    CALL FUNCTION 'SLS_MISC_GET_LAST_DAY_OF_MONTH'

    EXPORTING

    day_in = l_date_in

    IMPORTING

    LAST_DAY_OF_MONTH = l_date_out

    EXCEPTIONS

    DAY_IN_NOT_VALID = 1.

    *last date

    ST_LD = l_date_out+6(2).

    *Calculate the factor based on the MTD

    MON_FACTOR = ST_CD / ST_LD.

    How can I send MON_FACTOR to ZMTDFACOTR and back to the query?

    Thanks again.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.