This discussion is archived
5 Replies Latest reply: May 23, 2013 6:51 AM by Srini VEERAVALLI RSS

OBIEE 11.1.1.7.0

user12301120 Newbie
Currently Being Moderated
HI Gurus,

I got new requirement from client.

In prompt we have two columns one is Year and Qtr.

If we choose Year is 2013 then (Qtr column prompt) want to display current quarter.

If we choose Year is (not equal to Current year) then we need to display in Qtr prompt ( Max of Quarter in selected year).

Ex: 2012 Year Prompt

Quarter 4 (2012).

How to achieve this requirement, please suggest me.
     

Thanks,
  • 1. Re: OBIEE 11.1.1.7.0
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Few things first:
    Looking at your profile
    Total Questions:     36 (34 unresolved)
    I would suggest update all those posts, that helps you to get solutions for your future posts.

    Reg. current Post:
    Looks like as soon as you got requirement posted here thats cool for you.

    I would suggest try to solve your self that helps a lot...

    Your Quarter column query suppose to be something like
    SELECT MAX(Time.Quarter by Time."Year") saw_0 FROM "SA" WHERE (Time.Quarter = QUARTER_OF_YEAR(current_date)) OR (Time."Year" < Year(current_date))

    Take care of your Quarter data format values and set Constraint on Quarter column.

    Thanks
    http://cool-bi.com

    Edited by: Srini VEERAVALLI on May 23, 2013 7:12 AM
  • 2. Re: OBIEE 11.1.1.7.0
    KNYadav Explorer
    Currently Being Moderated
    Hi,

    Create two repository variable, one for storing the current year *(Current_Year)* value other one for storing the current quarter *(Current_Qtr)* value. Then in year promt store the year value in presentation variable (for this eg the presentation variable name is Var_Year). Then update the SQL for quarter prompt as below;

    Select case when @{Var_Year} = ValueOf(Current_Year) then ValueOf(Current_Qtr)
    else 'Quarter 4' end
    from Subject_area;

    Note: You can safely hardcode the else part of the query to show 'Quarter 4' as the maximum quarter will always be 4 only and if you want to disply the max qtr with year use var_year presentation variable and concate with qtr 4 value.

    Mark helpful/Correct.

    Regards,
    Kashi
  • 3. Re: OBIEE 11.1.1.7.0
    user12301120 Newbie
    Currently Being Moderated
    Hi Srini,

    Thanks for your quick response.

    I didn't get proper solutions in my previous post due to that am not able to close the threads.

    I will try to close all my pending posts.


    Your Quarter column query suppose to be something like
    SELECT MAX(Time.Quarter by Time."Year") saw_0 FROM "SA" WHERE (Time.Quarter = QUARTER_OF_YEAR(current_date)) OR (Time."Year" < Year(current_date))


    My Quarter column data is like Year-Quarter

    Ex: 2012 - Q1 , 2012-Q2,

    How to get current quarter value the above column. We can't put hardcode in where class.

    MY Prompt SQL like below.

    SELECT "D_CALENDAR_DATES"."CALENDER_QUARTER_NAME" FROM "TEST_EDW" FETCH FIRST 65001 ROWS ONLY

    If we apply sql statement in prompt, Constraint option was disabled.


    Thanks,
  • 4. Re: OBIEE 11.1.1.7.0
    user12301120 Newbie
    Currently Being Moderated
    Hi KN,

    I don't have rpd access so will try in my local system and update you shortly.

    Thanks,
  • 5. Re: OBIEE 11.1.1.7.0
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I didn't get proper solutions in my previous post due to that am not able to close the threads.
    Are you 100% dependent on otn forums for solutions? you might have solved by yourself, update that.

    Looks like you are not at all trying...

    replace(cat(YEAR(current_date) as char)||'-Q'||cast(QUARTER_OF_YEAR(current_date) as char),' ','')

    this would result something like YYYY-Qn


    have fun.

    Thanks equals no more responses

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points