OBIEE Functions Cheatsheet

How about this cheat sheet of OBIEE functions so you need not look around? I started with the ones that Oracle had supplied as part of the documentation.. Please comment if you like me to add more to this as I am sure I missed a lot but this is a good start 🙂

FunctionName	Type
Avg	Aggregate
AvgDistinct	Aggregate
BottomN	Aggregate
Count	Aggregate
CountDistinct	Aggregate
CountStar	Aggregate
First	Aggregate
GroupByColumn	Aggregate
GroupByLevel	Aggregate
Last	Aggregate
Max	Aggregate
Median	Aggregate
Min	Aggregate
Ntile	Aggregate
Percentile	Aggregate
PeriodAgo	Aggregate
PeriodToDate	Aggregate
Rank	Aggregate
StdDev	Aggregate
Sum	Aggregate
SumDistinct	Aggregate
TopN	Aggregate
Mavg	RunningAggregate
Msum	RunningAggregate
Rsum	RunningAggregate
Rcount	RunningAggregate
Rmax	RunningAggregate
Rmin	RunningAggregate
Ascii	String
Bit_length	String
Char	String
Char_Length	String
Concat	String
Insert	String
Left	String
Length	String
Locate	String
LocateN	String
Lower	String
OctetLength	String
Position	String
Repeat	String
Replace	String
Right	String
Substring	String
TrimBoth	String
TrimLeading	String
TrimTrailing	String
Upper	String
Abs	Math
Acos	Math
Asin	Math
Atan	Math
Atan2	Math
Ceiling	Math
Cos	Math
Cot	Math
Degrees	Math
Exp	Math
Floor	Math
Log	Math
Log10	Math
Mod	Math
Pi	Math
Power	Math
Radians	Math
Rand	Math
RandFromSeed	Math
Round	Math
Sign	Math
Sin	Math
Sqrt	Math
Tan	Math
Truncate	Math
Current_date	Calendar/Date/Time
Current_time	Calendar/Date/Time
Current_TimeStamp	Calendar/Date/Time
Day_Of_Quarter	Calendar/Date/Time
DayName	Calendar/Date/Time
DayOfMonth	Calendar/Date/Time
DayOfWeek	Calendar/Date/Time
DayOfYear	Calendar/Date/Time
Hour	Calendar/Date/Time
Minute	Calendar/Date/Time
Month	Calendar/Date/Time
Month_Of_Quarter	Calendar/Date/Time
MonthName	Calendar/Date/Time
Now	Calendar/Date/Time
Quarter_Of_Year	Calendar/Date/Time
Second	Calendar/Date/Time
TimeStampAdd	Calendar/Date/Time
TimeStampDiff	Calendar/Date/Time
Week_Of_Quarter	Calendar/Date/Time
Week_Of_Year	Calendar/Date/Time
Year	Calendar/Date/Time
Cast	Conversion
Choose	Conversion
IfNull	Conversion
IndexCol	Conversion
ValueOf	Conversion
User	Conversion
DateBase	Conversion
Case(Switch)	Expression
Case(If)	Expression

What should I do if I want to convert from one data type to another data type in OBIEE?

Answer is Use Cast function.

Well, what data types does it support?

CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING

Does Integer over Integer gives me a decimal answer ? Ex: Say I have a column say col1 which is sum of values in col1, col2 which is count of col1 rows, I want to get sum(col1) values / count(col1) row count represented in decimals, then use cast to atleast one of the values i.e. numerator or denominator so that your answer is also in decimals.

Until next time, kudos to all OBIEE evangelists

Thanks for your feedback……