5 Replies Latest reply: May 23, 2013 8:51 AM by Srini VEERAVALLI RSS

    OBIEE 11.1.1.7.0

    user12301120
      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
          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
            K N Yadav
            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
              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
                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
                  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