Skip to Content

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.

To report this post you need to login first.

87 Comments

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

    1. Anshu Lilhori 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

      (0) 
  1. Anshu Lilhori 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

    (0) 
  2. Arshiyan -

    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

    (0) 
  3. Kumar S

    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 🙂 .

    (0) 
    1. Anshu Lilhori 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

      (0) 
      1. Kumar S

        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 😎 .

        (0) 
        1. Anshu Lilhori 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

          (0) 
  4. S Kaur

    Hi Anshu,

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

    Cheers

    (0) 
    1. Anshu Lilhori 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

      (0) 
  5. Birajini Devi

    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

    (0) 
    1. Anshu Lilhori 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

      (0) 
      1. Venkateswarlu Ravula

        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…

        (0) 
        1. Anshu Lilhori 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

          (0) 
  6. sudarshan reddy

    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 ?

    (0) 
    1. Anshu Lilhori 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

      (0) 
  7. Nayab hussain

    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?

    (0) 
  8. kavitha pedada

    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

    (0) 

Leave a Reply