DENSE_RANK as a filter in OBIEE

  When we are using other than OBIEE function in a filter, we  will be getting an error saying that, "ora-30483 window functions are not allowed here at OCI call" .

Here is the solution for DENSE_RANK in a filter.

RANK is the OBIEE funtion, so that we can use RANK in filter condition.
Criteria :

Result :

Query : Physical query for the above report from Mangae sessions as below

DENSE_RANK is not an OBIEE function, so that we use EVALUATE function.

Criteria : Get Rank1 & 2 results using DENSE_RANK

Result : 

Query : Physical query for the above report from Mangae sessions as below. Query also throws same error as Results, getting in OBIEE.

Solution : From the 1st criteria, we will get Rank 1 result. We want Rank 1 & 2. It is from 2nd criteria.

If we get the query same as 1st Query (RANK function physical query), then we can apply DENSE_RANK in a filter i.e DENSE_RANK filter should come as inner query but not in Having condition.

Apply both RANK and DENSE_RANK in a filter with "OR" operation. Issue resolved.

After applying both the filters with "OR" operation, then we can see the query same as RANK function.

Logic behind : When User want to see the TOPN results, there is no chance to enter 0 (Zero) . He should enter 1 or more than that. As we use RANK with "OR" operation, even though user enters 1 then RANK and DENSE_RANK gives the same result.