Skip to Content

Status of Tickets based on their Dates

October 1, 2013 | 413 Views |
 abapabap objectsbasedbexbex queriesbex variablebexapi vba
Follow

Scenario:

Sometimes we may get the requirement to find the status of ticket based on their created on and closed date.This document can be applicable for any object which has some validity period and we need to find the status of it based on any date.

Lets say for eg:

Ticket Created on Closed Status
1001 20130101 20130115 CLOSED

So now if user enter any date in between those two dates then we need to get the status of the same.

The approach which we can follow to achieve the same will be sharing with you with all the steps and screenshots.

Data Sheet:

8.JPG

Data Loaded in DSO:

9.JPG

Step 1–Go to  BEx Query Designer–Drag and Drop Created on date in Characteristic Restrictions panel–Right click on Created date—Restrict–Show–Value ranges—Less than or equal to–Choose the variable 0I_DAYWT (User input variable).

1.JPG

Step 2—Now drag and drop Closing Date in Characteristic Restrictions panel–Right click on Closing date–Restrict–Variable–Create new variable.Refer the screenshot for details.

2.JPG

Hit the Replacement Path tab and give the details as mentioned in below screenshot

3.JPG

Now Save this variable.

Restrict the Closing date with the newly created variable as shown in below screenshot.

4.JPG

Users also need to see the date which has been entered by them on Selection screen in Query Columns as well.So for that our approach will be explained in furhter steps.

Step 3—Go to Columns panel in query designer create a new formula–Under Available Operands–Formula variable–Right click–New variable.Refer the Screenshot for reference.

5.JPG

Hit the Replacement Path tab and give the details as mentioned in below screenshot

6.JPG

Hit the Currency/Unit tab and give the details as mentioned in below screenshot.

7.JPG

Save this Variable and use it in your formula.

Query Format

11.JPG


Variable Screen:

12.JPG

Query Output:

14.JPG

So you can see based on the input date we got the status of tickets and also we got the date entered by user on selection screen in User Input column.

Hope this will be helpful.

To report this post you need to login first.

63 Comments

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

  1. Krishna Chaitanya

    Hi Anshu,

    Thanks for sharing this to all of us.  Actually I implemented the same in my previous project.

    Thank you once again for remembering all those things.

    🙂 🙂 🙂 🙂 🙂

    Regards,

    Krishna Chaitanya.

    (0) 
    1. Anshu Lilhori Post author

      Thanks Krishna,

      Its always great feeling to receive the first comment on the blog.. 🙂 ..This document will definitely come handy in future so thought of documenting it.

      Much appreciated your feedback.

      Regards,

      AL

      (0) 
  2. Former Member

    Like this way needed more docs on Bex category. Really good one and sure may come handy soon. Thank You anshu for making and sharing with us.

    Regards

    Raman

    (0) 
  3. Former Member

    Hi Anshu,

    Good thought and Nicely presented with screen shots how to implement. 🙂 🙂

    Thanks For Sharing with us. 😉 😉 😉

    Regards,
    Giri

    (0) 
  4. Former Member

    Hi Anshu,

    Created on and Closing Date restrictions will work together as AND condition right.

    The variable created in Step 2 and Step 3 are same or different?

    Regards,

    Dilip.

    (0) 
    1. Anshu Lilhori Post author

      Hi Dilip,

      If you look closely at those variables then you will notice one is formula variable and other characteristic variable..

      Regards,

      AL

      (0) 
  12. Former Member

    Hi Anshu,

    Nice work !!

    I will implement this approach in my requirement also which i did via writing long CMOD codes in i_step = 2 mode .

    To add further, my requirement also extends to having month as a general input rather than date . So i am trying to figure out an approach for that also.

    Regards,

    Ashu

    (0) 
    1. Anshu Lilhori Post author

      Hi Ashutosh,

      Thanks for your feedback..So you are saying that instead of date you will have month or both.

      I believe by replacing the date with month in my approach it should suffice your requirement.

      Regards,

      AL

      (0) 
      1. Former Member

        I am not completely sure of this requirement at this moment as it needs further clarification from the management …. but what i am guessing is that …. instead of a general date …. i will have a general month as an input.

        And then we need to check whether the intervals of created on and closing date lie partially within that month or completely. And display for example respective ticket numbers .

        (0) 
        1. Anshu Lilhori Post author

          I will also be little skeptical unless we do not have the complete requirement with the proper data.As this makes easy to analyze and get some concrete solution.

          Regards,

          AL

          (0) 
    1. Anshu Lilhori Post author

      Yes Kamal very true..I am digging out all possibility to avoid codes in certain occasion if your requirement is straightforward..

      Soon will come up with more blogs in all areas.

      Regards.

      AL

      (0) 
  21. Former Member

    Thanks Anshu for putting it nicely with all the screenshots. It will help many people.

    Small suggestions:

    1. You could make it further better by putting few more lines explaining “how it works”. I know its obvious for BW consultants but it might help newbies.

    “How it Works” section should contain 3/4 lines. saying how the user’s input is taken into account for selection of data and displaying the last column.

    2. You could also add some more tags. “date calculation”, “selection based on two dates”.

    (0) 
    1. Anshu Lilhori Post author

      Thanks Sujit for your feedback and will definitely try to consider the points you mentioned.

      I learnt a lot from your replies when you were active contributor and also liked the approaches you used to address the issues.Hope to see you again contributing actively.

      Regards,

      AL

      (0) 
  26. Former Member

    Hello Anshu,

    Nice document, I am sure the techinique you implemented here would definetely come in handy to everyone working with BEx.

    Thanks for sharing !

    (0) 
    1. Anshu Lilhori Post author

      Thanks for your feedback..Infact i had same requirement in one of my projects i ended up achieving it with the help of code and then eventually realized that this can also be possible with the help of variable operators.. 😉

      Regards,

      AL

      (0) 

Leave a Reply