Error when using TODATE(REVENUE, YEAR) Oracle SQL function

Jonathan Chan gmg

Summary

TODATE function resulted in error, what are possible alternatives to get the needed results?

Content

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)

Thank you.

Version

Oracle Business Intelligence 11.1.1.9.0

Arun Raj

Did you type in "year" or was that a member from the subject area? Is there a time hierarchy in the SA, if yes try to use "time hierarchy.Year" in the formula?

Jonathan Chan gmg

Hello Arun,

Thanks for reaching out.

1) I typed in "year".

2) There is no "time hierarchy.Year" in the subject area (SA).

3) In the SA, in "Time" folder, there is a "Year" dimension component (per enclosed screenshot).  However entering it into the TODATE() function produces the following error:

Formula syntax is invalid:
[nQSError: 10058] A general error has occurred. (HY000)
[nQSError: 43113] Message returned from OBIS. (HY000)
[nQSError: 27037] Unresolved level: "Costing - COGS and Gross Margin Real Time"."Time"."Calendar Year". (HY000)
SQL Issued: SELECT TODATE("Costing - COGS and Gross Margin Real Time"."Revenue"."Total Revenue", "Costing - COGS and Gross Margin Real Time"."Time"."Calendar Year") FROM "Distributed Order Orchestration - Fulfillment Lines Real Time"

Can you think of other approaches?

Nathan CCC

Hi,

The time series functions require presentation hierarchy. If you have no presentation hierarchy in the subject area then you cannot use the time series functions like todate and ago in your logical sql. The "Year" column you have selected is not a "dimension" but it is a "presentation column". I am using the strict terminology here from the metadata repository database (RPD). I know, it is confusing, because in an model that we would draw on a piece of paper we would talk about year as being a "dimension"!  

For example, in subject area "General Ledger - Journals Real Time" you can use "Time"."Date - Fiscal Calendar"."Fiscal Year" but you cannot use "Time"."Accounting Year" in a time series function (even if they both probably are from exactly the same logical column under the hood).

select all 0 as s_0, todate("General Ledger - Journals Real Time"."Journal Header Amounts"."Journal Control Total","Time"."Date - Fiscal Calendar"."Fiscal Year") as s_1 from "General Ledger - Journals Real Time"

For more information see

Oracle® Fusion Middleware
User's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)
E10544-08
December 2014
https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDHHGAJ

Nathan CCC

As a workaround have a look at using the aggregate function with function FILTER USING on your measure columns. So for example if you select your fact for 24 months of 2 years of data then use filter on your fact column to sum only where month is in the first 12 months (previous year to date), and another to only get the last 12 months (this year to date) etc... 

For example,

select all 0 s_0
, extract(month from "User"."Creation Date") as "Time"
, "Active Day Facts"."# of Users" as "Fact"
, rsum("Active Day Facts"."# of Users") as "Running Sum"
, sum("Active Day Facts"."# of Users") as "Sum"
, sum(filter("Active Day Facts"."# of Users" using extract(month from "User"."Creation Date")<6)) as "SumHalftoDate"
from "User System Usage"
order by 1 asc nulls last

Jonathan Chan gmg

Hello again Nathan Mogan,

Thanks for putting the time to make an example, and understanding the example was made a lot easier with the screenshot.

I will attempt to replicate this approach for my scenario.

Thank you.

Jon

 

Jonathan Chan gmg

Noted a presentation hierarchy is required in order to utilize the TODATE() function.   Thanks for introducing the metadata repository database (RPD) term "presentation column" and posting a link for reference on time series functions. 

By drawing on an hypothetical scenario with a screenshot, you've made it easy for me to see which columns work with the time series function, and which don't.

I will try migrating the suggested formula into "New Calculated Measure" box.

The only one difficult matter to this respond was to pick the best answers you provided because both are good!

Thank you.

Arun Raj

Thank you for the explanation. This totally make sense.