OBIEE-Prompts

Question

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

Gianni Ceresa · Answer

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.

Gianni Ceresa · Answer

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

3424459 · Answer

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}'