8 Replies Latest reply: Feb 26, 2013 8:53 PM by KBabu RSS

    Column Formula

    KBabu
      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
          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
            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
              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
                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
                  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
                    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
                      Hi DK,

                      Thank you..This is working..

                      Regards
                      • 8. Re: Column Formula
                        KBabu
                        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..