OBIEE: Evaluate

I haven't had a whole lot of opportunities to use this function. It's actually so rare, that I forget how to use it.

In the fine tradition of documenting everything, here I go.

Evaluate Syntax:

EVAULATE('your db function(%1,%2)', parameter list)

The reason I need to use it is because we are removing a view that has some fun SUBSTR/INSTR action in it. I tried writing it up in OBIEE SQL, but it doesn't allow you to traverse from the end of the string...well, someone out there probably could, but I like to limit this kind of thing in the RPD, preferring instead to put it in the database. As much as we'd like to believe it, OBIEE will not be the only application accessing this data. But I digress.

Here's the original Oracle SQL:

SUBSTR( col1, INSTR( col1, '/', -1 ) + 1 )

Which reminds me of something else...there is no real way to test this stuff in the admin tool (RPD). You have to create it there and then create an ad-hoc report to make sure it's working as it should. Rather than deal with that, I just started in Answers.

First go:

EVALUATE( 'SUBSTR( %1, INSTR( %1, '/', -1 ) + 1 )', '/TESTING/TESTING1/TESTING2/REPORT_NAME' )

This resulting in the following when I hit OK:



OK, my bet is that those single quotes inside the single quotes were the cause. So I tried variations of 2 single quotes, 3 single quotes and even double quotes. None of those worked either.

What if I just put that '/' in the parameter list?

EVALUATE( 'SUBSTR( %1, INSTR( %1, %2, -1 ) + 1 )' , '/TESTING/TESTING1/TESTING2/REPORT_NAME', '/')

Voila! Yay for me.