Math Functions in OBIEE

There are many science, research and development functions which are not much explored or used in business intelligence. Most of such functions belong to Math Functions. I am listing below all Math Functions.

Math Functions

Abs, Acos, Asin, Atan, Atan2, Ceiling, Cos, Cot, Degrees, Exp, Floor, Log, Log10, Mod, Pi, Power, Radians, Rand, RandFromSeed, Round, Sign, Sin, Sqrt, Tan, Truncate

As I am not working in any scientific or research & development project, so I decided to manually create such functions, to describe math functions. I then thought to randomly select some of trigonometric functions & test result through their graph, so I choose sin, cos and tan. But for trigonometric function I had to further make use of pi (you may use degrees as well).

The functions applied in my case for different functions is as below:

Sin - SIN((RCOUNT(1)-1)*PI()/4)

Cos - COS((RCOUNT(1)-1)*PI()/4)

Tan - TAN((RCOUNT(1)-1)*PI()/4)

Angle in Degree - CEILING(DEGREES((RCOUNT(1)-1)*PI()/4))

Where RCOUNT(1)-1 gives numeric value starting from zero & 1 in RCOUNT(1) is numeric symbolizing first column with distinct data & PI()/4 is used to multiply each count to pi/4 ( quarter of semicircle).

Math Function Test
Test Maths
Calculated Attributes
Angle in Degree	Sine Value	Cos Value	Tan Value
0	0	1	0
45	0.707	0.707	1
90	1	0	6.18986E+14
135	0.707	-0.707	-1
180	0	-1	0
225	-0.707	-0.707	1
270	-1	0	2.16235E+14
315	-0.707	0.707	-1
360	0	1	0
405	0.707	0.707	1
450	1	0	1.20485E+14
495	0.707	-0.707	-1
540	0	-1	0
585	-0.707	-0.707	1
630	-1	0	9.01976E+13
675	-0.707	0.707	-1
720	0	1	0
765	0.707	0.707	1
810	1	0	6.77417E+13
855	0.707	-0.707	-1

So as verifying from the graph & tabular value, it is clear to make use of them, leaving tan pi/2 as an exception of giving a finite value. However still aviating, some more scientific calculus functions, which will make obiee full package to be used in scientific research & development projects.

J

To Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingly.