TOPN display function

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax:
TOPN (NumExpr, integer)

Where:
numExpr is any expression that evaluates to a numeric value.

Integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

The TOPN function resets its values for each group in the query according to specific rules.
Steps to create

To create a derived TOPN function by using Existing logical columns you perform the following steps:

• Open the Repository in Offline Mode or Online Mode, in my case I am opening in offline mode.
• Create a Calculation Measure Derived from Existing Columns
• Create a Calculation Measure Using a display Function
• Deploy the Repository in Enterprise Manager Fusion middleware control. After deploying Repository restart all the system components.
• Once all System components are restarted, create and Run an Analysis to check whether Calculation measures are working properly or not.
• Check the Query Log in Analysis whether all tables, columns and Measures are querying properly or not.

Open Repository in offline mode.

1. Select Start > Programs > Oracle Business Intelligence > BI Administration>File>Open>Offline Mode.

2) In the BMM layer, expand Sample Sales > F1 Revenue

3) Right-click F1 Revenue and select New Object > Logical Column to open the Logical Column dialog box.
4) On the General tab, enter Name in the Name field.

5) Click the Column Source tab.
6)  Select Derived from existing columns using an expression and Click the Edit Expression button to open Expression Builder.

7)     In the left pane select Functions > Display functions > TOPN Click on numeric expression.

8)     Click on Logical Tables> F1 Revenue> Revenue and click on integer type 10. Click OK.

9)     Notice that the formula is added to the Logical Column dialog box.

10)     The TOPN function is added to the BMM layer

11)     TOPN  function is dragged in presentation layer.

12)     Deploy the Repository in Enterprise Manager Fusion middleware control. After deploying Repository restart all the system components.

13)     Once all System components are restarted, create and Run an Analysis to check whether Calculation measures are working properly or not.