Skip to Content

Introduction

 

Analysis of KPIs can be done in many ways during Annual Business Plans in any Business. Especially our Users require Slab Wise or Buckets Wise and Age Wise analysis of data. I am going to demonstrate these requirements in this blog in an easy manner.

 

Scenarios Covered :

 

1. Revenue Slabs in Rows along with any Char in Columns

Slab 2.JPG

This requirement can be easily achieved by Creating Buckets with New Formulas(Exception Aggregation of Counter of all Detailed values which are not Null). Your Rows definition is the driving factor to show Columns. You need not to define your Columns. There is already a beautiful document existing in SCN. You may refer the document by Neelesh Jain in http://scn.sap.com/docs/DOC-11080

 

2. Revenue Slabs in Rows along with other KFs

Slab 1.JPG

This is a tricky requirement and it is not as straight forward as Scenario 1. That’s the reason I have taken Scenario 1 to make you understand How to deal Scenario 2.

 

Please observe here that we need to define both Rows(Slabs) and Columns(other KFs) as well. How to define both at a same time? Here comes the concept of Cell Definitions which can be done on each Cell which is intersecting by Rows and Columns.

 

Concepts Used here :

 

a. Cell Definitions

b. If..else Formulas

c. Exception Aggregation

 

From the above Layout, we should achieve Order Count, Labor Rev and Parts Net as per the Rev Slabs accordingly.

 

Assume all required CKFs and RKFs are readily available in BEx. If not you may have to create them and then follow the procedure.

 

Step 1 : Go to BEx Query designer and Start creating the Query by dragging Branch into Rows Pane

Step 2 : Create a Structure in Rows Pane and name it as “Rev Slabs”. Under Structure, Create New Selections for all Slabs and just enter Descriptions like below. Need not to define them. These New Selections will just act as Descriptions and we will have to define them in Cell Definitions.

Slab 4.JPG

Finally your Structure will look like below along with Branch Char in Rows Pane.

Slab 5.JPG

Step 3 : Drag your respective Order Count Settled, Net Labor and Net Parts CKFs into KF Pane. The moment you drag, it enables “Cells” on top of Query to define Cell definitions for particular rows as per your structure defined.

 

I will show you How to Calculate Row1 i.e., < = RO 500 for each Cell. You can observe highlighted Cells.

Slab 6.JPG

Hope you all aware that when you Double Click each Cell, it takes the definition of the CKF/RKF dragged into KF pane.

 

Mistakes while coming to the right direction :

 

Your Cell Definitions will look like below after double-clicking each cell.

Slab 7.JPG

 

Are we in right direction?

No, we are not in right direction to achieve our requirement. 🙁

 

Our requirement is to show values as per Rev Slabs. So we should change our strategy now. It will be interesting now. 😉

 

Step 4 : We should bring If.. Else concept here now to consider all 3 KFs which Order Numbers Total values( which is a combination of all Components like Labor, Parts, Sublet etc..These are our business terms. Don’t get confused 😎 ) should be < = RO 500 only. I mean we should show only KF values which has Order No.s whose individual Order Value should not cross RO 500. For better understanding, an Order No which total value = RO 510 should not come here. It should come in second slab only.

 

To incorporate your own Formula/Selections in Cells, you must hide original CKFs/RKFs. Otherwise you will not be able to see them while defining Cell like below.

Slab 8.JPG

Step 5 : You must create new Formulas for 3 KPIs and define by based on first Slab i.e., < = RO 500 with If..Else conditions and Exception Aggregation like below.

Slab 10.JPG

I am considering all Components(Labor+Parts+etc.. which is nothing but Net Rev-Order)while defining “Order Count Sett”.

Net Labor will be considering only Labor Component and Net Parts will be considering only Parts Component. Please observe above image which reflects my explanation.

 

Hence your KF pane looks like below.

Slab 9.JPG

Step 6 : Now Click on Cells tab and double click on the cells for all Hidden KFs relevant as well as our first Slab relevant Cells like below.

Slab 11.JPG

Step 7 : Right click on the Cell–>New Formula–>Again right Click on the Cell–>Edit and define If..Else Formula as per 2nd Slab like below.

Slab 13.JPG

While defining our Cells, we should consider only the relevant cells which are in same row. You can make sense of above image by reading Step 5 again. All double clicked Cells will be available under Cells in Formula editor to make use them in our If..Else Formulas like below.

