Oracle Transactional Business Intelligence
Error when using TODATE(REVENUE, YEAR) Oracle SQL function
TODATE function resulted in error, what are possible alternatives to get the needed results?
Hello Oracle Community,
The below error (per enclosed 2 screenshots) prompted when using TODATE function (per enclosed screenshot named TD) in Analysis in Oracle BI. "Total Revenue" is a fact component:
Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. (HY000)
[nQSError: 43113] Message returned from OBIS. (HY000)
[nQSError: 27037] Unresolved level: "year". (HY000)
SQL Issued: SELECT TODATE("Costing - COGS and Gross Margin Real Time"."Revenue"."Total Revenue", year) FROM "Distributed Order Orchestration - Fulfillment Lines Real Time"
1) What is the cause to this error? What would be the resolution to this error?
2) If the error cannot be overcame, what other formula could be inputted into "New Calculated Measure" box in BI to obtain the following results:
i) Year-to-Date revenue
ii) Last year's Year-to-Date revenue (e.g. 2019 year-to-date revenue)
iii) Month to Date revenue
iv) Current 12month revenue
v) Previous 12month revenue (revenue from previous 24months to 13months)
Oracle Business Intelligence 184.108.40.206.0