Wednesday, April 15, 2009

How to display description in the prompt and filter by id

There was a question on OTN forum how to display description in the dashboard prompt by selecting corresponding id and use it in filter. Maybe there is much easier solution then this so if someone knows please let me know.

We will have only one dashboard prompt so there is no need to have two prompts, one for description and one for id and separate GO buttons. This example is based on displaying CALENDAR_YEAR and using CALENDAR_YEAR_ID in filter.

This example only for a test purpose. So I didn't test performance.

Make a dashboard prompt with CALENDAR_YEAR column:

Answers report:

You see this in filter on CALENDAR_YEAR_ID column:


I created GET_ID_FROM_DESC function in Oracle database (in my case I use HR and SH schema) and use it in EVALUATE function. This function returns CALENDAR_YEAR_ID for CALENDAR_YEAR as input.

The function code:

create or replace function get_id_from_desc(p_desc varchar2)
return number DETERMINISTIC
v_id number;
select distinct calendar_year_id into v_id from sh.times where calendar_year=p_desc;
return v_id;
exception when others then null;

Maybe you'll need to create a function-based index on GET_ID_FROM_DESC(CALENDAR_YEAR), that's way I put DETERMINISTIC clause in function.

I found this information about DETERMINISTIC clause in

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.

If you not specify DETERMINISTIC clause when you try to create function-based index you'll get an error:

ORA-30553: The function is not deterministic

So if you need, create function-based index on GET_ID_FROM_DESC(CALENDAR_YEAR):

create index times_fnc_idx1 on sh.times(hr.GET_ID_FROM_DESC(calendar_year));

Make a dashboard page and test report:


select T20553.CALENDAR_YEAR as c1,
sum(T20550.AMOUNT_SOLD) as c2
TIMES T20553,
SALES T20550
where (T20550.TIME_ID = T20553.TIME_ID
group by T20553.CALENDAR_YEAR
order by c1

Example in this post was about how to choose description column from a dashboard and use id column in filter. Replace this example with your real case and test performance.

1 comment:

mithun said...

Hi Goran,

It is very nice, and I'm looking something similar.

Assume I have two fields EmpName and EmpID , I want to display empname in the prompt drop down and pass the empid back to the query. Please let me know how this can be done.