Skip to Content
Author's profile photo Anshu Lilhori

How to show First and Latest Record at BEx Level

Introduction:

Lately i came across so many threads where the requirement was to show the first or latest record at bex level.I tried to replicate the same scenario in my system.Here i will be showing the sales of a particular plant on first and last day of particular month.


Data Sheet:

2.JPG

Data Loaded in DSO:

3.JPG

Query Format :

8.JPG

Here we have Converted the Characteristic 0calday into Keyfigure (Date KF) with the help of formula variable with replacement path.

Make sure to choose Unit/Dimension as Number.I am not giving a walkthrough on the steps of the same as it has been discussed so many times.

Refer the below document for step by step navigation:

Convert a Characteristic into a Key Figure (BEx)



Approach to show the First Record:

Step 1–Hit the condition tab at BEx query designer–Right click on empty space–New condition–Edit


Step 2–Hit the New tab—Keyfigures–Date KF–Operator–Bottom N–Values = 1.Then Hit the Transfer tab.Refer the below screenshot.

9.JPG

Step 3–Hit the Characteristic Assignment Tab..Choose the options as per the screenshot below.

7.JPG

Step 4–Save the condition and Query.

Query Output: To show first Record

10.JPG

Approach to show the Latest Record:


All the steps to get the latest record will be same except with the slight change in conditions tab.

Instead of Operator Bottom N we will choose Top N.Refer the below screenshot.

12.JPG

Query Output:To show Latest Record

11.JPG

Conclusion:It’s apparent that after following the above approach we got the desired result as per our data stored in DSO.

Hope this helps.All suggestions and comments are welcome.

