Oracle® Business Intelligence New Features Guide Release 10.1.3.4.2 Part Number E10416-07 |
|
|
View PDF |
This new feature enables users and administrators to create more powerful reports by directly calling Database functions from either Oracle BI Answers or by using a Logical column (in the Logical Table source) within the Metadata (repository).
The Oracle BI server now supports the capability to directly call functions defined within the Database from either the Answers interface or using a Logical column (in the Logical Table source) within the Metadata (repository). This feature is restricted to support SQL sources in this release.
Note:
MDX or MOLAP data sources are not supported in this release.The following new database functions are supported:
EVALUATE
For more information, see Section 6.1, "EVALUATE Function".
EVALUATE_AGGR
For more information, see Section 6.2, "EVALUATE_AGGR Function".
EVALUATE_PREDICATE
For more information, see Section 6.3, "EVALUATE_PREDICATE Function".
This function is intended for scalar and analytic calculations.
Syntax: EVALUATE('DB_Function(%1)', <Comma separated Expression>)
Example: SELECT e.lastname,sales.revenue,EVALUATE('dense_rank() over(order by %1 )',sales.revenue) FROM sales s, employee e;
This function is intended for aggregate functions with group by clause.
Syntax: EVALUATE_AGGR('DB_Aggregate_Function(%1)', <comma separated Expression)
Example: SELECT year.year, sales.qtysold, EVALUATE_AGGR('sum(%1)', sales.quantity) From SnowFlakeSales;
This function is intended for functions with a return type of Boolean.
Syntax: EVALUATE_PREDICATE('DB_Function(%1)', <comma separated Expression)
Example: SELECT year, Sales as DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_sales, 1, time, time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) FROM "Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE('OLAP_CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '') =1', OLAP_CALC) order by year;