Skip to Content
Author's profile photo Sateesh Kumar Bukkisham

Top and Bottom N ranks in a single table with one Input control in webi

Hi Team,

Purpose : How to show Top N and Bottom N records controlled by a single Input control , and show Top and Bottom records in single table ordered as per their functionality  and format them with alerts .

Desired Output

In above table top 2 records are colored with green ,ordered by descending and bottom 2 records are colored with red,ordered by ascending.

How to do :

* Create a variable that acts as Input control for ranking.

Top =2 (Default value )

* Create another variable which distinguish Top and Bottom and filter the records in table for Top and Bottom records.

Top/Bottom =If(Rank([Prod])<=[Top] ) Then “Top” Else If( Rank([Prod];Bottom)<=[Top]) Then “Bottom”


Filter the table with Top/Bottom variable inlist Top and Bottom . By default we get only Top as value for Filter , we need to add Bottom value manually to Filter.

Now we get below table . Rank column is added to table.Here values are not in order.

Next to do is to order values .(If you sort on measure /Rank we get only ascending /descending , but our purpose is to have Top values in descending and bottom values in ascending).

* Create another variable for sort

Sort =If([Top/Bottom]=”Bottom”;Max(Rank([Prod]))In Block-Rank([Prod]) ; Rank([Prod]))

Add Top/Bottom and Sort tables to table .

Do descending sort on Top/Bottom  and ascending sort on Sort column.

Hide Sort and Top/Bottom columns ( don’t delete)

Create alert to distinguish Top and Bottom records.

..Hope you’ll enjoy this trick. 🙂

Assigned tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      hi Sateesh, Thanks for sharing good information

      Author's profile photo Former Member
      Former Member

      Good document for begginers.

      Author's profile photo Lokeswara Reddy Nallajennu
      Lokeswara Reddy Nallajennu

      Nice Tip Sathish. Thanks for sharing good stuff. It is helpful for beginners...

      Author's profile photo siva kumar
      siva kumar

      Thank you very much for the article but can you please help me in "Hiding" a column in webi.. googled but unable to find a way to hide a column..

      I am using Business Objects 3.1

      Author's profile photo Sateesh Kumar
      Sateesh Kumar
      Blog Post Author

      Hi Siva ,

      There is no direct way to do. We need to create an alert , which has text and background color as white.

      http://www.dwbiconcepts.com/reporting-a-analysis/25-business-objects/134-conditional-column-hiding-bo-webi.html

      Author's profile photo siva kumar
      siva kumar

      Thanks Sathish for your quick response...

      This article is really helpful

      Author's profile photo siva kumar
      siva kumar

      I have one doubt here.... if we hide somewhere in between there will be white space that is displayed... is there any way that "Hidden part" can be moved to the end of the table..

      Author's profile photo Sateesh Kumar
      Sateesh Kumar
      Blog Post Author

      we need to insert the columns to be hidden at the end.

      Author's profile photo siva kumar
      siva kumar

      For a beginner like me it was a very helpful article..

      Thanks.

      Author's profile photo Former Member
      Former Member

      awesome!

      Author's profile photo Former Member
      Former Member

      Thank you very much for the article, it is very helpful.

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Why are the bottom two values sorted in ascending order? What's the rationale behind that?

      Author's profile photo Sateesh Kumar
      Sateesh Kumar
      Blog Post Author

      Hi Neil,

      When we apply bottom ranking we'll get values from least to highest.

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Yes, I get it, but I don't understand why? Don't you think it's a bit counterintuitive? You're showing the Top and Bottom x amount, so naturally you would expect them to be in order - so top & bottom 2 from a list of 10 you would expect 1,2,9,10. Your mechanism shows them as 1,2,10,9 - and I don't understand a business reason for wanting to display in that order? That's what I'm curious about - maybe it's a western thing, but I would expect a ranking to be in order - that's all.