Status of Tickets based on their Dates
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:
Data Loaded in DSO:
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).
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.
Hit the Replacement Path tab and give the details as mentioned in below screenshot
Now Save this variable.
Restrict the Closing date with the newly created variable as shown in below screenshot.
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.
Hit the Replacement Path tab and give the details as mentioned in below screenshot
Hit the Currency/Unit tab and give the details as mentioned in below screenshot.
Save this Variable and use it in your formula.
Query Format
Variable Screen:
Query Output:
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.
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.
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
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
Thanks Raman for your valuable feedback and continuous support and encouragement.
Much appreciated.
Regards,
AL
Hi Anshu,
Good thought and Nicely presented with screen shots how to implement. 🙂 🙂
Thanks For Sharing with us. 😉 😉 😉
Regards,
Giri
Thanks Giri..Glad that you liked it.. 🙂 ..Your smileys really make me 🙂
Regards,
AL
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.
Hi Dilip,
If you look closely at those variables then you will notice one is formula variable and other characteristic variable..
Regards,
AL
NIce information anshu.
🙂
Thanks Satendra..for your feedback.
Regards,
AL
Ok the second one is Formula variable with Replacement path, fine
Thanks,
Dilip.
Yes dilip correct..
Hi Bex Hero,
Gr8 Post. Appreciate your efforts in making nicely and informatively 🙂 🙂
Regards,
Suman
Hi Suman,
Thanks for your valuable comments..you do not know that your blogs inspire me to make such blogs.. 🙂
Regards,
AL
I am inspired by others blogs. Your are inspired by my blogs. It's all continuous process in SCN 🙂
Yes its a continuous process which goes on and should keep going.. 🙂
Hi Anshu
Thanks For Sharing with us..
Regards
Sandeep
Thanks Sandeep.
Regards,
AL
Very Nice blog Anshu.
Thanks for sharing..
Regards,
Abdullah
Thanks Abdullah.for your feedback.
Regards,
AL
Its very nice document.
Regards,
Sushant
Thanks Sushant..for your feedback
Regards,
AL
Nice blog Anshu!
Martin
Thanks Martin for your feedback..
Regards,
AL
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
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
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 .
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
Hi Anshu,
Nice one exploring the potential of Formula variables especially.
Sees lots of yous interest in Bex area.
Thanks
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
Hmmn.
We are waiting then.
Thanks
very well explained. thanks for the information.
Thanks chadra..Really appreciate your comments.
Regards,
AL
Really Very nice document, Keep it up Anshu... 🙂
Thanks Mithun for your valuable feedback.
Much appreciated.
Regards,
AL
Good Presenation for scenario based on Replacement path.Helpful one
Thanks Karthick..Really appreciate your feedback.
Regards,
AL
Anshu ... You rock... Really Appreciate your effort and well drafted the document...
Thanks for sharing ... Keep posted.
Rated the doc.
Regards
KP
Thanks KP..Your comments really elated me.. 🙂 Yes will try to come up with some new blogs soon.
Regards,
AL
Really useful blog. Thanks
Thanks Raghvendra for your feedback..
Regards,
AL
nice Blog specially very descriptive one
Regards
Praveen Kumar
Thanks Praveen for your comments.
Regards,
AL
Nice blog.
Thanks for sharing.
Glad that you liked the blog..Eduardo.
Regards,
AL
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".
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
Superb !! . It would be really great if we get more documents on BEx like this 🙂
Thanks Sanyukta Shrivastava for your feedback..In my blogs and documents i mostly try to share some real time issues and scenarios which people faces while working on a project..
Regards,
AL
Dear Anshu....Great piece of information....
Thanks a lot vijay for your feedback.
Regards,
AL
Nice doc...very helpfull
thanks Anshu for sharing with us.
Regards,
Rishabh
Thanks Rishab for your comments.
Regards,
AL
It was really nice Anshu.
Thanks for your feeback.Much appreciated.
Regards,
AL
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 !
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
Hi Anshu,
its very good one doc.
Thanks for sharing...
Keep it up 🙂 🙂 🙂
Regards
Wasem
Good to know,that you liked it. 🙂
Regards,
AL
Hi Anshu,
Useful information thanks for sharing 🙂 .
Thanks ganesh for your feedback..You can like or rate the content i would really appreciate it.
Regards,
AL
Hi Anshu,
very useful document,nice explanation with screen shots.
Regards,
J.Sakthikumar