How to implement the below logic in obiee 11g? - Page 2 — Oracle Analytics

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

How to implement the below logic in obiee 11g?

Received Response
132
Views
21
Comments
2

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Abinash2707,

    In your screen with TODATE formule If you want to add TIME LEVEL you shoud write something like that:

    "TIME"."AD_DIM_TIMEDim"."Fiscal Year"


    Your complete formule:

    TODATE("INDSUMM FYTD"."Sales UTI", "TIME"."AD_DIM_TIMEDim"."Fiscal Year")

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi

    i have tried this...Its accepting at Edit Formula box, but getting following error while coming to result TAB.

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 22043] TODATE functions may not be nested.Please have your System Administrator look at the log for more details on this error. (HY000)

    Thanks


  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Read about Time Seriec Functions: OBIEE - Time Series Conversion Functions : AGO and TODATE [Gerardnico]

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi Cesar,

    I implemented your suggested solution...as shown below both in RPD and its output in BI answer, Here Created By is the dummy column you had asked to create in Dim_Time table and its default value is 'obi_user' .

    todate2.jpg  todate4.jpgtodate5.jpg

    Also shown the Todate function i have used in RPD to create the Logical Column "Total Sales"

    Is this approach correct?

    please suggest

    Regards,

    Abinash

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Sorry for not being clear, right know, I cannot access to rpd to take the.screenshot. however, we have to do a little change.you have to put the "created by" column in the "AD_DIM_TIME Total", and in your metric by to_date function used you have to use this level this level "AD_DIM_TIME Total".

    Kind regards,

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi Cesar,

    "AD_DIM_TIME Total" is defined as the grand total level, so i can't define any key for it. Shall I remove it as grand total level and try with it. And also if I keep it as Grand Total Level then I can not use it in Todate function.

    Please suggest.

    regards,

    Abinash

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Yes sorry, I forget create a new level as.A child of"AD_DIM_TIME Total", put inside your.new.column.And set.your chronological key. Please. Try your results in answers.

    Thank you.

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi cesar,

    That is  exactly I did last time...already shown in images in last reply..

    I am getting the output as sum of all the Years irrespective of Year. Please see the screenshot.

    Regards,

    Abinash

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Ok that's right, but why you create a measure "sales total" based in dimension all years level??? I saw a measure inside "all years" level of your hierarchy dimension time.?? Please, don't put the measure based in.level, that's the reason why you have the all sum of the years in the "sales total" column(125 323 616 950)

    Kind regards

  • AbinashBehera
    AbinashBehera Rank 5 - Community Champion

    Hi cesar,

    In your earlier reply You had asked to add the logical column to level. Is it possible to have your RPD screenshot and BI answer Screenshot for reference.

    Regards,

    Abinash