how to apply prompt based on description and send ID to analysis for calculation

Viswanath-Oracle

Summary:

I have Period Name as Dashboard prompt but period Number should be returned based on Period Name selected in dashboard prompt to underlying analysis.

eg., when I select Jul-21 in drop down of dashboard prompt, then Period Number 6 should be returned to my analysis for further calculations in the criteria.

Content (required):

Version (include the version you are using, if applicable):

oac

Code Snippet (add any code snippets that support your topic, if applicable):

Wayne Van Sluys

use sql in the filter referencing the presentation variable

for example

monthNum = decode( variable,'Jan',1,'Feb',2 ....)

Gianni Ceresa

when I select Jul-21 in drop down of dashboard prompt, then Period Number 6 should be returned to my analysis for further calculations in the criteria.

That's a business rule you should cover in your model somehow. The tool is smart but doesn't do any magic. How is it supposed to know that July 2021 means period number 6?

Where is the logic handling the translation? Is your time dimension source able to handle that? Is your "Fiscal Calendar" table able to handle that translation? Isn't this used in your model in a way that you can filter by "July 2021" and a join will automatically apply the "period number 6" filtering?

There are various possible ways to get there, but it depends on many things you didn't really provide in your question.

Michal Zima

I think you can leverage feature defined in metadata called Descriptor Id column (you define for "descriptive" logical column in BMM his "code" counterpart -Descriptor ID column. And if you have this descriptive column in dashboard prompt, then Id column value is passed to analysis as filter (see https://docs.oracle.com/middleware/bi12214/biee/BIEMG/GUID-681E108F-F011-4D97-B402-76697C782C4C.htm#desc_id_log_col and https://docs.oracle.com/middleware/bi12214/biee/BIEUG/GUID-EF3F4409-A734-4FD9-9967-8F027FACAEDB.htm#BIEUG589)

Viswanath-Oracle

hi @Gianni Ceresa , this is based on Calendar functionality and rpd has been built to bring in both period name and period num for same year.

Here my issue is my report has 60 unions and each union report have filters like '"Fiscal Calendar"."Accounting Period" <= (@{PERIOD}{6})' and Accounting quarter filter like "Fiscal Calendar"."Accounting Quarter" = case when @{PERIOD}{5} in (1,2,3) then 1 when @{PERIOD}{5} in (4,5,6) then 2 when @{PERIOD}{5} in (7,8,9) then 3 when @{PERIOD}{5} in (10,12,11) then 4 end

so now passing period_name like Jul-21 will make this report '"Fiscal Calendar"."Accounting Period" <= (@{PERIOD}{Jul-21})' will not work, even passing Period Number as presentation variable not working.

how to handle Jul-21 = 6 at Dashboard level rather than modifying all 60 complex unions.?

Viswanath-Oracle

Michal Zima , tried already the option of Descriptor ID column which perfectly used to work for me in 11g

but it is not returning any value to report .

please let me know if i am missing something here

Gianni Ceresa

Of course you can't pass 'Jul-21' to your filter on "Accounting Period" if that was expected to a number 1-12.

But if your time dimensions know how to handle the conversion, you can use a filter on "Month-Year" = 'Jul-21'.

I would say that the problem is anyway not there:

my report has 60 unions

No idea what your analysis is trying to do, but when you have 60+ unions to me it sounds like your model is wrong for the job you need to do. Why to model things in the RPD if you can't use them to answer questions and need to build "things" (I can't call it analysis anymore at this point) with 60+ unions?