Assigned Tags

      87 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Nice efforts Anshu 🙂 . It seems you have observed yesterday's post. Appreciate your step by step approach..

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Suman.I was planning to share this approach since few days and recently i saw so many posts on the same so it was high time to replicate the scenario and give a write up.

      Regards,

      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      This can avoid exception aggregation, right?

      Author's profile photo CH Raman
      CH Raman

      Good content and neat explanation. Thanks for your ideas and efforts.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks raman for your valuable feedback..Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      USeful INFO

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Giri..for your feedback.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      I guess your reply got posted multiple times.Yes Exception aggregation is not required and in case where you need the output like this then Exception aggregation is not going to help.

      It may show the last and first record in result but if you drill down on calday as i have kept then it will show all the records.

      Regards,

      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      You can post this to Nidhi's question.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Nice work around anshu.. keep it up..kepp exploring bex and share us..

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks again for your comments..Yes sure will keep sharing.. 🙂

      Regards,

      AL

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Nice Idea indeed. 😉

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hello Anhu

      Thanks for blog. It is knowledgeable for those who haven't work yet BEX and ofcourse for the new SCN users. keep it up a.d thanks or sharing!

      Arshiyan

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Arshiyan..really appreciate your feedback.

      Regards,

      AL

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Thanks for sharing anshu.......NIce blog and very helpful...... 🙂

      Regards,

      SM

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Satendra for your feedback.Really appreciate.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice documentation Anshu; It will be helpful to fufill business requirements.

      If possible, you just include a link you have for 'Converted the Characteristic 0calday into Keyfigure', So that your blog itself entire solution for this type of requirement.

      Your knowledge share is the solution for many SAP people. Continue the spirit 🙂 .

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sunny for your valuable feedback..Glad to receive such great response from you guys.. 🙂

      As you requested to add the link so will do that shortly.

      Regards,

      AL

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Sunny..Link has been updated..

      Author's profile photo Former Member
      Former Member

      wow...Thanks. When one person asked about some sample code, you are checking in your system regarding its working nature and providing it. That much responsibility will give response right. Its your content share response 😎 .

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Yes Sunny,if time permits then i try to test it on my system as it gives me the clarity of the scenario and i understand the person who posts ABAP queries is novice so its better if i post the complete code with proper explanation.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice Presentation Anshu..Keep Posting ..Good Work.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Naveen for your feedback..Yes will come up with more interesting scenarios soon

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Really nice way of tackling this requirement.

      Regards,

      Ashutosh

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Ashutosh for your valuable feedback..Much appreciated.

      Regards,

      AL

      Author's profile photo Arvind Doomra
      Arvind Doomra

      Thanks for sharing

      Regards,

      arvind

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Arvind for your feedback..Much Appreciated.

      Regards,

      AL

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Nice workaround Anshu.  Great job  keep it up and keep going.  I think it will be helpful for yesterdays post.

      Cheers 🙂 🙂 🙂 🙂 🙂

      Krishna Chaitanya.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Krishna for your feedback and your smileys usually makes me more than happy. 🙂

      Regards,

      AL

      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      NIce one Anshu! i am sure this info will come in handy someday.

      It shows the level of effort you take to find the solution:)

      Regards,

      Benedict

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Benedict..For your cheerful comments..I just try to share across my knowledge with you guys.

      Regards,

      AL

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Nice effort n detailed explanation... Good job.

      Bookmarked and rated the blog... Thanks for sharing..

      Regards

      KP

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KP for your feedback..Nevertheless thanks for rating it as well. 🙂

      Regards.

      AL

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Good One, Anshu...

      BR

      Prabhith

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Prabhith for your valuable feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice Document.

      Regards,

      Sushant

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sushant for your valuable feedback..Really appreciate your comments. 🙂

      Author's profile photo Sherif Elkoumi
      Sherif Elkoumi

      very helpful and valuable information, Anshu 🙂 thanks a lot

      best regards,

      Sherif

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sherif for your feedback..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      One more nice document released from anshu's hand ! 🙂

      Thanks for sharing Anshu.

      Regards,

      Venu

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your kind words and appreciation Venu.. 🙂

      My pleasure.

      Regards,

      AL

      Author's profile photo Sandeep a
      Sandeep a

      Thanks for sharing this with us

      regards

      Sandeep

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for the feedback sandeep.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Your ways mostly give  "Business Oriented solutions" instead a technical solution which a true consulting..instead of being just developer!!!

      Cheers

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Kaur,

      Really pleased with your feedback..I always try to replicate the scenario which people post in forums and if i get some concrete solution then give it a write up.It makes sense to me.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice creativity Anshu. 🙂

      I'd like to discuss one senario related to this .

      Support we have three prodcuts A,B,C.

      Someone purchased A on 13th ,B on 14th ,C on 16th then A on 17th date.

      So if we run the report the latest transaction should come as 17th date.

      how we can implement this logic  in reporting level?

      Can we implement the same logic for this senario ? Need your suggestion.

      Regards,

      B Devi

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Devi for your feedback..

      Coming to your question yes it should work and show you the latest record only if you follow the document.

      In case any particular issue then please raise the question in forum people might come up with their ideas.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Nice document. Appreciate your effort.

      Thanks

      BR

      Aakash G

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Akash for your feeback.

      Regards,

      AL

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi Anshu,

      Good Information and nice presentation 🙂

      Regards,

      Ganesh Bothe

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Ganesh for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Thank you Anshu! Very good explanation and pretty clear.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Venkateswaralu for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Now, I have a same kind of user req but not exactly the same. I have to show 1st GRN date of an article/material. Here, we are getting the Goods receipt qty as key figure and movement type as character. Based on this KF & CHAR combination we are calculating GRN quantity. Could you please advise how to calculate 1st GRN date of an article/material at BEx level. Your assistance is highly appreciable and obviously, helpful a lot. Thank you in advance!.

      Best Regards

      Venkat...

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      I would appreciate if you can open this query as a new discussion so that others also can give some inputs on the same.

      You have not mentioned anything about GRN date which should be the basis of your whole calculation.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Ok, thank you. I will open a new discussion on this.

      Regards

      Venkat...

      Author's profile photo Former Member
      Former Member

      Nice presentation, appreciate your efforst.. 🙂

      Thanks for sharing.. 🙂 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Prerna..Appreciate your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good work...nice demo..:)

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Naveen..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Thank you for taking the time, for sharing your experience in BEx.

      and Clarify:

      Mentioned Kayfigure –Sales  in Step 2, Keyfigure -Date KF in screenshot ?

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Sudarshan,

      Thanks for the feedback.It is type error,this should be date not sales.I will amend the blog.

      Great that you noticed that.

      Regards,

      AL

      Author's profile photo SVU 123
      SVU 123

      HI Anshu,

      Good explanation ..Nice Workaround !! 

      rgds

      SVU123

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks SUV123 appreciate your feedback.

      Regards,

      AL

      Author's profile photo Vijay Chandra.R
      Vijay Chandra.R

      Anshu is Rocking.... Well done sirji...

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks a lot Vijay for your rocking feedback..Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Again a valuable edition in Bex part. This will be a valuable addition to my knowledge in Bex. Thanks for sharing.

      KR,

      Manna Das

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Manna for your feedback..I am glad that my blogs are source of knowledge to others.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good point and clearly presented ! This is very helpful... thanks for sharing Anshu !

      Regards,
      Siva

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Saravana Siva for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,


      Nice documentation, It will be helpful to fufill business requirements.


      Thanks,

      Chandresh Patel

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Good to know that..Thanks for your comments.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Very helpful.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      I am glad that this blog is helpful for you. 🙂

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Handy information.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feedback Giri.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hello Anshu,

      Good to know the trick you applied, on date to get latest and first.

      Easy method to solve complex issue 😎

      Thanks for sharing!

      LS

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Limbadri for spending time in reading my blog.Glad that you liked it as well.

      Regards,

      AL

      Author's profile photo Pavan Kumar K R
      Pavan Kumar K R

      Nice Document Anshu. Thanks for sharing.

      Hoping more & more such scenario-oriented documents from you.

      -- KRPK

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Pawan.I am glad that you liked it.Yes will try to post some more useful document.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Like this trick .Happy to read 🙂 .

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Good to know that..Thanks for the feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshul,

      If in characteristic assignment  i choose "All characteristics in the drilldown independently" then the condition will work for all the characteristics . Does that mean i have to convert all the characteristics into key figure?

      Author's profile photo Former Member
      Former Member

      Anshul,

      Thanks for sharing this Document  it is easy to recollect and Execute.

      Regards

      PB

      Author's profile photo Suresh Vemulapalli
      Suresh Vemulapalli

      Really great !!! Thanks for sharing Hidden tricks.

      Author's profile photo Former Member
      Former Member

      Hi Anshul,i very urgent requirement:

      Data:

      Test_data.JPG

      In Formula1 column was expecting Latest record, but followed everything in the document.

      Please help to fetch correct information

      looking for first and last records(based on 0calday) in two columns as Formulas or cfks

      Author's profile photo Former Member
      Former Member

      Nicely explained. Useful info.