Evaluate With Date Functions in OBIEE

by Kurt Wolff

If you’re not concerned about database portability – for example, you use Oracle and that’s that, forever – then the evaluate function in OBIEE can be useful. However, using the evaluate function can be tricky, and the documentation could be better.

Here’s an example of using Evaluate with the Oracle next_day function, which returns the date of the next specified day of the week following any given date. This function is useful for calculating the week ending date for any given date. If your standard week ends on Saturday, then the expression you would use would be next_day(<date>, ‘saturday’).  This is quite simple, but how do you use it within OBIEE?

Here’s a simple subject area in OBIEE which I used to test how to use next_day( ). It contains two logical tables: a Days table and a Facts table. The facts table shows how many calls occurred on any day.

As these query results show, there is one call per day.

The objective was to sum up the number of calls for each Saturday-ending week using the Oracle next_day function.

When you use the evaluate function in OBIEE, the expression builder presents you with this template:

Following the template, you would write this:

However, this syntax produced a “Union of non-compatible types” error.

I do not know what this error is really saying. Does the column name have to be enclosed in single quotes to make it a string?

The administration tool accepts this, but you get a SQL error when you use it in a query.

ORA-01858: a non-numeric character was found where a numeric was expected

The reason is obvious when you look at the SQL generated:

select next_day('"XE".""."XE"."DAYS"."DATE1"','saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day('"XE".""."XE"."DAYS"."DATE1"','saturday')
order by C1

Instead of providing a column identifier that had a date data type as an argument in the next_day function, the SQL contained a string value.

My next thought when I encountered this was to include the column name as part of the first “str_Expr”.

However, this produced another Oracle error:

ORA-01741: illegal zero-length identifier

So to fix this I edited the column identifier.

The administration tool accepted this, but it produced a different SQL error at run time:

ORA-00904: "DAYS"."DATE1": invalid identifier

The reason this is an invalid identifier is that the SQL OBIEE generated included a table alias for the DAYS table, T3392.

select next_day("DAYS"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("DAYS"."DATE1",'saturday')
order by C1

To fix this, I deleted the DAYS identifier in the function. This might work, but not if you have a DATE1 column defined in more than one table in the FROM clause. This data contained a DATE1 column both in the DAYS table and DAYSCALLS table. Therefore, the query produced another Oracle error.

ORA-00918: column ambiguously defined

To fix this, a table identifier was needed. Since OBIEE used T3386, I included this in the evaluate function.

The administration tool accepted this, and OBIEE generated correct SQL that Oracle accepted.

select next_day("T3386"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("T3386"."DATE1",'saturday')
order by C1

However, the results in Answers were not correct.

Notice that instead of getting a week ending date, we see something that looks like a month, with the sum by week presented as separate rows within the month. Even though the function next_day returns a date, OBIEE treated it as text.

To fix this, I added AS DATE in the formula to declare that the returned value was a date.

After I refreshed metadata on the presentation server, the query now produced the desired results.

Looking at column properties, everything seemed correct. The column was being treated as a date.

But look what happened – see the X axis -- when I graphed this.  The X-axis showed question marks instead of week ending dates.

After adding the graph, I examined the column properties.

The column had reverted to text!

To get OBIEE to recognize the results of the evaluate function as a date, one solution that seemed to work was to use the Oracle to_date function along with the next_day function.

Now the graph showed week ending dates on the x-axis.