8 Replies Latest reply: Mar 21, 2012 2:47 AM by Haree RSS

    Prompt Drop down options for 30, 60, 90 days or all

    924701
      Hi,

      I'm trying to build a report which needs a prompt with drop down options. The drop down options required are
      1. 30 day report,
      2. 60 day report
      3 90 days report or all;
      For ex: if the report month is March 2012,
      A 30 day report will pull data with an end date of 04/30/2012
      A 60 day report will pull data with an end date of 05/31/2012.
      And so on.

      This is an urgent report, so a quick help on this is much appreciated.
      Thanks,
        • 1. Re: Prompt Drop down options for 30, 60, 90 days or all
          kart
          For ex: if the report month is March 2012,
          A 30 day report will pull data with an end date of 04/30/2012
          Hi,

          the report should show the last 1 month data right? How can you show future date. i suppose the date range should be 02/01/2012 to 02/29/2012. confirm this. Post also the start date and end date for the 30, 60, 90 day frequencies.
          • 2. Re: Prompt Drop down options for 30, 60, 90 days or all
            924701
            Hi,

            Yes, it's the one month data. I just gave it as an example. Do not take it as it is!
            Another example: if the report month is say, October 2011, then,
            1. A 30 day report will pull all data with an end date of 11/30/2011,
            2. A 60 day report will pull all data with an end date of 12/31/2011,
            3. 90 day report will pull all data with an end date of 12/31/2011.
            • 3. Re: Prompt Drop down options for 30, 60, 90 days or all
              SPowell42
              Are you using OBIEE 11g or 10g?

              Thx,
              Scott
              • 5. Re: Prompt Drop down options for 30, 60, 90 days or all
                kart
                921698 wrote:
                Hi,

                Yes, it's the one month data. I just gave it as an example. Do not take it as it is!
                Another example: if the report month is say, October 2011, then,
                1. A 30 day report will pull all data with an end date of 11/30/2011,
                2. A 60 day report will pull all data with an end date of 12/31/2011,
                3. 90 day report will pull all data with an end date of 12/31/2011.
                1) Build a prompt to return values as "30 days", "60 days" and "90 days". Save them in a pres variable. e.g. date_range.
                2) Build a report. Add one dummy column to the report witht the formula as
                case when 1=0 then table_name.column_name else "30 days" end
                3) now create a filter on this dummy column with the condition as,
                column = @{date_range}{30 days}
                4) add a filter on your date column with the condition as,
                date < timestampadd(sql_tsi_month, 2, timestampadd(sql_tsi_day, -dayofmonth(currentdate), currentdate))
                5) the operator should be "AND" between the prompts created in setps 3 and 4.
                6) Now go to the dummy column which is created in step 2 and change the forumla as,
                case when 1=0 then table_name.column_name else "60 days" end
                7) now create a filter on this new dummy column with the condition as,
                column = @{date_range}{60 days}
                8) add a filter on your date column with the condition as,
                date < timestampadd(sql_tsi_month, 3, timestampadd(sql_tsi_day, -dayofmonth(currentdate), currentdate))
                9) the operator should be "AND" between the prompts created in setps 7 and 8.
                *10) this is the main step. the operator should be "OR" between the filters in step 5 and 9*
                11) Repeat the steps for the 90 days. just change the value 3 in step 8 condition to 4.

                Let me know if you face any issues in this.
                • 6. Re: Prompt Drop down options for 30, 60, 90 days or all
                  924701
                  Thanks a lot for writing down all the steps.
                  Well, as of now, I'm not able to move beyond the first step.
                  I'm trying to make a choice list of prompt to make 30 days, 60 days and 90 days to appear in the drop down.
                  But it's giving me 'ORA 932' error. Inconsistent datatype:expected DATE got CHAR.
                  I'm of the opinion that changes have to be made in the rpd. Am I right?

                  Anyway, thanks a lot for the steps. I'm sure I can get the report to work with the steps u have mentioned.
                  • 7. Re: Prompt Drop down options for 30, 60, 90 days or all
                    kart
                    But it's giving me 'ORA 932' error. Inconsistent datatype:expected DATE got CHAR.
                    In the prompt don't use your date column. Use some varchar column. the formula should be,

                    case when 1=0 then table_name.column_name else "30 days" end

                    in the show sql results, type in the below sql

                    select case when 1=0 then table_name.column_name else "30 days" end from subject_area
                    union all
                    select case when 1=0 then table_name.column_name else "60 days" end from subject_area
                    union all
                    select case when 1=0 then table_name.column_name else "90 days" end from subject_area
                    • 8. Re: Prompt Drop down options for 30, 60, 90 days or all
                      Haree
                      Hi,

                      Create a variable prompt as v_duration with options 30 Days,60 Days,90 Days.

                      Then under Edit formula of the Date column use the following code:

                      *case '@{v_duration}'*
                      when '30 Days' then cast(TIMESTAMPADD(SQL_TSI_DAY,30,cast(concat(cast (current_date as char),'12:00:00') as timestamp)) as date)
                      when '60 Days' then cast(TIMESTAMPADD(SQL_TSI_DAY,60,cast(concat(cast (current_date as char),'12:00:00') as timestamp)) as date)
                      when '90 Days' then cast(TIMESTAMPADD(SQL_TSI_DAY,90,cast(concat(cast (current_date as char),'12:00:00') as timestamp)) as date)
                      End

                      Try this.

                      Regards,
                      Haree