Date between in filter and title when using presentation variable from calendar dashboard prompt or drop-down list in OBIEE

In my previous post how to change date format mask in date dashboard prompts - drop-down list and calendar I showed my solution of date format problem when using drop-down list and calendar dashboard prompt and repository variable as default. I use same prompts and localedefinitions.xml settings as in my previous post.

Now we need to create a report in Answers and then show date prompts in report title. First we use a calendar prompt for input which is in d.m.yyyy format and later in drop-down prompt which is in dd.mm.yyyy format. According to input formats our output format will be the same for both calendar and drop-down list and will be something like Amount sold from date dd.mm.yyyy to date dd.mm.yyyy. You can see that although we have two different date formats as inputs (d.m.yyyy for calendar and dd.mm.yyyy for drop-down list) there is only one format in title (dd.mm.yyyy) and it is with leading zeros for date and months values.

Example 1 - using calendar date prompts in filter and report title

Dashboard prompts:



Column formula for the first calendar date prompt:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.1900' as date) end

and for the second:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.2900' as date) end

Column formula for drop-down list date from field:



Column formula for drop-down list date to field is:

EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy')

For this example we use only calendar prompts.

We created columns in Answers as in picture:



Amount sold -> SALES.AMOUNT_SOLD

p_date_from -> '@{p_date_from}'

original presentation variable date from p_date_from from a calendar

p_date_to -> '@{p_date_to}'

original presentation variable date to p_date_to from a calendar

Variable length ->



Title date from in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

Title date to in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

We add filter on TIMES.TIME_ID column:



TIMES.TIME_ID between '@{p_date_from}{1.4.1999}' and '@{p_date_to}{1.5.1999}'

Default values are only for test in Answers.

We add narrative view as replacement for a title view because we want to include last two column fields from Answers (Title date from in dd.mm.yyyy and Title date to in dd.mm.yyyy). We call these values in title with @5 and @6.



Delete a classic title view first:



We make a dashboard page with our dashboard prompts and Answers report, so we can test it now.

Initial start:



We didn't touch calendar, we just start report with initial repository variable which is in dd.mm.yyyy format so at this first point our presentation variables p_date_from and p_date_to is filled with dd.mm.yyyy 01.01.1999 (yellow) although prompt fields are showing d.m.yyyy. (blue). We can hide last two columns (red) because we use them only in title. Green part is populated in narrative view which use last two columns (case). Title will always show date in dd.mm.yyyy format.

If we now choose value from a calendar:



We see that the yellow part is similar to dashboard prompt fields (we populated it from a calendar). Everything else is like in previous initial start part. Title will always show date in dd.mm.yyyy format.

NQQuery.log for this example:



Example 2 - using drop-down list date prompts in filter and report title

In the report that we created in the previous example we need to replace p_date_from with p_date_from2 and p_date_to with p_date_to2 in all columns that use presentation variables and also in filter, to filter our report now with drop-down list date prompts.

So no mather if we are doing initial start with repository variable or if we select value from a list everything is the same (red), the format in the title, presentation variables and drop-down list fields is in dd.mm.yyyy format already, so we don't need our CASE for dd.mm.yyyy format in title.



This is NQQuery.log for this example:



When retreiving rows in both example 1 and 2 Oracle does implicit conversion char to date so index TIMES_PK is used on TIMES.TIME_ID column.

If you run all these statements in database:

select 1 from times where time_id='1.1.1999' --example 1
select 1 from times where time_id='01.01.1999' --example 2
select 1 from times where time_id='1.01.1999'
select 1 from times where time_id='01.1.1999'

you can see that in all cases Oracle use TIMES_PK index, so implicit conversion char to date is present according to NLS settings in session/database.