This discussion is archived
8 Replies Latest reply: Feb 26, 2013 6:53 PM by KBabu RSS

Column Formula

KBabu Explorer
Currently Being Moderated
Hi

I am using 10g

I have a column called HIERARCHY_DATE_WID from table A. it will display in report as '20120331' but user want to see this as 31-Mar-2012

in this table A, this column data type is DOUBLE and i cant change the datatype to DATE/TIMESTAMP as this effects some joins...!!!!!

I am trying to change at Answers level. However i made to write a SQL resulting as user requested but unable to keep this SQL in answers....can you please help me on this...

TO_CHAR(TO_DATE(to_char(HIERARCHY_DATE_WID),'YYYYMMDD'),'DD-MON-YYYY')

if i copy and past the above one in the column forumula..i am getting syntax error :(

Edited by: KBabu on Feb 25, 2013 3:12 PM
  • 1. Re: Column Formula
    DNK Explorer
    Currently Being Moderated
    You have to Use Evaluate Function in OBIEE .

    Here is the Example like how to use in OBIEE

    EVALUATE('TO_DATE(%1,%2)' AS DATE ,EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), CURRENT_DATE, 'MM/DD/YYYY'),'MM/DD/YYYY').

    please let me know the update.

    Thanks
    NK
  • 2. Re: Column Formula
    KBabu Explorer
    Currently Being Moderated
    Hi DK

    Thanks for replying

    Can we use this EVALUATE function at Answers level ?? Sorry i didnt find the EVALUATE function in answers level

    I tried in below way in Answers level, but throws syntax error

    EVALUATE('TO_DATE(%1,%2)' AS DATE ,EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "- Outlet - Geographic Hierarchy"."Geographic - Leaf Node Effective From Date", 'YYYYMMDD'),'DD-MON-YYYY')

    P.S : "- Outlet - Geographic Hierarchy"."Geographic - Leaf Node Effective From Date" is actually HIERARCHY DATE WID, contains values like (20120331, 20120430, 20120531)
  • 3. Re: Column Formula
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Try this option if you compromise with format apart from my last email

    Column Properties->Data Format->Override Default Data Format->Custom->
    Use this text ####-##-##

    This should help, if does mark as correct :)

    Edited by: Srini VEERAVALLI on Feb 26, 2013 9:10 AM
  • 4. Re: Column Formula
    DNK Explorer
    Currently Being Moderated
    Hi Babu,

    I have tried on my local..This should work in answers

    EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ),Evaluate( 'to_date(%1,%2)',Evaluate( 'TO_CHAR(%1)' as char,20090101) , 'YYYYMMDD'),'DD-MON-YYYY')

    Let me know if you have any issues.

    Thanks
    NK
  • 5. Re: Column Formula
    DNK Explorer
    Currently Being Moderated
    Hi Babu,

    I have tried on my local..This should work in answers

    EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ),Evaluate( 'to_date(%1,%2)',Evaluate( 'TO_CHAR(%1)' as char,20090101) , 'YYYYMMDD'),'DD-MON-YYYY')

    Let me know if you have any issues.

    Thanks
    NK
  • 6. Re: Column Formula
    DNK Explorer
    Currently Being Moderated
    Hi Babu,

    I have tried on my local..This should work in answers

    EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ),Evaluate( 'to_date(%1,%2)',Evaluate( 'TO_CHAR(%1)' as char,20090101) , 'YYYYMMDD'),'DD-MON-YYYY')

    Let me know if you have any issues.

    Thanks
    NK
  • 7. Re: Column Formula
    KBabu Explorer
    Currently Being Moderated
    Hi DK,

    Thank you..This is working..

    Regards
  • 8. Re: Column Formula
    KBabu Explorer
    Currently Being Moderated
    Hi Sreeni

    Thanks for you reply

    I tried this option and got 2011-03-31

    as you told to SUBSTR this by year, month, date and concatenate all 3.. I will try out this one as well

    EVALUATE function is working perfectly as of now..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points