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

OBIEE-Prompts

Received Response
2
Views
4
Comments
3421036
3421036 Rank 2 - Community Beginner
edited Aug 13, 2024 6:04PM in Oracle Analytics Cloud and Server

Hi,

We have a requirement to create prompts based for financial year. Eg: We have two prompts prompt 1 and prompt 2 on date columns.If we select value for prompt 1 as Apr-16 then prompt 2 should display the values from May-16 to March-17(i.e it should not display the selected month value of prompt 1 in prompt 2) and prompt 2 should limit to the financial year i.e march of the next year.

eg2) if prompt 1 has value as apr-15 then prompt 2 should have the values from may-15 to march-16.Please let me know if in case of any queries

Thanks in advance

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Your requirement is understandable, but what are you asking exactly? Which part couldn't you manage to do?

    What have you tested and tried so far?

  • 3424459
    3424459 Rank 1 - Community Starter

    Hi Ceresa,

    Thanks for the reply. I want to know how do I limit the values in the second prompt (i.e prompt 2) basing on prompt 1 values. In short how can I fulfill this requirement.

    1st I have tried to get the month valves greater in the prompt 2 than the prompt 1 values buy using below query. But this did not work

    SELECT "Time"."Fiscal Period" FROM "General Ledger - Journals Real Time" where   "Time"."Fiscal Period" >'@{Accounting Period Name}'

  • Gianni Ceresa
    Gianni Ceresa Mod

    Good point the LSQL to get your values isn't the most obvious one ... I have some "dirty" ways crossing my mind for now but I prefer to find something a bit more clean before to post it ...

    Let's see if others are also inspired

  • Gianni Ceresa
    Gianni Ceresa Mod

    Will already give you few bits of the possible solution ...

    You get the global idea: you set a variable in the first prompt and use it in the LSQL populating the second.

    The issue is that query you are trying to write, there are 2 things it must do:

    - limit periods for the same financial year of the period selected in first prompt

    - limit periods to periods > than the one selected

    Because of the format of your date, a text containing the month name and part of the year, it's not as simple.

    The dirty ideas crossing my mind would turn around a self join on the date dimension guessing you have a column representing the periods in the right order (so maybe 2017-01 or something like that) and that you actually use as sorting column for your textual columns. The self join would be used to translate the textual month into that sortable format and to get the year of the selected period and the self join condition would just be year = year and sortable_period > sortable_period and done.