Forum Stats

  • 3,837,505 Users
  • 2,262,265 Discussions
  • 7,900,308 Comments

Discussions

Presentation variable for in between date prompts

Hi,

I have a requirement where I need to pass value to presentation variables for "In between" date prompts.

Basically a user can select two dates from the same column and see the revenue between those dates.

The problem is that I can use one column only once and if I do "In between" then the "set variable" option is gone.

Kindly help me with the issue.
Thanks.

Vinay

Best Answer

  • David_T
    David_T Member Posts: 2,432
    Answer ✓
    In terms of feeding the prompts, you don't have to use the same column. It is only when you are filtering that your date column becomes important. With that, do this:

    Place two columns in the prompt workspace and use CASE WHEN 1=0 THEN... to effectively make the columns dummy columns. Use the dropdown to make both prompts "Edit box." Set the operand to "is equal to" and set the prompts to PVs: StartDate and EndDate. You can create session variables to set the default values. This method bypasses any date columns and the user types in the value. The default dates show the format users must use.

    Now in your Request, click on the filter of your date column, convert the filter to SQL and type:

    "Date Table."columnname >= '@{StartDate}' AND "Date table".columnname <= '@{EndDate}'

    This will work, but there are other options. If you want to have the user select a date from a drop down list, just use another date column that has the available dates for the user to select. Again you are just feeding the Pvs in the prompts; it's not necessary for the second column to be the same as the first one.

Answers

  • David_T
    David_T Member Posts: 2,432
    Answer ✓
    In terms of feeding the prompts, you don't have to use the same column. It is only when you are filtering that your date column becomes important. With that, do this:

    Place two columns in the prompt workspace and use CASE WHEN 1=0 THEN... to effectively make the columns dummy columns. Use the dropdown to make both prompts "Edit box." Set the operand to "is equal to" and set the prompts to PVs: StartDate and EndDate. You can create session variables to set the default values. This method bypasses any date columns and the user types in the value. The default dates show the format users must use.

    Now in your Request, click on the filter of your date column, convert the filter to SQL and type:

    "Date Table."columnname >= '@{StartDate}' AND "Date table".columnname <= '@{EndDate}'

    This will work, but there are other options. If you want to have the user select a date from a drop down list, just use another date column that has the available dates for the user to select. Again you are just feeding the Pvs in the prompts; it's not necessary for the second column to be the same as the first one.
  • Vinay .
    Vinay . Member Posts: 251
    Thanks for quick reply.

    I tried what u suggested earlier, but I am getting this error.

    [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: “test”. (HY000)
    SQL Issued: SELECT CASE WHEN 1=0 THEN Organization.”Branch Name” ELSE “test” END FROM “Cons Sector”

    i guess what you are talking about is
    http://oraclebizint.wordpress.com/2008/02/26/oracle-bi-ee-101332-between-prompts-for-date-columns-using-presentation-variables/
  • Hi...

    Venkat's post is the correct solution for your question...
    I hope the mistake you have done is you kept test in double-quotes:
    The correct SQL you need to write is:
    SELECT CASE WHEN 1=0 THEN Organization."Branch Name" ELSE 'test' END FROM "Cons Sector"

    Important thins you need to remeber is: don't copy these kind of SQLs from blog and edit as per your requirement.
    Previously i used to get syntax error because of just copying from blogs,
    Especially you need to take care of doublequotes and singlequotes, you need to override these if you copy ;)

    Hope it solve your problem...

    Thanks & Regards
    Kishore Guggilla
  • Vinay .
    Vinay . Member Posts: 251
    Thanks for your reply..
    I tried Single,double and No quotes..
    still same error..

    I also tried changing datatype to timestamp..but still same error..
  • Kishore Guggilla
    Kishore Guggilla Member Posts: 2,351
    edited Nov 15, 2008 10:56PM
    Hi...

    Please don't hesitate to provide some more information for me..

    Tell me whether Branch Name column has char datatype or not...
    And... where you are getting this error? in place of prompt w/o showing values it's showing error??
    am i correct?
    And please tell, you you cast to timestamp and for which column you did that??
    Please post more details

    Thanks & Regards
    Kishore Guggilla

    Edited by: Kishore Guggilla on Nov 16, 2008 9:25 AM
    Kishore Guggilla
  • David_T
    David_T Member Posts: 2,432
    edited Nov 16, 2008 6:06PM
    First check your prompt. The CASE statement should be written in the window that opens when you click the Edit Formula button. And when you do, do not put the FROM "Cons Sector" part. OBIEE does that for you. The SQL should end with END.

    If you don't type this correctly, then OBIEE will attempt to do an action on the column you use. So again in the prompt, type this:

    CASE WHEN 1=0 THEN "Branch Name".Name ELSE 'TEST' END

    If you are going to copy and paste code, paste in Notepad first. This gets rid of "Smart Quotes" that Word and other word processing programs use.

    Oh, unless you put default values in the filter, don't test this in the Answers. It will only work in the Dasboard.
  • Vinay .
    Vinay . Member Posts: 251
    Thanks.

    Yes I did not put FROM in the query.
    and I did it using notepad, manually and copy paste. still getting same error.
  • David_T
    David_T Member Posts: 2,432
    edited Nov 16, 2008 6:30PM
    Now we debug. First test the prompt itself. Does it work in the dashboard? Do you see the values you expect? What is the format? Timestamp or Date or CHAR?

    If that works then test the Request itself. Do you have at least one column that is not a "dummy" column? If not, you will get the "unresolved" error. If that is okay, add a default date after the PV in the filter:

    Now see if the report renders meaningful values.

    Report what you see.
    David_T
This discussion has been closed.