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. 🙂

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
        1. 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..

          (0) 
      1. 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.

        (0) 

Leave a Reply