Dense ranking for displaying top 5 Highest/Lowest Revenue companies.
Dense Ranking the companies based on their revenue (least or highest) and displaying top 5 according to the user selection. Even if there are many Number of Companies on the same rank all of them will be considered and displayed.
a. Xcelsius Dashboard(4.0 and higher)
b. SAP Business Objects Web Intelligence Rich Client(4.0 and higher)
c. SAP Business Objects Live Office (4.0 and higher)
1. Import a Business object report through Live office Connection.
2. Create a radio button filter for Conditional object (Status=High Rated or Least Rated Company)
3. Mapping the conditional filter and getting the output as per user selection
4. Ranking formulas in excel.
5. Map the destination to the bar charts.
1. Import a Business object report through Live office Connection
- Goto Live office tab and click on interactive analysis to import a BO report.
- Goto the respective report from where the data is supposed to be fetched.(make sure you have ranked the objects in the report using a dense rank formula as provided below)
- Create a object [Status] in Universe for Least or High rated company)
[Rank_Amt]= dense_rank() Over(Order by Sum(Revenue)ASC)
[Rank_Amt2]= dense_rank() Over(Order by Sum(Revenue)DESC)
*Note: [Rank_Amt] and [Rank_Amt2] are universe objects
You can refer to the link for further information on SAP BO Dense ranking
2. Import a Business object report through Live office Connection
Drag a radio button from component list.
3. Mapping the conditional filter and getting the output as per user selection.
Select Labels for the radio button from the imported BO report
Select The Source and destination for the radio Button
4. Ranking formulas in excel
Create a new column in excel(say Column L) with the following formula
(Column H contains the filtered ranks from report)
–This formula will leave a blank space where the rank is repeated
Take 5 cells in Excel which will give smallest 5 numbers out of the above formula
Say N7 to N11
N7 = SMALL(L:L,1)
N8 = SMALL(L:L,2)
N9 = SMALL(L:L,3)
N10 = SMALL(L:L,4)
N11 = SMALL(L:L,5)
-These Formulas will give the minimum 5 values out of the above formula
Create a flag Column with Formula as follows
–This formula will give value “between” for the minimum 5 values in the rank column
Now create a dummy combo box with labels as flag column
Source and destination as follows
Always give the input as “between” to this combo box. This can be done by going into properties of combo box/behavior/map the item value to text “between”
Later hide the dummy combo box by right click and send to back(it will go off the background)
5. Map the destination to the bar charts
Create a bar chart and map the dummy combo box destination cells as the axes values.
Top 5 Least Revenue companies:
Top 5 Highest Revenue companies:
***Even if there are many Number of Companies on the same rank all of them will be considered and displayed.
Refer to the below excel dashboard snapshot