Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Specify time hierarchy in Period-rolling

Received Response
116
Views
7
Comments
Ebin Cherian
Ebin Cherian Rank 5 - Community Champion
edited Aug 13, 2024 5:55PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    Ebin Cherian Rank 5 - Community Champion

    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
    Gianni Ceresa Mod

    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
    Ebin Cherian Rank 5 - Community Champion

    Hi Gianni,

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

    pastedImage_0.png

    pastedImage_1.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    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
    Joel Rank 8 - Analytics Strategist

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

    pastedImage_0.png

    In the screenshot above, this would be:

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

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi Gianni,

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