OBIEE Step by Step Guide

October 28, 2009

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……

 

Advertisements

Blog at WordPress.com.