Evaluate function issue, cannot ship function

Question

Hi friends,

Im in obiee 11.1.1.7.150120 and i have the Fact called fact-capex-bi and the dimension called dim-capex-bi.

In that fact fact-capex-bi i have the column Budget and in dimension dim-capex-bi i have the column year. This year column consist of the values like FY17, FY18 etc.

My scenario is when i try to substitute below formula for the budget fact column

FILTER("Fact-Capex BI"."Budget" USING (("Dim-Capex BI"."Year" IN ('FY'||EVALUATE('TO_CHAR(%1)' AS CHAR, (EVALUATE('to_number(%1)' AS int, SUBSTRING(CAST(2017 AS  CHAR) FROM 3 FOR  2))+1))))))

The above formula filters the budget value for the year FY18(as the Evaluate expression above returns FY18). The above formula is working fine as it is filtering the budget value for FY18.

Now my problem is if i include any column from one of my other dimension ccid_Group to the above analysis im getting the issue like

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 42015] Cannot function ship the following expression: Evaluate( to_number(%1),substring(cast(2017 as CHARACTER ( 30 )) from 3 for 2)) . (HY000)

The above error is occuring only if i include a column from other dimensions ccid_Group.

Suppose if i keep only the column from fact-capex-bi and dim-capex-bi then im not getting any issue.

Not sure what could be the reason besides the issue.

Thanks in advance.

Regards,

Saro

Saro · Answer

Thanks Gianni,

I used the below like you said and it worked

FILTER("Fact-Capex BI"."Budget" USING (("Dim-Capex BI"."Year" IN ('FY'||cast(cast(substring(cast(2017 as char) from 3 for 2) as numeric)+1 as char)))))

Regards,

Saro

Gianni Ceresa · Answer

'FY' || CAST((CAST(SUBSTRING(CAST(2017 as CHAR) FROM 3 FOR 2) as INT)+1) AS CHAR)

This thing also result FY18 but doesn't depends on a database knowing what "TO_NUMBER" and "TO_CHAR" means and will work on any source.

Gianni Ceresa · Answer

Hi,

Why to use EVALUATE and making your analysis DB dependant when the default OBIEE functions could do the same job?