Oracle Business Intelligence Applications Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› More Solutions› Oracle Business Intelligence Applications Forum

Categories

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

OTBI Time Series function error

Received Response
31
Views
6
Comments
User618255-OC
User618255-OC Rank 3 - Community Apprentice
in Oracle Business Intelligence Applications Forum

Hi

Does anyone know why the OTBI function AGO("Assignment Event"."# Of Hires", "Time"."Month",1) does not work and give the error:

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27037] Unresolved level: "Time"."Time"."Month". (HY000)

SQL Issued: SELECT AGO("Assignment Event"."# Of Hires", "Time"."Time"."Month",1) FROM "Workforce Management - Worker Assignment Event Real Time"

OK (Ignore Error)

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner 
    User618255-OC wrote:[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27037] Unresolved level: "Time"."Time"."Month". (HY000)

    Is your level really "Time"."Time"."Month"?

    The level's fully qualified name must be correct, then the formula will work perfectly fine:

    pastedImage_1.png

  • User618255-OC
    User618255-OC Rank 3 - Community Apprentice

    Hi Christian

    Thanks for the reply.

    I tried  AGO("Assignment Event"."# Of Hires", "Time"."Month",1)

    and AGO("Assignment Event"."# Of Hires", "Time"."Time"."Month",1)

    and both of them did not work. The error message is a bit of a mix-up.

    Do you have the syntax that works?

    thanks

    Abhishek

  • User618255-OC
    User618255-OC Rank 3 - Community Apprentice

    Oh and the time hierarchy does not come up when you are writing the formula. It is there in the subject area but does not show up when you are in the formula box

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Your formula is syntactically correct. A little trick you can use to see the actual names is to pull in the hierarchical column into a blank analysis, open it down to the level you want and then check the XML:

    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">   <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;PC0&quot;">      <saw:columns>         <saw:column xsi:type="saw:hierarchicalColumn" hierarchyID="Time" tableName="&quot;Time&quot;" dimensionID="Time" columnID="c50b56d07a0a51dbf">            <saw:hierarchyLevels>               <saw:hierarchyLevel levelID="Total"/>               <saw:hierarchyLevel levelID="Year"/>               <saw:hierarchyLevel levelID="Quarter"/>               <saw:hierarchyLevel levelID="Month"/>               <saw:hierarchyLevel levelID="Day"/></saw:hierarchyLevels></saw:column></saw:columns></saw:criteria>
  • Gianni Ceresa
    Gianni Ceresa Mod

    Other "trick" is to expand it in the left-panel when you can select columns to add to your analysis and then edit the formula of your column and move the edit window enough on the right to still see in the background the expanded hierarchy so you clearly see the names of the levels (table name, hierarchy name, level name).

  • User618255-OC
    User618255-OC Rank 3 - Community Apprentice

    I will check these and let you know the results, thank you