Search

Custom Search

Friday, September 27, 2013

Rank

Description:

You can use rank to generate a report that display for example top 10 customers based on sales information or bottom 10 branches based on their profit. To do that in BO we have 2 Methods.

 

[Method # 1]: Apply Rank from the Webi report

In this method you can click on Apply/ Remove rank button () to add rank on the selected column.

This will open the following Wizard to set Rank properties:

Ranking Properties:

In ranking properties you can select your ranking direction. if you want to display top records in your ranked list then just tick check box beside Top. If you want to display records at bottom of your list then select Bottom. You can also enter the number of records that you want to display. for example if you select Top and entered 10 in the text box beside it then it will display top 10 records if you selected count as a calculation mode.

 

Based on:

select the measure that will be used to rank your records. You may have more than one measure displayed in your table but you can only rank them based on one of them.

 

Ranked By:

Select the dimension associated with this rank. 

 

Calculation Mode:

  • count: will stop ranking after it reach for that count. For example if you selected Top 10 with count as calculation mode it will keep displaying records on top of your list till the 10th record.
  • Percentage: 
  • Cumulative sum: it will keep displaying records till reach the limit specified in ranking properties.
  • Cumulative Percentage:

Example:

Find below the raw data that we will use in our example:

Branch Transaction Amount Transaction Volume
101 50,000 100
102 1,000,000 600
103 60,000 250
104 950,000 700
105 150,000 460
106 2,000,000 800
107 200,000 300
108 500,000 620
109 350,000 430
110 1,900,000 850
Now lets check the following ranking cases:

Case # 1:

Description: Display top 5 branches based on their transaction volume

Ranking Properties:

Top  [ 5 ]

Based on [ Transaction Volume]  Ranked by [   ]

Calculation Mode: Count

Results:

Branch Transaction Amount Transaction Volume
110 1,900,000 850
106 2,000,000 800
104 950,000 700
108 500,000 620
102 1,000,000 600

 

Case # 2:

Description: We need to display branches that performed 80% of total transaction volume

Ranking Properties:

Top  [ 80%]

Based on [ Transaction Volume]  Ranked by [   ]

Calculation Mode: Cumulative Percentage

How it Works:

Branch Amount Volume % Cum %
110 1,900,000 850 17% 17%
106 2,000,000 800 16% 32%
104 950,000 700 14% 46%
108 500,000 620 12% 58%
102 1,000,000 600 12% 70%
105 150,000 460 9% 79% **
109 350,000 430 8% 87%
107 200,000 300 6% 93%
103 60,000 250 5% 98%
101 50,000 100 2% 100%

** Limit Achieved

Results:

Branch Amount Volume
110 1,900,000 850
106 2,000,000 800
104 950,000 700
108 500,000 620
102 1,000,000 600
105 150,000 460

 

Notes:
  • We ranked our data based on Volume and we didn’t consider Transaction amount in our calculation.
[Method # 2]: Apply Rank from the Edit Query window

In this method you will retrieve only the ranked data from the database.

Useful Links: