Forum Stats

  • 3,851,567 Users
  • 2,264,000 Discussions
  • 7,904,781 Comments

Discussions

OBIEE prompts 'is between'

Hi..

My requirement is when we select start date and end date in my dashboard prompt

then the selected values should be displayed in my report also.

How could be achieve this...
Tagged:
«1

Answers

  • Madan Thota
    Madan Thota Member Posts: 839
    check this article

    http://oraclebizint.wordpress.com/2008/02/26/oracle-bi-ee-101332-between-prompts-for-date-columns-using-presentation-variables/
  • 625748
    625748 Member Posts: 124
    Dear user8732779 ,

    I have one question , is your start date and end date is from one presentation column?

    if this is the case then you can achieve this, follow the steps below.
    1. create dashboard prompt , add date column and set "Operator" = "is between".
    2. then in the report when you want to filter value based on this prompt add the above date column as filter and make this date column as "is prompted".

    This might help you.

    Regards,
    Som
  • 717867
    717867 Member Posts: 88
    Thanks for ur reply Somu..

    I can get a output based on the prompts value.

    But my requirement is when I select dates in a prompts it should be passed as a input for a narrative type report.

    Ex:
    Select the start date and end date from the prompts

    Date Between 1/1/09 and 1/31/09

    and click go button

    I need the output as follows

    Start date 1/1/09 End date 1/31/09 (Narrative report)
    Table view report

    If I use 'is equal to / is in' then I can achieve this using presentation variable. But when I select 'is between' the presentation column is disappeared in prompt creation view.


    Hope I have explained clearly...

    I am waiting for your reply..

    Thanks.
    Mohan
  • David_T
    David_T Member Posts: 2,432
    edited Aug 21, 2009 1:22PM
    Edit: I just re-read your post. To achieve what you want, you have to modify how your build your prompts. Basically, you can't use the "in between" operand for the reason you noted. Instead, use two columns in your prompt. You can use a CASE statement on the two columns you will use for your start and end dates (i.e., CASE WHEN 1=0, ...) so that you can use the same column twice (you can't pull two columns twice if you omit the CASE statement) and choose Edit Box as your type. This will allow you to save your two prompts to Presentation Variables.



    Here's how to do it. Say your two Presentation Variables (from your prompts) are called prmtStartDate and prmtEndDate.

    1) Grab two columns to your workspace; doesn't matter what columns you choose. For this example, drag the two columns so they are the first and second columns in your report.

    2) In the first column, click on the fx (column formula) button and delete what is in the window and enter '@{prmtStartDate}'

    3) Repeat for the second column, except type '@{prmtEndDate}'

    Now your prompts are in your report. You can hide the columns if you want.

    4) Go to the Narrave View and enter:

    Date Between @1 and @2

    (Note: the "1" and "2" represent the position of the columns in Criteria. That is why I said make these columns the first two in your Criteria.)

    Enter "1" in the window that controls how many times you want this value to repeat (bottom right of Narrative window).

    5) Add this view to your Compound Layout View.

    Edited by: David_T on Aug 21, 2009 7:17 AM
    David_T
  • 717867
    717867 Member Posts: 88
    edited Aug 24, 2009 4:55AM
    Hi David..
    Thanks for reply..

    What ever you told that's working fine...

    At a first time its work fine, when I change the date in my dashboard report it shows the following error

    'Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <12>: Syntax error [nQSError: 26012] . (HY000)
    SQL Issued: {call NQSGetQueryColumnInfo('SELECT 8/1/1981 12:00:00 AM, 8/31/1984 12:00:00 AM, EMPLOYEE."BLODD GROUP", EMPLOYEE.DEGREE, EMPLOYEE.DEPARTMENT, EMPLOYEE.DOB, EMPLOYEE.GENDER, EMPLOYEE.ID, EMPLOYEE.NAME FROM SH')}

    SQL Issued: SELECT 8/1/1981 12:00:00 AM, 8/31/1984 12:00:00 AM, EMPLOYEE."BLODD GROUP", EMPLOYEE.DEGREE, EMPLOYEE.DEPARTMENT, EMPLOYEE.DOB, EMPLOYEE.GENDER, EMPLOYEE.ID, EMPLOYEE.NAME FROM SH'

    I have noticed when we use the calendar or Edit box type with time (Ex:1/1/1984 12:00:00 AM) its shows the above mentioned error. If I remove the time (Ex:1/1/1984) in prompt its work fine.

    And next issue is my report not display the start date and end date in my report but its show in Narrative report correctly.

    "+1) Grab two columns to your workspace; doesn't matter what columns you choose. For this example, drag the two columns so they are the first and second columns in your report.+

    +2) In the first column, click on the fx (column formula) button and delete what is in the window and enter '@{prmtStartDate}'+

    +3) Repeat for the second column, except type '@{prmtEndDate}'+ "

    In my report it show the value '0'

    Once again thanks for your reply.. How could I overcome from this issues.


    Thanks,
    Mohan

    Edited by: user8732779 on Aug 24, 2009 1:45 AM
  • 625748
    625748 Member Posts: 124
    Dear Mohan,

    Could you please try this.
    *1.* Create dashboard prompt as given in Venkats blog.
    http://oraclebizint.wordpress.com/2008/02/26/oracle-bi-ee-101332-between-prompts-for-date-columns-using-presentation-variables/

    *2.* Then create a report with date column and a measure.

    *3.* Create narrative view, in narrative view prefix section or postfix section add the presentation variables created in step1.
    *StartDate @{date2} and EndDate @{date1}*

    with the above steps i was able to get the result what you was expecting.

    Regards,
    Som
    625748
  • 717867
    717867 Member Posts: 88
    Thanks for ur reply somu...

    I got the answer in narrative type view... But I could not calculate the date difference between date1 and date2..

    Could u post ur date diff formula field code..

    Thanks,
    Mohan
  • David_T
    David_T Member Posts: 2,432
    The easiest way to calculate the difference is this:

    1) Go to the date column you wish to perform the difference, say Account Open Date.

    2) Click on the filter button of this column.

    3) Assume that the two PVs representing the beginning and ending date of your range are prmtStartDate and prmtEndDate, respectively.

    4) In the filter, click on Add>Variable>Presentation Variable, change the operand to "greater than or equal to" and enter prmtStartDate.

    5) Repeat step 2.

    6) Repeat step 4 except this time change the operand to "less than or equal to" and enter prmtEndDate.

    This will give you the date range based on the PVs.

    If you just want the difference in the number of days, read this. It gives you a good foundation regarding dates...

    http://oraclebizint.wordpress.com/2007/12/19/oracle-bi-ee-101332-dates-dates-and-dates/
  • 717867
    717867 Member Posts: 88
    edited Aug 27, 2009 5:33AM
    Thanks for your reply David..

    Your link is very useful for me.. but it does not solve my problem

    When I use "TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘@{Date2}{1900-01-01 12:00:00}’, TIMESTAMP ‘@{Date1}{1900-01-01 12:00:00}’)" this code in my field I got the following error

    "[nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <‘1900>: Syntax error [nQSError: 26012] (HY000) "

    Could u post the calculation field code.....

    Edited by: Mohan 8732779 on Aug 27, 2009 2:33 AM
  • David_T
    David_T Member Posts: 2,432
    edited Aug 27, 2009 3:14PM
    Hmmm. One piece of advice. When pasting code in your post, use the code markup (square bracket, the word code another square bracket) and the closing one. This way we can see exactly what you are using.

    Okay, it looks like you have smart quotes on. Don' t use Word to write code and paste into OBI (assuming that is what you did); use Notepad. Anyway, change the single quotes to straight single quotes and get rid of the double quotes on each end of your code.
This discussion has been closed.