Dashboard prompt between 2 different date columns

3025352

Hi all,

I'm trying to create a Dashboard prompt that will allow the users to filter the report based on 2 different date columns. We have an open date column and a closed date column. I want to allow the users to pick an open date and then a closed date that is at a later date. How would I go about setting this up?

Thank you in advance!

SriniVEERAVALLI

Check this OBIEE - Date Format in presentation variable, dashboard prompt and logical SQL [Gerardnico]

3025352

Thank you for your reply Srini. I don't think that's what I'm wanting.. Instead of having 1 date column and having an 'in between' operator on that 1 date column, I have 2 different date columns(open date and close date) and I'm trying to do an 'in between' 2 different date columns. So the user will select a value from open date column and then the user will select a value from the close date column. Does that make sense?

SriniVEERAVALLI

I got your question but I was not sure about your ability to create Prompt

You are going to use 2 dates Open and Closed in the Prompt and set Presentation variables. In the report for that 1 date column use <= and >= instead of Between operator.

3025352

Gotcha! Thank you for that information, Srini! Have a good weekend

3025352

Hi Srini I'm having difficulty with creating the 2 different date prompts. Here's the Prompts and Filters I have set up currently.

Filters:

CAST("Service Request Secondary Dates"."Open Date" AS DATE) >= date '@{OpenDate}{2016-1-1}'

CAST("Service Request Secondary Dates"."Close Date" AS DATE) >= date '@{CloseDate}{2016-1-31}'

Prompts:

CAST (Open Date AS DATE )

Column value

CAST (Open Date AS DATE )

CAST (Close Date AS DATE )

Column value

CAST (Close Date AS DATE )

I get the following error: [nQSError: 46046] Datetime value 2016-1-1 does not match the specified format.

Have any suggestions?

Thank you

SriniVEERAVALLI

default values should be like for those 2 lines

date '@{OpenDate}{2016-01-01}'

date '@{CloseDate}{2016-01-31}'

3025352

Thank you for that. that was an easy fix. It's now working but not working like I need it to. It's just using <= one date and not between the 2 dates. It's doing greater than open date but not 'and less than close date' What would we the syntax to take into account greater than open date and less than close date? I'm confused with the syntax..

SriniVEERAVALLI

CAST("Service Request Secondary Dates"."Open Date" AS DATE) >= date '@{OpenDate}{2016-01-01}'

and

CAST("Service Request Secondary Dates"."Close Date" AS DATE) <= date '@{CloseDate}{2016-01-31}'

3025352

That's working a little bit better but I'm still getting some dates that are not between the 2 dates pick. Any suggestions? See image for date results.

3025352

BTW I changed the dates to 01/01/2015 - 01/31/2015