LOV - Custom format for date type in list of values

building semantic layer
1

Hello,
I have found quite interesting workaround for following problem:
If you want to have some variable - i.e. dimension, type date then you should know that list of values for this specific variable will probably have timestamp format. This is not problem in table in report, you can always set specific date format there, but it is problem in prompt or when you want to use UserResponse function for this variable and wanna specific format directly from universe.

The thing is whatever you do in custom SQL in LOV, the format is still the same (this is conditioned by date type variable - at least this is behaviour on XI 3.1 and Teradata Database). Sure you can cast this date variable to char and then there is no problem with specific format, but if the table contains multi-millions of rows it is not exactly the greatest idea because casting eliminates partitioning and most likely statistics on this as well so it will result to unwanted all-scan and performance loss.
When you attempt to try cast only LOV, error about incompatible format appears. And the solution?
Create different variable,just duplicate original date variable and cast it to char. No LOV for this variable needed. In original date variable edit LOV, in selection pane choose new char date variable - that is the trick, because now you will be able to use custom SQL with changing of date type to char and specifying format - no error occurs and it works perfectly well.

If you have any question feel free to post.

AnAlienHolakres3 (BOB member since 2012-04-06)

2

The only problem with this solution you are losing sorting on the date because it is converted to string.

icotler :us: (BOB member since 2002-08-19)

3

I must disagree, there is a solution:

Instead of writing

SEL 
cast(cast(my_dt as date format 'dd.mm.yyyy') as char(10))
FROM
my_table
Group by 1
order by 1

write this clausule without GROUP BY, with DISTINCT (on teradata it does not matter since v 13.0) so:

SELECT distinct
cast(cast(my_dt as date format 'dd.mm.yyyy') as char(10))
FROM
my_table
order by my_dt desc

AnAlienHolakres3 (BOB member since 2012-04-06)

4

OK, there may be another problem - described solution works in Rich Client (WebI), but in browser light version (in non-edit mode) an error WIS 10901 (DB error “character string failed conversion to numeric value”) may occur. In that case you must cast regarding prompt as well:


mytable.date_id= cast(@Prompt('Ultimo:','D','Dimenze - WhoIsGestor\Datum',mono,constrained,Not_Persistent)   as date format 'DD.MM.YYYY'))

Prompt date format and LOV char format should be of course the same.

Hope that helps someone

AnAlienHolakres3 (BOB member since 2012-04-06)