Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Using 'Rank' formula in Business Objects

Using 'Rank' formula in Business Objects

Using 'Rank' formula in Business Objects

I have a crosstab in BO and I wish to select the Top 3 values from the measure in the table. I have tried using the rank function but this does not work.

My crosstab looks something like this:

Name     A    B    C    D
01/01    10        8    2
02/01        7        
03/01    1    9    6    2

I know I need to use the 'rank' formula but I cannot seem to get it to work properly.

Any help greatly appreciated!

RE: Using 'Rank' formula in Business Objects

What do you want the output to look like.

Steve Krandel

RE: Using 'Rank' formula in Business Objects

Basically the report is lots of pages. I want just the name columns with the top 3 results for each date, all other results filtered out.

Thanks for your help  

RE: Using 'Rank' formula in Business Objects

The crosstab columns are not 'independent'. They are basically the result of the intersection (and rollup of the measure). So your requirement is going to collide with the way a crosstab works. If you want the rank to work for each A,B,C,D you would want a set of tables..   

Ties Blom

RE: Using 'Rank' formula in Business Objects

I managed to do this by creating a new measure 'rank' using the rank formula.

many thanks for your suggestions.

RE: Using 'Rank' formula in Business Objects

Sorry for being obnoxious here, but.....

All I asked was for a simple example of what you wanted.  If you are going to post a problem, it helps to post the desired result.  

Then, when you finally solve the problem on your own, it is VERY valuable to the other members of the community if you post your solution.  Boards likes this are all about sharing.  So, please share your solution.   

Steve Krandel

RE: Using 'Rank' formula in Business Objects

Point taken...

Once Blom0344 pointed out the crosstab columns were not independent (Thanks Blom!) I realised I needed to create a new measure which ranked the original measure in my crosstab.

Using the variable editor I added the formula =Rank([measure];[Name];[Date])

I then applied a filter to the block filtering on my new 'rank' measure with the operator 'in list' with the values 1,2 & 3 to select only the top 3.

I hope this is of use to someone.

RE: Using 'Rank' formula in Business Objects

That's a good one.  You must be on XI R2 (or higher) to do this.  In the past you couldn't filter on the Rank.

Steve Krandel

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close