This content has been marked as final. Show 6 replies
You need to use a server variable to determine the current year. The you can user SQL on the prompt properties to show only the years you need based on your varible (which shows current year)
Refer to: http://gerardnico.com/wiki/dat/obiee/server_variable_date
Follow the link it may be helpful to u
Try this sql in prompt show->sql results
SELECT case when 1=0 then TIMES.yearcolumn else EXTRACT( YEAR FROM CURRENT_DATE ) end FROM Subjectarea
union all SELECT case when 1=0 then TIMES.yearcolumn else (EXTRACT( YEAR FROM CURRENT_DATE )-1) end FROM Subjectarea
you can create a logical SQl like that:
SELECT case when 1=0 then cast("any attribute" as int) else YEAR(current_date) end saw_0
SELECT case when 1=0 then cast("any attribute" as int) else YEAR(current_date)-1 end saw_0
SELECT case when 1=0 then cast("any attribute" as int) else YEAR(current_date)-2 end saw_0
You can put this SQL inside the Dashboard Prompt in Show->SQL Results
I hope it helps.
There is also another technique:
You put a filter on your year column, convert it to sql and then enter some sql to just get you the most recent 3 years where data exists.
Thanks a lot... We implemented it..its working
Any suggestions for a slight variation of this. My requirement is provide a selection of any year within the past 10 years and the year prior to the one selected. So if 2009 is selected then the results needs to filter on database between 2008 and 2009.