nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Specify time hierarchy in Period-rolling

Ebin Cherian

Hi All,

Oracle Business Intelligence 12.2.1.1.0

I am using PeriodRolling function in a calculated column in RPD. I have multiple time hierarchies defined.... for example Period, Invoice Date, Due Date etc. So if I pull Period and Calculated column with PeriodRolling, result is fine. Problem happens when I include Invoice Date or Due Date to the same report. Then the PeriodRolling expression evaluates to NULL and thus the report returns null value in that column. Is it possible to associate a time hierarchy in the PeriodRolling expression ? I always want PeriodRolling to use the Period Hierarchy. I have seen a similar thread with the same issue, but its a 2 year old thread and so I just want to check if anyone got it working.

https://community.oracle.com/thread/3732313

Thanks

Ebin

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi Ebin,

Add the hierarchy you want to use as 4th parameter.

https://gerardnico.com/wiki/dat/obiee/obis/logical_sql/function_time#periodrolling

Note that the PERIODROLLING function includes a fourth optional hierarchy argument that lets you specify the name of a hierarchy in a time dimension, such as yr, mon, day, that you want to use to compute the time window. This option is useful when there are multiple hierarchies in a time dimension, or when you want to distinguish between multiple time dimensions.

So not a level of a hierarchy in a time dimension but really a reference to the hierarchy in case you have many (which seems to be your case).

Ebin Cherian

Hi Gianni,

I am struggling to add the 4th parameter to period rolling formula. It gives me below mentioned error when I add the 4th paramter.

[nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] .

Gianni Ceresa

As I don't have a running OBIEE 12c right now I only tested it in the front-end, there it allows me to use the 4th parameter in OAC. Can't say right now if the RPD has a more strict approach ...

Can you try just writing the formula in the front-end to test first?

Ebin Cherian

Hi Gianni,

I am receiving unresolved hierarchy error while I use it as 4th parameter in front end.

Gianni Ceresa

The hierarchy, not the level

"Time"."Time"."Fiscal Month" : "Fiscal Month" is a level of the hierarchy, "Time" (the 2nd is the hierarchy name), "Time" (the 1st) is the presentation table.

Try with just "Time"."Time"

Joel Acha

The Logical Level is what you should be using in the Period Rolling function

In the screenshot above, this would be:

"Usage Tracking"."Time Hierarchy"."Month"

Ebin Cherian

Hi Gianni,

It worked in OBI front end. But still I couldn't make it work in RPD.