Site navigation (main menu).


Rank function syntax in OBIEE

A question came up recently during an OBIEE 10g training course we were running which merits a note on the blog. As part of the training, a request was built that displayed the TopN most expensive projects within an organisation for a specified year. The question posed by a delegate was “how do I display the TopN most expensive projects across a range of years? For example, the top 3 most expensive projects on a year-by-year basis.

The output of the request should look something like this:

Year    Project  Cost    Rank
2012    A        10,000  1
2012    B        9,000   2
2012    C        8,000   3
2011    B        7,500   1
2011    C        6,000   2
2011    D        5,000   3
2010    A        9,500   1
2010    B        8,500   2
2010    E        3,000   3

This type of request is simple to construct using SQL in the Oracle database, utilising the Rank funtion with a partition by clause for the year.

When we look at the Rank function in OBIEE, the tooltip describes the function as follows:

Syntax: RANK(expr)
Description: Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,…). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7…).

Rank Function Description

What the description does not tell us is the syntax for the partition by clause. In order to add the clause, the syntax is as follows:

RANK(measure by attribute) e.g. RANK(“Fact – Project Cost”.Cost by “GL Calendar”.”Fiscal Year”)

Edit Column Formula - RANK syntax

The results of the query then match the requirement with minimul fuss:

Results table

If we interrogate the request log and look at the physical SQL query, we can identify the database Rank function in use, with the partition by clause (excuse the aliases):

Case when SAWITH0.c1 is not null then Rank() OVER ( PARTITION BY SAWITH0.c2 ORDER BY SAWITH0.c1 DESC NULLS LAST )

Hope this is useful…

20 Mar, 2012 by

Business Intelligence

One thought on: “Rank function syntax in OBIEE”

  1. Senthil November 19, 2013 10:02 AM

    It was informative.

Comments are now closed... Please contact us if you have any queries.