OBIEE Presentation Variable in Evaluate Function

SonPat99

Dear Gurus,

I am trying to use Presentation Variables in Evaluate Function in OBIEE.

OBIEE Version: 12.2.1.4, DB is Oracle 12c.

Evaluate Function used:

Evaluate('case when %1 = %2 then %3 end', "AAA"."Month", @{testvar}{201911}, "AAA"."Ryg")

Here, "@{testvar}" is a Presentation Variable defined in Dashboard PROMPT.

The problem is when I am using this FUNCTION, it is only taking the DEFAULT value I have defined and does not change with the PROMPT selection.

When I remove the default Value, it is taking BLANK as value.

Blogs Referred:

Using presentation variable from the first dashboard prompt as filter in the second dashboard prompt ~ OBIEE - Oracle Bu…

Oracle Business Intelligence OBIEE 101: OBIEE EVALUATE function and presentation variables

Please help me here.

Regards,

SonPat

Joel

1st question, why do you need an EVALUATE function for a CASE statement?

SonPat99

Hi Joel,

Somehow FILTER USING and CASE is not generating the correct physical SQL and hence, the values coming are not correct.

I am trying EVALUATE as an alternate approach.

My requirement is to populate values from a column dynamically depending on the values selected in the Prompt.

[Deleted User]

SonPat99 wrote:My requirement is to populate values from a column dynamically depending on the values selected in the Prompt.

That's not a requirement. That's a technical solution. What is the PROBLEM you are trying to solve. Most likely you're approaching it from a wrong direction, that's why Joel was asking.

Gianni Ceresa

Are you sure the variable is set? How did you test that @testvar is properly set?

SonPat99

@Gianni Ceresa, Yes, The variable is properly populated. I tested it by taking the variable in a single column in the same report and it populated the values dynamically once I changed the Prompts.

@Christian Berg, The Problem is: I have a trend report  for a Year and I have to display the YTD values (which are pre-calculated in DB). The logic used by developer is: If I am selecting

Month=MAR,      YTD=Value Corresponding to MAR in DB

Month=JUL,        YTD=Value Corresponding to JUL in DB

and so on....

The months change using the Dashboard PROMPT.

There are other columns to show monthly data as well in the report.

I tried using FILTER.. USING to get this, but for some reason, the SQL generated was not capturing the USING condition.

[Deleted User]

You have different physical columns storing YTD values for each month?!

SonPat99

No, a single column storing YTD values for each month in different rows. Something like below

Jerry Casey

Hi SonPat99,
This may be irrelevant to the preceding discussion, because I'm only familiar with using EVALUATE with SQL server functions, but your original statement has no ELSE clause in it.
That would also lead to a blank record.

Jerry