OBIEE - BottomN function

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs) > OBIEE - Logical Sql

1 - About

The BottomN function is an aggregate function. See also the TopN function for more example and information.

The BottomN function applies a filter on the Bottom Number (See Syntax).

3 - Syntax

BottomN(Metrics,BottomNumber)

where:

  • BottomNumber represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.
Advertising

4 - Example

4.1 - Base Analysis

From the air flight demo:

SELECT
   0 s_0,
   "X - Airlines Delay"."Route"."Distance Group" s_1,
   "X - Airlines Delay"."Flight Facts"."# of Flights" s_2
FROM "X - Airlines Delay"
ORDER BY 1, 3 DESC NULLS FIRST, 2 ASC NULLS LAST
FETCH FIRST 100001 ROWS ONLY
Distance Group # of Flights
0250-0499 Miles 36,982,313
0500-0749 Miles 24,841,413
Less Than 250 Miles 21,713,534
0750-0999 Miles 17,491,483
1000-1249 Miles 10,742,987
1250-1499 Miles 4,781,436
1500-1749 Miles 4,596,821
1750-1999 Miles 2,572,119
2250-2499 Miles 2,258,349
2000-2249 Miles 1,579,532
2500 Miles and Greater 1,564,889
Advertising

4.2 - BottomN(# of Flights, 1)

Distance Group # of Flights BottomN(# of Flights, 1)
2500 Miles and Greater 1,564,889

4.3 - BottomN(# of Flights, 3)

Distance Group # of Flights BottomN(# of Flights, 1)
2250-2499 Miles 2,258,349 3
2000-2249 Miles 1,579,532 2
2500 Miles and Greater 1,564,889 1