CODEWITHSUNDEEP
sql-serverbusiness-intelligenceobieeoracle-fusion-middleware
QAIS
QAIS

Reputation: 149

OBIEE TOPN Display Function

How does TOPN Display function in OBIEE work? And how can I do it with SQL Server?

where TOPN(N,10) <= 10)

This is my query

SELECT
  N,
  SUM(column),
  REPORT_SUM(SUM(column) BY N)
FROM 
  TABLE
WHERE
  (TOPN(N,10) <= 10)

Upvotes: 0

Views: 5850

Answers (2)

Mark P.
Mark P.

Reputation: 1827

From an OBIEE perspective, the TOPN function allows OBIEE to perform a TOPN analysis. A Top 'N' Analysis refers to getting the top-n rows from a result set (ex. find the top three employees by salary). When you add a TOPN as a formula column in an answer, the data are automatically filtered. You don't need to add any special TOPN filter.

The expression is as follows: 

TOPN (n_expression, n)

where:

  • n_expression is any expression that evaluates to a numerical value.
  • n is the N of TopN which is any positive integer. It Represents the top number of rankings displayed in the result set, 1 being the highest rank.

As a note, a query can contain only one TOPN expression.

Below is a screenshot of the formula entered, and the physical SQL issued to the database from OBIEE for the formula:

Image of the TOPN formula used

SELECT DISTINCT D1.c1 AS c1, D1.c2 AS c2, D1.c3 AS c3 FROM (SELECT DISTINCT 0 AS c1, D1.c1 AS c2, CASE WHEN CASE WHEN D1.c2 IS NOT NULL THEN RANK () OVER (ORDER BY D1.c2 DESC NULLS LAST) END <= 8 THEN CASE WHEN D1.c2 IS NOT NULL THEN RANK () OVER (ORDER BY D1.c2 DESC NULLS LAST) END END AS c3 FROM ( SELECT SUM (T428861.POUND_AMT) AS c1, T428861.PURCH_ORDER_NUM AS c2 FROM WC_ASN_SHIP_F T428861 WHERE (T428861.FINAL_SAMPLE_FLG = 'Y' AND T428861.DELETE_FLG = 'N') GROUP BY T428861.PURCH_ORDER_NUM) D1) D1 WHERE (D1.c3 <= 8) ORDER BY c1

http://gerardnico.com/wiki/dat/obiee/topn

Upvotes: 1

jackohug
jackohug

Reputation: 438

See http://gerardnico.com/wiki/dat/obiee/topn

The topn function is used in the criteria column formula, but has the result of a smaller dataset. If you use topn(column, 10) your result set will only have 10 rows.

The topn function does not get passed back to the database, but an appropriate query will be sent to the database. For example with Oracle RDBMS a subquery contains the rank function and the outer query filters based on the rank.

What query does it submit to SQLServer? I don't know and perhaps someone else will be able to answer.

Upvotes: 2

Related Questions