Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Evaluate function issue, cannot ship function

Received Response
51
Views
3
Comments
Saro
Saro Rank 5 - Community Champion
edited Aug 13, 2024 5:56PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

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

  • Gianni Ceresa
    Gianni Ceresa Mod

    '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.

  • Saro
    Saro Rank 5 - Community Champion

    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