Dense Ranking – Xcelsius Dashboard

[Former Member]

1.0  Objective

Dense ranking for displaying top 5 Highest/Lowest Revenue companies.

2.0   Description

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.

3.0 Prerequisites

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)

4.0  Procedure

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)

    =If([Status]="Least";[Rank_Amt];[Rank_Amt2])

        Where

    [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

Dense Ranking in SAP Business Objects Web Intelligence

      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

        Source:

     

        Destination:

     

4.    Ranking formulas in excel

  Create a new column in excel(say Column L) with the following formula

  L=IF(H7="","",IF(COUNTIF(H7:$H50,H7)=1,H7,"")

(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

         =IF(ISNA(IF(MATCH(H7,$N$7:$N$11,0),"between","older")),"",IF(MATCH(H7,$N$7:$N$11,0),"between”, older"))

  -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

Source:

           

Destination:

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