Detecting all choices from dashboard prompts in Answers

If you are using all choices option in dashboard prompts and presentation variable, there is a way to detect this if you write a filter manualy (using convert filter to SQL option in Answers).

Prompt:



Report:



Detecting all choices for heading:

case when LENGTH('@{pv_char}')>0 then concat('Calendar month is ', '@{pv_char}') else 'All choices' end

or you can write it like this:

case when LENGTH('@{pv_char}') is null then 'All choices' else concat('Calendar year is ', '@{pv_char}') end

Detecting all choices in filter:

TIMES.CALENDAR_MONTH_DESC = ifnull('@{pv_char}',' ')
or
ifnull('@{pv_char}',' ')=' '

Put a heading in narrative view below tittle.

Test (all choices):



NQQuery.log:



Test (selected value):



NQQuery.log:



The same is if you are using number column in dashboard prompt and variable pv_number, just replace pv_char with pv_number and CALENDAR_MONTH_DESC with CALENDAR_YEAR for example.

Content Accelerator Framework V1 free utility set for OBIEE

This works only with OBIEE patch release 10.1.3.4.1 which has not been released yet.

This tool is designed to help OBIEE developers to develop templates from any source to any target environment. Major use of this tool is that you can simply duplicate any reports or logical part in your repository RPD file from one environment to another. The only required thing here is that the target environment should have basic logical model within RPD.

So if you like to clone a dashboard or report and all underlying objects to and want this to be very quickly you can use CAF V1. You can propagate all layers from dashboard level to RPD logical level just in few clicks.

CAF V1 coverage:



If you for example rename some objects in RPD your reports are broken because of new aliases in RPD and normally you would synchronize catalog manually, but this tool will synchronize all automatically.

*Note
CAF V1 cloner will clone all logical calculations and all Answers calculations.
CAF V1 cloner will not clone RPD objects that directly map to physical column or physical formula calculation.



Oracle Business Intelligence Enterprise Edition on OTN:
http://www.oracle.com/technology/products/bi/enterprise-edition.html

Download CAF utility:
http://download.oracle.com/technology/products/bi/files/OracleBIEE_CAFV1_Setup.zip

CAF documentation:
http://www.oracle.com/technology/products/bi/pdf/oraclebiee-cafv1-usage-instructions.pdf

How to display description in the prompt and filter by id

There was a question on OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=885967&tstart=15 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:

TIMES.CALENDAR_YEAR_ID = EVALUATE('GET_ID_FROM_DESC(%1)' as double, @{pv_desc})

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
is
v_id number;
begin
select distinct calendar_year_id into v_id from sh.times where calendar_year=p_desc;
dbms_output.put_line(v_id);
return v_id;
exception when others then null;
end;

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 rwijk.blogspot.com/2008/04/deterministic-clause.html:

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:



NQQuery.log:



select T20553.CALENDAR_YEAR as c1,
sum(T20550.AMOUNT_SOLD) as c2
from
TIMES T20553,
SALES T20550
where (T20550.TIME_ID = T20553.TIME_ID
and T20553.CALENDAR_YEAR_ID = GET_ID_FROM_DESC(2000))
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.

Using presentation variable from the first dashboard prompt as filter in the second dashboard prompt

There was a question on OTN forum
http://forums.oracle.com/forums/thread.jspa?threadID=884660&tstart=0 on how to filter second dashboard prompt which use EVALUATE function with presentation variable which is populated from the first dashboard prompt.

Here is the solution with POWER function in EVALUATE.

First dashboard prompt:



Second dashboard prompt:



Edit column formula:

case when 1=2 then TIMES.CALENDAR_MONTH_ID
else
EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3})
end

If you write this instead of the code above:

case when 1=2 then TIMES.CALENDAR_MONTH_DESC
else
cast(EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3}) as char)
end

then you cannot use decimal numbers in edit-box of the first dashboard prompt to populate POWER function. So do not use cast as char for this solution, leave it in number format.

Test:



At initial start, the presentation variable pv_test is 0 and default values are used:

