Friday, 2 August 2013

The secret functions in OBIEE



The secret functions in OBIEE? 

TO_DATETIME is such a function.
To be fair, you can see this function in the Administration Tool, but there is no reason not to use it in analysis directly.

What does it do? It lets you control the date format of a given date & time string and converts it. No longer the tyranny of specific formats of the cast function. For example the following returned the timestamp I needed: 
to_datetime ('01 / 08/2013 21:00 ',' dd / mm / yyyy hh: mi ')


This function Converts string literals of DateTime format to a DateTime data type. Syntax TO_DATETIME ('string1', 'DateTime_formatting_string) Where: string1 is the string literal you want to convert DateTime_formatting_string is the DateTime format you want to use, Such as yyyy. mm.dd hh: mi: ss. For this argument, Represents year yyyy, mm Represents month, dd Represents day, hh Represents hour, mi Represents minutes, and ss Represents seconds.


 
Examples SELECT TO_DATETIME ('2009-03-03 01:01:00', 'yyyy-mm-dd hh: mi: ss') FROM Snowflakesales


 
SELECT TO_DATETIME ('2009 .03.03 01:01:00', 'Yyyy.mm.dd hh: mi: ss') FROM
Snowflakesales

Few more "secret" functions:
Aggregate Functions
FIRST - selects the first non-null returned value of the expression argument.
FIRST_PERIOD - selects the first returned value of the expression argument.
GROUPBYCOLUMN - Used only in Repository.
GROUPBYLEVEL - Used only in Repository.
LAST - selects the last non-null returned value of the expression.
LAST_PERIOD - selects the last returned value of the expression.
STDDEV_POP - returns the standard deviation for a set of values ​​Using the
computational formula for population variance and standard deviation.

Math Functions
EXTRACTBIT - retrieves a bit at a particular position in an integer. It returns an integer
of 0 or 1 Either Corresponding to the position of the bit.

 Conversion Functions
CHOOSE - returns the first item in the list that the user has permission to see.
INDEXCOL - exists in Admin. Tool. Can use external information to return the appropriate column for the logged-in user to see.

Database Functions
EVALUATE_ANALYTIC - exists in Admin. Tool. This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.
EVALUATE_PREDICATE - exists in Admin. Tool. Passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

Hierarchy Navigation Functions
IDOF -
ISANCESTOR
ISCHILD
ISDESCENDANT
ISLEAF
ISPARENT
ISROOT
PARENT


No comments:

Post a Comment