AGO Function in R&A

Question

Hello

I'm trying to use the AGO function in R&A but i'm unsucessful. I either get the same values as what i have on the books or it's empty or I get the following error:

Odbc driver returned an error (SQLExecDirectW

For example I want a cancellation report with the total number of rooms cancelled on a date and want to know the same data last year.

I used "AGO("Statistics-Reservations Daily"."Room Details"."Cancelled Rooms", year, 1)" and this is how the report looks like:

and I get those results:

There are no values in cancelled rooms (and some of the market segments don't belong to the selected property but that's a different issue)

If I remove market descripion, I get that:

thank you for your help

Gianni Ceresa · Answer

I don't know what R&A is, but the fact you are building a "classic" analysis and not a DV workbook makes me believe it isn't a very new version… In current versions of OAC, it looks like it can accept generic values like "year", but this then expect the tool to guess how to handle that.

But in older versions, historically, the second parameter of the AGO function is expected to be a logical level of a hierarchy marked as a time hierarchy in your semantic model.

For example, in this presentation table named "Dim Times" there are presentation columns, there is even one named "Year". But the AGO function expects a logical level, therefore I would need to reference the level I named "Year", being part of the "Hierarchy Times", being a child of the "Dim Times" presentation table.

With this kind of semantic model / rpd, if I wanted to use AGO with the year level, the second parameter would be: "Dim Times"."Hierarchy Times"."Year" .

In your case, you need to look in your model to see if you have a time hierarchy, if it does have levels, and what's the name of the level you need. Assuming you are on a "older" system that still work with logical levels, and not a new one that apparently accept a very random "year" value, which sounds quite unstable to me as it isn't a clearly defined hierarchy level.

SabM246 · Answer

Hello Gianni

thank you very much for your feedback!

Are you able to expand more on the hierarchy you're talking about, please, as I'm not familiar; or send me a link with a detailed explanation, please?

thank you!

Gianni Ceresa · Answer

Hi,

Welcome to the Oracle Analytics and AI community.

AGO in "classic" expects the second parameter to be a time level, the name of the level you want AGO to work on in your time hierarchies in your subject area. Just "year" isn't really a level (mostly because the full name for a level would be at least "presentation table"."hierarchy"."level".

Try by using the right reference for the year level of your hierarchy, should get better.

And when you get a query error, expand the error and get the details of what was wrong, that will give more information than just knowing it failed.