Slab 14.JPG

Slab 15.JPG

After defining all empty cells, your cells pane looks like below image.

Slab 16.JPG

Execute the query in Analyzer to see the final report. We have achieved our Scenario 2 requirement finally.

 

Slab 17.JPG

 

3. Age Wise Slabs of a particular Char in rows along with KFs

Slab 3.JPG

Age Wise requirements can also be achieved in a similar fashion. You must achieve the Age CKF first by based on your business logic. You can use this CKF in your If..Else conditions in Cells.

 

Conclusion : I have tried my level best to make it very clear to achieve this peculiar requirement. I am sure this is going to help us, as many clients does this kind of analysis during Annual Business Plans.

 

Thank You

To report this post you need to login first.

67 Comments

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

  1. Raman Korrapati

    First off all thanks for sharing your approach which you implemented and efforts to make a best blog on bex. Thanks lot for your valuable information.Book marked and may be handy soon.

    Thank you

    Raman

    (0) 
  2. Harish Allachervu

    peculiar scenario and nice articulation…. 😆

    have some doubts but i am trying to digest entire concept thoroughly 🙂

    and then revert you… 😕 but overall i can say thumbs up for all your efforts.

    Regards,

    Harish

    (0) 
  3. Krishna Chaitanya

    Hi Suman,

    Seems like special dedication and perseverance to get through such a beautiful, useful and informative blog.  I congratulate you and wish you the best as you embark on your next exciting another blog.  Your achievement is an example to all of us. Keep in touch!. 🙂

    Regards,

    Krishna Chaitanya.

    (0) 
    1. Suman Chakravarthy K Post author

      Hi Krishna Chaitanya,

      Yep Krishna. I have put lot of efforts to write this blog. This is the most toughest one for me to make as I compiled many screen shots with explanations. The objective is to serve all the people who encounter these requirements. This gives an instant solution to go ahead. Really happy to know that you all liked it 🙂

      Thanks for your amazing rating.

      Regards,

      Suman

      (0) 
    1. Suman Chakravarthy K Post author

      Hi Nitesh,

      Really enlightened by your wonderful comments 🙂 🙂 . I too feel great about it. My objective to make it as ready made solution for all.

      Regards,

      Suman

      (0) 
  4. Suseelan Hari

    Hi Suman,

    Good Morning!

    I can see how much effort you have put for this blog. Nice to see you like this.

    You are rocking!

    Keep it up! 😎

    Regards,

    Hari Suseelan

    (0) 
    1. Suman Chakravarthy K Post author

      Hi Hari,

      Really delighted by your comments. 🙂 My objective is to provide a ready made solution for all BEx developers. I am big fan of Bex 🙂 🙂

      Regards,

      Suman

      (0) 
      1. Suseelan Hari

        Hi Suman,

        I am happy to know that! You will be able to post many blogs related to freshers about career growth which motivates new comers in IT world and SCN. Atleast one blog, I request you to post in Career Growth. I am 100% sure that it will be very interesting to read your blogs.  😎

        You are one among the best in the SCN Members. I liked very much! 🙂

        Regards,

        Hari Suseelan

        (0) 
        1. Suman Chakravarthy K Post author

          Hi Hari,

          The problem with me is, I cannot write general non-technical blogs as you all guys do 😛 .

          Hence I am not writing in Career Forum so far. But I will try to come up with some thing interesting there 😉 . Thanks for your confidence on me.


          I am really delighted by your exciting words 🙂 🙂 🙂 . Wish you all the very best for your SAP PI.


          Advanced Happy New Year 2014 😎


          Regards,

          Suman

          (0) 
          1. Suseelan Hari

            Hi Suman,

            I bet you! You will definitely going to rock with new blogs soon!

            Thank you so much for your appreciation and wishes. 😎 😎 😎

            Regards,

            Hari Suseelan

            (0) 
  5. Anshu Lilhori

    I appreciate the efforts you have put in to share this real time scenario.Lot of concepts has been put together in it like cell definition,if else conditions..Definitely helpful for people who needs to understand complex reports and requirement.

    Regards,

    AL

    (0) 
    1. Suman Chakravarthy K Post author

      Yes ANshu!! It took some time for me to arrive to those 3 concepts to fulfill my requirement.

      It was really challenging. Really glad to receive your comments 🙂 🙂 🙂

      (0) 

Leave a Reply