How to choose null, not null or all values from drop down list

Example

Prompt the user to select only null, not null or all values from drop down harcoded list and reference it to the column.

Table for this example:

create table value_test (id number,col1 number, col2 varchar2(100))



After importing table to Administrator we join it to the dummy fact table in the BMM (not shown).

Dashboard prompt with drop down harcoded list:



SQL Results:

SELECT case when VALUE_TEST.COL1 is not null then 'Not null' when VALUE_TEST.COL1 is null then 'Null' end FROM "Write back test" union all SELECT case when 1=2 then VALUE_TEST.COL2 else 'All' end FROM "Write back test"

Put All as default:



Answers report:



Advanced SQL filter:

case
when '@{pv}'='All' then 'All'
else
case when VALUE_TEST.COL1 is not null then 'Not null' else 'Null' end
end='@{pv}'

Test

All (column col1 all values):



NQQuery.log:



Not null (column col1 is not null):



NQQuery.log:



Null (column col1 is null):



NQQuery.log:

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.

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).

Date between in filter and title when using presentation variable from calendar dashboard prompt or drop-down list in OBIEE

In my previous post how to change date format mask in date dashboard prompts - drop-down list and calendar I showed my solution of date format problem when using drop-down list and calendar dashboard prompt and repository variable as default. I use same prompts and localedefinitions.xml settings as in my previous post.

Now we need to create a report in Answers and then show date prompts in report title. First we use a calendar prompt for input which is in d.m.yyyy format and later in drop-down prompt which is in dd.mm.yyyy format. According to input formats our output format will be the same for both calendar and drop-down list and will be something like Amount sold from date dd.mm.yyyy to date dd.mm.yyyy. You can see that although we have two different date formats as inputs (d.m.yyyy for calendar and dd.mm.yyyy for drop-down list) there is only one format in title (dd.mm.yyyy) and it is with leading zeros for date and months values.

Example 1 - using calendar date prompts in filter and report title

Dashboard prompts:



Column formula for the first calendar date prompt:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.1900' as date) end

and for the second:

case when 1=2 then TIMES.TIME_ID else cast ('1.1.2900' as date) end

Column formula for drop-down list date from field:



Column formula for drop-down list date to field is:

EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy')

For this example we use only calendar prompts.

We created columns in Answers as in picture:



Amount sold -> SALES.AMOUNT_SOLD

p_date_from -> '@{p_date_from}'

original presentation variable date from p_date_from from a calendar

p_date_to -> '@{p_date_to}'

original presentation variable date to p_date_to from a calendar

Variable length ->



Title date from in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

Title date to in dd.mm.yyyy format ->



*This case always returns dd.mm.yyyy date format

We add filter on TIMES.TIME_ID column:



TIMES.TIME_ID between '@{p_date_from}{1.4.1999}' and '@{p_date_to}{1.5.1999}'

Default values are only for test in Answers.

We add narrative view as replacement for a title view because we want to include last two column fields from Answers (Title date from in dd.mm.yyyy and Title date to in dd.mm.yyyy). We call these values in title with @5 and @6.



Delete a classic title view first:



We make a dashboard page with our dashboard prompts and Answers report, so we can test it now.

Initial start:



We didn't touch calendar, we just start report with initial repository variable which is in dd.mm.yyyy format so at this first point our presentation variables p_date_from and p_date_to is filled with dd.mm.yyyy 01.01.1999 (yellow) although prompt fields are showing d.m.yyyy. (blue). We can hide last two columns (red) because we use them only in title. Green part is populated in narrative view which use last two columns (case). Title will always show date in dd.mm.yyyy format.

If we now choose value from a calendar:



We see that the yellow part is similar to dashboard prompt fields (we populated it from a calendar). Everything else is like in previous initial start part. Title will always show date in dd.mm.yyyy format.

NQQuery.log for this example:



Example 2 - using drop-down list date prompts in filter and report title

In the report that we created in the previous example we need to replace p_date_from with p_date_from2 and p_date_to with p_date_to2 in all columns that use presentation variables and also in filter, to filter our report now with drop-down list date prompts.

So no mather if we are doing initial start with repository variable or if we select value from a list everything is the same (red), the format in the title, presentation variables and drop-down list fields is in dd.mm.yyyy format already, so we don't need our CASE for dd.mm.yyyy format in title.



This is NQQuery.log for this example:



When retreiving rows in both example 1 and 2 Oracle does implicit conversion char to date so index TIMES_PK is used on TIMES.TIME_ID column.

If you run all these statements in database:

select 1 from times where time_id='1.1.1999' --example 1
select 1 from times where time_id='01.01.1999' --example 2
select 1 from times where time_id='1.01.1999'
select 1 from times where time_id='01.1.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.