Skip to Main Content

Month Range Selection in OBIEE against Essbase

user627522Nov 12 2012 — edited Nov 13 2012
OK. I am pretty knowledgeable on the Hyperion side, but new to the OBIEE world so I am looking for some major assistance in getting this to work.

We currently have an Essbase outline that is built with the Year and Periods combined like the below example from 2001 - 2015:

YrPeriod (generation 1)
-----2011 (generation 2)
----------201101 (generation 3)


What we are trying to do in OBIEE is build a dashboard that shows multiple reports (analysis) that are graphing 25 months of data (customer request). I have tried to:

1. use the dashboard prompt to select a Start_Month and a prompt for the End_Month. These were selecting the Gen3 members of the YrPeriod dimension and saving to a Presentation variable. This allows the users to select from the dropdown list all the Gen3 members of the dimension which I want. Those presentation variables where then selected in the BETWEEN filter for the Analysis on the YrPeriod dimension. HOWEVER, it seems to only select the first member in the range which is the START_MONTH presentation variable. Not sure if this approach will work or not.

SO I tried to do it a different way.

2. I created Session_Variables, but I am not seeing a way to use them in the BETWEEN filters on the Analysis for YrPeriod dim. It only allows you to select Presentation variables or Request Variables.The only way I have gotten them to work was to add a sql on the Initialization block for the session variable:
One FOR THE START MONTH - select to_char(add_months(sysdate,-25),'yyyymm') start_month from dual ;
AND THE END_MONTH - select to_char(add_months(sysdate,-1),'yyyymm') Ending_Month from dual AND) .

When added to the BETWEEN filter this works in getting the full 25 month range!
HOWEVER, I would like to give the users the ability to change the run period range and not be stuck with just the system date.

Ideally, I would like to have just 1 prompt for the Run period and have it update the range based on the selection.

I hope I have given you all enough info to let you know what we are trying to do and what I have tried to this point. Hopefully this is something relatively simple and I am just missing it completely!

Thank you in advance for any and all your help with this!


Edited by: user627522 on Nov 12, 2012 3:08 PM