Tuesday, March 31, 2009

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.

12 comments:

dimitris said...

hi,tnks in advance.
i want by choosing 2 dates from calendar to recalculate a measure column ("quantity" let's say) in the specific period of time.But let's make it easier.
I made a repository variable named=startt-->select to_char(sysdate,'dd/MM/yyyy') from dual<--
i choose my date column for calendar in my dashboard prompt and i put startt as default,i also set a presentation variable start1
I make a simply report-->
FILTER(FACT."quantity" USING ("calendar"."cal_date" = DATE '@{start}' ))
i changed both my "greek" and english based locales as follow-->
dateshort:dd/MM/yyyy
dateorder:dmy
i put in the availables formats the previous specified.
what i am doing wrong??
please help me..
tnks one more time

Goran Ocko said...

Hi.
You didn't specify what is the problem.
Thanks.

dimitris said...

hi,tnaks for your quick reply.
I get a problem that the system do not recognize this date format.(if i put manually in the calendar the date in the format'yyyy-MM-dd' , the result is ok.But with the changes that i mentioned before i get an error.I suppose that the system recognize only the date format'yyyy-MM-dd' , so i have to change it from the begging(dashboard prompt) or try to change it in my report using concat and substring functions.
Tnks one more time.
Many thanks to Croatia.

Goran Ocko said...

Hi.

How did you make filter? Have you write it manually using 'convert this filter to sql' or in a regular way - column is equal to presentation value?

Please write exactly code that you have in filter in Answers report.

Thanks.

Goran

http://108obiee.blogspot.com/

dimitris said...

hi,
I choose my quantity column for my report.Then i click the icon "edit column formula" .It shows FACT.quantity .I changed it to --> FILTER(FACT."quantity" USING ("calendar"."cal_date" = DATE '@{start}' )) <--
where the "calendar"."cal_date" is my calendar dim dimension.
/////

If i put 2 columns , date column and quantity and use the filter "click" on date column and say that "calendar"."cal_date" = (my presentation variable) start1 ,
it works ok.

Goran Ocko said...

Hi.

Try to avoid using FILTER(expr USING filter_expressions) in column editor, it's a little bit confused and it required syntax date column=DATE 'yyyy-mm-dd'.

You do not need to put date column in the right side of the report to be able to use it in filter. Just go to the left side where your column appears and CTRL+click on the column will give you create/edit filter mode for this column.

Is it working fine now?

Regards,

Goran

http://108obiee.blogspot.com/

dimitris said...

If i can remember, it works.But my final scenario is that the user choose from calendar 2 periods of time(start1-end1 presentations variables from calendars) and(start2-end2 presentations variables from calendars)in order to effect the same "quantity" column.In other words , i want to show to my report (1)quantity for the period 1(between start1 and end1) and (2)quantity for the period 2(between start2 and end2).This i want it ,in order to compare them and other statistics
.For the other situtaions,month ago,quarter...i used ago function with success.But i have to let the user to compare 2 periods of time for a specific wuantity column.
tnks in advance.I put this question in metalink3,oracle forum , nothing..
tnks

Goran Ocko said...

Hi.

For your final scenario try this (we want to avoid an extra expression conversion of presentation variable to a date format yyyy-mm-dd):

*****Make two columns in Answers report:

1.

FILTER
(YOUR_MEASURE USING (YOUR_DATE_DIM_COLUMN between '@{start1}' and '@{end1}'))

2.

FILTER
(YOUR_MEASURE USING (YOUR_DATE_DIM_COLUMN between '@{start2}' and '@{end2}'))

Notice that I don't have YOUR_DATE_DIM_COLUMN=DATE...

Now, it's works fine and you'll get in NQQuery.log something like (my example):

/* LOGICAL */

RqList distinct
FILTER(AMOUNT_SOLD:[DAggr(SALES.AMOUNT_SOLD by [ ] )] USING TIMES.TIME_ID between '13-Jan-1999' and '13-Jan-1999') as c1 GB,
'13-Jan-1999' as c2 GB
OrderBy: c2 asc

/* PHYSICAL */

select distinct D1.c1 as c1,
'13-Jan-1999' as c2
from
(select sum(T20550.AMOUNT_SOLD) as c1
from
TIMES T20553,
SALES T20550
where ( T20550.TIME_ID = T20553.TIME_ID and T20553.TIME_ID between '13-Jan-1999' and '13-Jan-1999' )
) D1
order by c2

Oracle will use implicit conversion char to date.


*****If you have YOUR_DATE_DIM_COLUMN=DATE in FILTER(USING) and you harcode some value for a date with yyyy-mm-dd format it will generate (for example):

/* LOGICAL */

RqList distinct
FILTER(AMOUNT_SOLD:[DAggr(SALES.AMOUNT_SOLD by [ ] )] USING TIMES.TIME_ID between DATE '1998-01-01' and DATE '1998-01-01') as c1 GB,
'' as c2 GB
OrderBy: c2 asc

/* PHYSICAL */

select distinct D1.c1 as c1,
'' as c2
from
(select sum(T20550.AMOUNT_SOLD) as c1
from
TIMES T20553,
SALES T20550
where ( T20550.TIME_ID = T20553.TIME_ID and T20553.TIME_ID between TO_DATE('1998-01-01' , 'YYYY-MM-DD') and TO_DATE('1998-01-01' , 'YYYY-MM-DD') )
) D1
order by c2

This does not work with presentation variables because of yyyy-mm-dd format that is expected.

*****So you'll see that if we leave DATE in FILTER(USING) it's works only with YYYY-MM-DD so that's why you get an error if you put:

FILTER
(YOUR_MEASURE USING (YOUR_DATE_DIM_COLUMN between date '@{start1}' and date '@{end1}'))

/* ERROR */

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46047] Datetime value 19-Jun-2008 from 19-Jun-2008 does not match the specified format. (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SELECT ''19-Jun-2008'', FILTER(SALES.AMOUNT_SOLD USING (TIMES.TIME_ID between date ''19-Jun-2008'' and date ''19-Jun-2008'')) FROM "Normal model"')}

SQL Issued: SELECT '19-Jun-2008', FILTER(SALES.AMOUNT_SOLD USING (TIMES.TIME_ID between date '19-Jun-2008' and date '19-Jun-2008')) FROM "Normal model"

/* BECAUSE */

date '19-Jun-2008' is not possible

only

date '2008-06-19' is possible

/* TRY SELECT FROM ORACLE DATABASE */

select date '19-Jun-2008' from dual--not a valid month
select date '2008-06-19' from dual--this works

*****But if you test this in Answers without validating dashboard calendar prompts and you leave DATE in FILTER(USING) it's works fine because OBIEE is puting 1900-01-01 as default for all presentation variables if they are not currently populated from a prompt and then you'll have (for example):

/* LOGICAL */

RqList distinct
FILTER(AMOUNT_SOLD:[DAggr(SALES.AMOUNT_SOLD by [ ] )] USING TIMES.TIME_ID between DATE '1900-01-01' and DATE '1900-01-01') as c1 GB,
'' as c2 GB
OrderBy: c2 asc

/* PHYSICAL */

select distinct D1.c1 as c1,
'' as c2
from
(select sum(T20550.AMOUNT_SOLD) as c1
from
TIMES T20553,
SALES T20550
where ( T20550.TIME_ID = T20553.TIME_ID and T20553.TIME_ID between TO_DATE('1900-01-01' , 'YYYY-MM-DD') and TO_DATE('1900-01-01' , 'YYYY-MM-DD') )
) D1
order by c2

This is it.

The first part (the bold one) should works.

Regards,

Goran

http://108obiee.blogspot.com/

dimitris said...

hi....tnks a lot..
one last question.
in the dashboard prompt , in order to have the 4 periods of time i have to put 4 times my calendar column...
I have to format it as below??
start1:
case when 1=0 then "My_Date_Column" else date'11/11/2008'end
end1:
case when 1=0 then "My_Date_Column" else date'12/12/2008'end
.and set as default value my repository variable (made in administrator in the format'char(...,dd/MM/YYYY').
tnks one more time

Goran Ocko said...

Hi

No, this does not work:

start1:

case when 1=0 then "My_Date_Column" else date '11/11/2008' end

end1:

case when 1=0 then "My_Date_Column" else date '12/12/2008' end

Because you cannot have date 'dd/mm/yyyy'.

If you leave it you'll get an error:

Datetime value 11/11/2008 from 11/11/2008 does not match the specified format

This should work fine:

case when 1=0 then "My_Date_Column" else date '2008-11-11' end

But i preffer to use this:

case when 1=0 then "My_Date_Column" else cast ('11.11.2008' as date) end

It's the same.

Depends how many fields you'll have for each you must have different value (for example '1900-11-11' for the first, '1900-11-12', etc). This is important just for OBIEE to use this as alias for dashboard field and to populate calendar window, you wont use this value in reality.

All this you can find in my post:

http://108obiee.blogspot.com/2009/03/date-between-in-filter-and-title-when.html

You can now try to populate your default repository character variable that is in dd/mm/yyyy format into calendar field (date separator is /, date short format is dd/MM/yyyy and date order is dmy).

My advice:

Try use d.M.yyyy in date short format in localedefinitions.xml rather then dd/MM/yyyy because 1/1/2008 (d/M/yyyy) is what you will get in calendar and if you leave dd/MM/yyyy you'll have initial 01/01/2008 in calendar field and when select from a calendar you'll get 1/1/2008, that is problem. It's your choice.

For this new problem with formating calendar field and how to change date format in calendar using a javascript please read my post

http://108obiee.blogspot.com/2009/04/changing-date-format-mask-in-javascript.html

Regards,

Goran

http://108obiee.blogspot.com/

dimitris said...

tnks one more...I tried your solution but no result.I had and other work today so i will try to re-check it out tomorrow .
Tnks for your help and i post this link to oracle forums and metalink3,i beleive that it is ok from you.
Tnks ,Dimitris.

Goran Ocko said...

Hi.

Of course, you can post link!

Everything works for me fine. Try to test different format conversion in Oracle database first to see if Oracle use implicit char to date conversion for given format.

Example:

select to_date('01/01/2009') from dual

When does this error occur?

And what part doesn't work?

Have you set up prompts and default variable correctly and this works fine?

Regards,

Goran

http://108obiee.blogspot.com/