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