SATYA RANKI REDDY OBIEE SOLUTIONS

Date format converting using Evaluate function in OBIEE 11g.

I have seen several posts in OTN. How to change date format using evaluate function in OBIEE 11g. Oracle by default date column and in OBIEE display everything including the minutes and seconds, users usually asks to remove that. Client will ask to sometimes display only the month in a more and only day format not required time stamp.

Here am providing one of solution in the repository using Evaluate function and the popular to_char function.

Solution 1:  Displaying date Year and Month.

While applying evaluate function in answers level in OBIEE 11g you will get below error.

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

SQL Issued: SELECT cast(Evaluate('to_char(%1,%2)',"D_CALENDAR_DATES"."DATE_TIME"."CALENDAR_DATE",'YYYY-Mon') as varchar(10)) FROM "PROD_EDW"

OK (Ignore Error) 

Solution:

You have to set the EVALUATE_SUPPORT_LEVEL in NQSConfig.INI file like below.

 Before modification:

After Modification:

You have to restart BI services like below.

Then you can check in the report level.

1. I am going to apply evaluate function in report level.

Click ok and click the result tab.

Thanks,

Satya Ranki Reddy