Presentation Variable scope in OBIEE 11g

3347281

Hello there,

I have a dashboard prompt with two Variable Prompts on Date field, populating two presentation variables: Current_Date_From and Prev_Date_From. The default values for these variable prompts are taken from 2 different Server variables: CURRENT_FSCL_YEAR_START_DT and PREVIOUS_FSCL_YEAR_START_DT respectively.

I use this dashboard prompt for a dashboard report (lets call it Summary). I have an action link from Summary to Details report  and I try to use the same Date field as report filter in Details report as shown here

Now the issue I am facing here is that the Date field always has the value from presentation variable Prev_Date_From. I did try to use the Current_Date_From variable in the filter, as shown below, but even it does not work and the field always have Previous year's start date.

Is this an issue with the value of the Current_Date_From being overridden by value of Prev_Date_From or with the scope of the presentation variable itself?

Any pointers / suggestions in this regard will be helpful.

Thanks

Anamika

Chris Arnold

This is one of those things that is a little hard to explain in a post, but I'll try...

For your PREVIOUS_FISCAL_START_DT prompt, go into the Formula and make it something like:

CASE WHEN 1=0 then 'ANYTHING' ELSE "Time"."Date"

For your CURRENT_FISCAL_START_DT prompt, go into the Formula and make it something like:

CASE WHEN 2=0 then 'ANYTHING' ELSE "Time"."Date"

Next, go into your reports and set the filters to be something like:

CASE WHEN 1=0 then 'ANYTHING' ELSE "Time"."Date" = @{PREVIOUS_FISCAL_START_DT}

CASE WHEN 2=0 then 'ANYTHING' ELSE "Time"."Date" = @{CURRENT_FISCAL_START_DT}

I think this will remedy your problem.

I hope this helps.

*Note - for my above CASE statements you may get an error because we are declaring static text ('ANYTHING') and a date column ("Time"."Date"). If this is the case then just set the 'ANYTHING' to instead be any column that is of the same format that "Time"."Date" is.

Gianni Ceresa

Did you try by protecting your filters?

You are affected by what is supposed to be (and actually is) a feature: if you have a prompt based on a column by default OBIEE will use that value for any filter on the same exact formula (column) in the analysis contained in the page. To avoid this "feature" which override your filter values you can protect the filter inside the analysis.

Sherry George

Like Gianni's  suggestion. You should try with protect filter on your second filter option. You can protect filter either by clicking "Protect Filter" in edit filter dialog or using "Covert this filter to SQL"

3347281 wrote:

Now the issue I am facing here is that the Date field always has the value from presentation variable Prev_Date_From. I did try to use the Current_Date_From variable in the filter, as shown below, but even it does not work and the field always have Previous year's start date.

3347281

Thanks Chris for your response, but it didn't work out for me. Any other suggestions?

3347281

Thanks Gianni for the response. Yes, I did protect the filter but no help with that too.

FTisiot

I believe it's also strange you have two prompts based on the same column "Time"."Date". I would create one of the two as "Variable prompt" (if they exist in 11g) or change the "select column" for one of them

Sherry George

Hi Anamika,

May be I didn't get the requirement completely and since the suggestions already provided are not working, could you include a bit more information about your requirement . I would like to see the filters you have put in your Summary report also. Also if possible could you also provide some screenshots of both reports including how you are drilling down to the detail report ( from the dashboard, (you can mask sensitive data as required))