OBIEE 11g – How to Make Drop Down List Box default to latest data value available in the List

Customer Question

The customer would like to make drop down list box default to latest data value available in the list. There is a hidden request which points towards the transactional data availability rather than dimensional values. For e.g. there is a global filter list box defined on Date column, by default the list should shown all date values available irrespective of transactional data is available or not. So there are two different requests we are talking here, the first one to restrict list of dimensional attribute based on transactional data available and then take the latest at default. 

There are different ways we can achieve what customer wants, one can use session variable to get CURRENT_SNAPSHOT_DT or any other variable which pick up the latest date for which transactional data is available, second option is use SQL’s within the global filter definition for dimensional attributes based on transactional data availability.

This blog is an attempt to show how this customer request can be fulfilled by using SQL’s within global filter definition.  It is make more interesting when subject area has more than one logical fact.

Choice List Values

By default dashboard prompt defined on Calendar Data displays all possible values available in day dimension. The choice list values can be restricted by using SQL Results.

A sample SQL which restrict calendar date based on Revenue Values is shown below. The outcome is a list which only displays calendar date where revenue exists, the assumption is that you want to drive your date column value based on revenue fact.

SELECT "Time"."T00 Calendar Date" FROM "A - Sample Sales" WHERE

("Base Facts"."1- Revenue" >= 1) ORDER BY 1 ASC

FETCH FIRST 65001 ROWS ONLY

 Default Selection

By default dashboard prompt defined on Calendar Data displays no default selection. 

A sample SQL which shows the latest calendar date value for which Revenue Values exists is shown below. The outcome is a latest calendar date  for which revenue exists in fact table, the assumption is that you want to drive your date column value based on revenue fact.

SELECT MAX("Time"."T00 Calendar Date") FROM "A - Sample Sales" WHERE

("Base Facts"."1- Revenue" >= 1) ORDER BY 1 ASC

FETCH FIRST 65001 ROWS ONLY

Bloggers’ Take

There are some scenarios where this default selection and choice list value become more interesting. Some of the valid discussion points are as below.

What about if you have multiple facts e.g. revenue and order, there may be situation where revenue exists for a specific date but there is no order for the specific date.

What if one of the fact is defined as a implicit fact? Is it really helpful to define implicit fact for each of the subject area?

Are there any performance implications of setting up dimensional value restriction based on fact?

Is adding a special subject area for all dashboards prompt for reporting solution is a good idea?

I am hoping to cover these interesting scenarios in feature blogs.