cast(EVALUATE('POWER(%1, %2)',@{pv_test}{2},@{pv_test}{3}) as char).



If we remove default values from code and leave just:

cast(EVALUATE('POWER(%1, %2)',@{pv_test},@{pv_test}) as char)

zero (0) is polulated from pv_test and the result of POWER(0, 0) is 1:



Decimal numbers test:



This works fine.

Changing date format mask in javascript for calendar dashboard prompt

There was a question for me how we can change the date format mask when we choose a value from a calendar. It always shows date in format d.m.yyyy no mather what settings we have in localedefinitions.xml files. Only date order and date separator is populated from localdefinitions.xml (example, dateOrder is dmy and dateSeparator is -). So if you have been read my previous posts How to change date format mask in date dashboard prompts - drop-down list and calendar and Date between in filter and title when using presentation variable from calendar dashboard prompt or drop-down list in OBIEE you know that we used d.m.yyyy date format in calendar for selecting value from it, for default value from a repository variable and for parsing into presentation variable.

Javascripts are used for calendar prompts. To see which scripts are used here you must open your calendar dashboard prompt and see the source that is generated with view source. This will give you the order of scripts that are executed.

What now if I want to change date format mask in calendar so that it shows me for example 1-Jan-2009 format when I take a value from it? And also I want 1-Jan-2009 format to be the first value (default, from a repository variable) when I start this prompt.

Step 1 - default repository variable from initialization block



Leave this as in previous posts, default date is in character format dd.mm.yyyy and OBIEE will do implicit conversion to a date format which we have defined in dateShortFormat in localedefinitions.xml.

Step 2 - localedefinitions.xml

Depends on our locale settings these entries need to be modified:



dateShortFormat -> d-MMM-yyyy

This format is for default values.

dateSeparator -> -

This separator we expect after picking up the value from a calendar.

dateOrder -> dmy (like in previous posts)

This date order we expect after picking up the value from a calendar

Step 3 - view prompt source



Date short format, date order and short month names:



NQCShowCalendar function (on click):

NQCShowCalendar
(
document.getElementById('saw_11_6'),
document.getElementById('saw_11_4'),
null,
null,
false,
null,
nqcalmns,
nqdfmt,
nqdsep);

Step 4 - modify calendar.js javascript file to show a month abbreviation instead of month number

We use calendar.js from location \OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla, not from location \OracleBI\web\app\res\b_mozilla.

These are default values and they are populated in NQCShowCalendar function:



RgMN array is populated with nqcalmns=new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') which we see in view source. Nqcalmns depends on locale settings (login language, localedefinitions.xml):



We need to call rgMN array in NQCSetDate function so we modify current code with the new one (just put in comment old part of the code):



Step 5 - save, restart presentation service and test

I made a dashboard page with report in Answers and a calendar dashboard prompt:





I put here in filter 1-Jan-1999 as default value so I need to alter session to a English language because my database is in Croatian. This I'll do in Administrator:



Initial start (prompt is filled with repository variable):



NQQuery.log:

*Note that at initial dashboard start, the default 01.01.1999 is going directly into presentation variable so that's way we see it in SQL in NQQuery.log. After we pick up a new date from a calendar or refresh the same we will see new format in NQQuery.log (1-Jan-1999).



Choosing another date from a calendar:



NQQuery.log:



If you run all these statements in database:

select 1 from times where time_id='6-Jan-1999'--our example
select 1 from times where time_id='06-Jan-1999'
select 1 from times where time_id='6-JAN-1999'
select 1 from times where time_id='06-JAN-1999'
select 1 from times where time_id='6-jan-1999'
select 1 from times where time_id='06-jan-1999'

you can see that in all cases Oracle use TIMES_PK index, so implicit conversion char to date is present according to NLS settings in session/database.

if someone knows easier way to change month number to a month abbrevation for a calendar date prompt like I described in this post or to any other format please let me know.

You can change date order and separator from a localedefinitions.xml but it's not what we want to (for example 1.1.1999, 1/1/1999, 1999/1/1, 1999-1-1, etc).