Tuesday, March 19, 2013

OBIEE date format converting to user friendly display


Convert an Oracle date to a more user friendly format in the repository

A common problem is that dates in Oracle by default and in OBIEE display everything including the minutes and seconds, users usually ask to remove that. They also ask to sometimes display only the month in a more friendly way

This is one solution in the repository using Evaluate function and the popular to_char function

Solution 1: Displaying daet as a Month

USING EVALUATE
cast(Evaluate('to_char(%1,%2)',"Inventory"."Catalog"."dbo"."Dim_W_DAY_D_Inv_Unallocated_Run_Month"."CALENDAR_DATE",'YYYYMon') as varchar(10))

cast(Evaluate('to_char(%1,%2)',<<COLUMN HERE>>,'YYYY-Mon') as varchar(10))

results


Solution 2: Getting rid of the minutes/seconds

Simply change the format in the physical layer


No comments:

Post a Comment