Oracle Analytics Cloud and Server

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

Categories

Default Fiscal Year

Received Response
70
Views
8
Comments
3371002
3371002 ✭✭✭✭
edited August 13 in Oracle Analytics Cloud and Server

How can I develop a default fiscal year for a Fiscal Year prompt? I need it to show a default value of the current year (so the default would be 2017 now) in the Display. Right now I have a SQL Result to sort it by descending but 2017 is not set as the default.

Answers

  • Thomas Dodds
    Thomas Dodds ✭✭✭✭✭

    what does your time dimension look like?  if you have fiscal info on the date row then:

    SELECT TIME_DIMENSION.FISCAL_YEAR FROM table TIME_DIMENSION WHERE TIME_DIMENSION.DATE = CURRENT_DATE

    • CURRENT_DATE being the builtin variable value

    That would get you the fiscal year for the current date always .... depends on how well formed your dimensional table is.

  • 3371002
    3371002 ✭✭✭✭

    time dimension.PNG

    This is what my time dimension consists of

  • Gianni Ceresa
    Gianni Ceresa mod

    What Thomas posted is the cleanest and right way to do it as it also cover the special fiscal calendars (often fiscal <> normal calendar).

    Apparently your dimension stop at the month level, so it's a bit more difficult.

    You can try something like: SELECT YEAR(CURRENT_DATE) FROM "your subject area"

  • Sudhir.Sanaga
    Sudhir.Sanaga ✭

    Hi,

    Can you try this  for current fiscal year.....

    pastedImage_0.png

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    ??

  • Gianni Ceresa
    Gianni Ceresa mod

    I'm not going to say it doesn't work as there is for sure a way to make it work using EVALUATE, but if going down that way why not create a Excel file containing a macro which generate the current fiscal year and then generate a CSV file which is read by a ODI (or any other ETL tool) and loaded in a database. Then the table with the current year is loaded in the RPD and mapped into the model.

    The idea is that if OBIEE can produce the current year at the BI server level it's better than disturbing the DB and so having the BI server sending a query to the database and get the result back etc.

  • 3371002
    3371002 ✭✭✭✭

    Sorry all, I should have mentioned that SELECT YEAR(CURRENT_DATE) FROM "your subject area" worked well for me. Thanks for the other solutions, will take a look at them sometime soon.

  • Gianni Ceresa
    Gianni Ceresa mod 
    3371002 wrote:Sorry all, I should have mentioned that SELECT YEAR(CURRENT_DATE) FROM "your subject area" worked well for me.

    So you maybe have a button around to close the thread (or mark as correct or something like that)? For now it's still This question is Not Answered.