How to achieve Slab Wise or Bucket Wise and Age Wise requirements in BEx

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

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

Assigned Tags

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

Hi Suman,

its useful information...thanks for sharing.

It will helpful to many 🙂 .

Suman Chakravarthy K
Blog Post Author

Thanks for your valuable feedback Ganesh 🙂

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

Suman Chakravarthy K
Blog Post Author

Hey Raman,

Really appreciate your feedback. 🙂 Glad to know that you recognized my efforts as well. 🙂 . Thanks a lot 🙂

Thanks for rating it.

Regards,

Suman

Informative ℹ , Thanks For Sharing 🙂 ..

Suman Chakravarthy K
Blog Post Author

Thank you Seshu for feedback. 🙂

Its kol Suman, keep kicking

Arshiyan

Suman Chakravarthy K
Blog Post Author

Thank you Arshiyan for commenting.

For me it's a wonderful stuff in Bex for achieving such kind of result. Thanks for sharing 🙂 .

KR,

Manna Das

Suman Chakravarthy K
Blog Post Author

Delighted by your wonderful comments Manna Das 🙂 🙂 . Really motivating me..

Another Good n Informative blog ... thanks for your effort n Sharing .. 🙂

Regards

KP

Suman Chakravarthy K
Blog Post Author

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

Suman Chakravarthy K
Blog Post Author

Amazed by your heartful compliments Harish Allachervu 🙂 🙂 It's not tough to digest or follow it. Just follow my approach, you will get it..

Suman once again nice work and i need to say thanks for your efforts on neat presentation.

Suman Chakravarthy K
Blog Post Author

Regards,

Suman

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.

Suman Chakravarthy K
Blog Post Author

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 🙂

Regards,

Suman

Hi Suman,

useful information...thanks for sharing.

Regards,

Shyamala

Suman Chakravarthy K
Blog Post Author

Regards,

Suman

So nicely described.

Regards,

Sushant

Suman Chakravarthy K
Blog Post Author

Thanks a lot Sushanth for your valuable feedback 🙂

Great Job. Pls Keep it up....

Suman Chakravarthy K
Blog Post Author

Thank you Harpal for ur feedback 🙂 🙂 .

Suman Chakravarthy K
Blog Post Author

Really excited to see your comments Ravi 🙂 It's my pleasure to know my blogs are helping many people.

Hi suman really grate document and i impressed you document.

Thanks,

Phani.

Suman Chakravarthy K
Blog Post Author

Thank you kodanda pani KV Me too happy to know my blog has impressed you 🙂 🙂

Very nice presentation and thanks for your time and effort to share with all of us.

Thanks,

Umashankar

Suman Chakravarthy K
Blog Post Author

Hi suman,

Thanks for sharing this information.

Regards,

Mahesh

Suman Chakravarthy K
Blog Post Author

Amazing rating by u mahesh bondula 🙂 🙂 🙂

Hi Suman,

What a nice way to handle this scenario.

Appreciate your masterpiece work and passion.

Keep Rocking.

Thanks,

Nitesh

Suman Chakravarthy K
Blog Post Author

Hi Nitesh,

Regards,

Suman

Hi Suman,

I already rated it 🙂 .

A big thumbs up for this tremendous effort.

Waiting for your next wonder work 🙂 .

Regards,

Nitesh

Hello Suman,

Great read.. Indeed, Very helpful... Awesome article.... Nice post " BEX  ". This is a brilliant way to go.

Regards

Ashok.

Suman Chakravarthy K
Blog Post Author

Hi Ashok,

Fantastic expressions by you 🙂 Thanks for your ultimate rating.

Regards,

Suman

Hi Suman,

I already rated it 🙂 .

A big thumbs up for this tremendous effort.

Waiting for your next wonder work 🙂 .

Regards,

Nitesh

I really appreciate your effort in building this query and sharing it with the community in such a nice and well explained way. Thank you Suman 🙂

Suman Chakravarthy K
Blog Post Author

Very happy to receive nice comments from you 🙂 . Yep, I worked hard to achieve this and wanted to share with our members. Thanks for your great compliments 🙂

Regards,

Suman

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

Suman Chakravarthy K
Blog 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

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

Suman Chakravarthy K
Blog 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

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

Hi Suman, Happy new year!!!

Its simply a wonderful blog. Very clear and in simple. All your efforts are highly appreciated. Thank you for sharing.

Regards

Venkat...

Suman Chakravarthy K
Blog Post Author

Extremely happy that you liked my blog 🙂 🙂 . Thanks for rating as well.

HAPPY NEW YEAR

Regards,

Suman

Very useful information...Thanks for your efforts.

BR,

Naresh K.

Suman Chakravarthy K
Blog Post Author

Thanks

Hi suman

its too good useful information

Reagrds

Wasem

Suman Chakravarthy K
Blog Post Author

Thanks for sharing useful info..

Suman Chakravarthy K
Blog Post Author

Thank you Arvind for your feedback 🙂

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

Suman Chakravarthy K
Blog Post Author

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

nice & elaborative contatnt Suman...:)

Suman Chakravarthy K
Blog Post Author

Thanks for your valuable feedback 🙂

Regards,

Suman

Hi Suman,

Good one... Thanks for Sharing....

Regards,

Rajesh

Suman Chakravarthy K
Blog Post Author

Regards,

Suman

Hello Suman

very good documents.thanks for sharing details.

Regards

Kumar

Suman Chakravarthy K
Blog Post Author

Thank you sachin kumar for your feedback 🙂

Hi Suman,

Very nice blog. Really very useful to us... Good effort and All the best... Keep going on...

Regards,

Kokila

Suman Chakravarthy K
Blog Post Author

Thank you Kokila Praboudoure for your marvelous feedback 🙂

Regards,

Suman

Hi Suman,

A very nice blog, its really helpful 🙂 .

regards,

Arvind.

Suman Chakravarthy K
Blog Post Author

Thank you Arvind for your comment.

Hi Suman,

Really nice blog.

Regards,

Shalaka

Suman Chakravarthy K
Blog Post Author

Thank you Shalaka Golde for your comment!

Hi Suman

Good document.

Regards

Hitesh