Monday, February 23, 2009

OBIEE Making a distinct date prompt

One of the question I get asked on regurlar basis is: "If have a timestamp column, how do I convert this in a date only prompt".

image 

One of the possible solution is this:

First Create as truncated date column in your RPD:

image

using :

CAST( "BM_CP_TEST"."DIM_DATE_F_FACTS"."D_DATE_RANDOM"  as DATE)

Next create a prompt:

image

on the show dropdown select SQL results.

Next edit the SQL to:

SELECT DISTINCT DIM_DATE_F_FACTS.DATE_TRUNC FROM BM_CP_TEST
ORDER BY DIM_DATE_F_FACTS.DATE_TRUNC

(Don't forget the order by!)

EDIT:

image

Till Next Time

5 comments:

Anonymous said...

Hi John,

This does not give distinc values. Although it does not display timestamp but it considers the timestamp and gives the value od dates as per that.

E.g. in the example you have shown, it will show:

1/1/2006
1/1/2006
1/2/2006
1/2/2006
1/3/2006
1/3/2006
1/4/2006
1/4/2006

Thanks
Saurabh

John Minkjan said...

Hi Saurabh,

It works fine for me! (See added pic) Are you sure you have added the distinct and order by clause? Did you check the log if the correct SQL is send to the db? Are you maybe "fooled" by the cache?

regards John

Unknown said...

Its working fine john!!!

Anonymous said...

Hi

does this work even for multiselect prompts?

John Minkjan said...

@Anonymous,

this should work also. Don't forget the distinct clause.