Display duration time as days, hours and minutes in OBIEE

How to display time split in days, hours, minutes and seconds?

Let’s say we have a Duration Time column which contains a number of seconds of duration. We would like to display it in the report as value divided into days, minutes, hours and seconds.

For example to display 20,000 seconds is 5 hours, 30 minutes and 15 seconds.

We can do that in Answers using one of two methods:

Setting Custom data Format for a column

OBIEE offers setting custom column data format masks, including one to convert numbers in seconds, minutes, hours or days into DD:HH:MM:SS format.

How to set column’s format to display duration

  1. Go to Column’s properties
  2. Go to Data Format and check Override Default Data Format
  3. Select Treat Numbers As Custom and enter Custom Numeric Format from one of the below strings:

[duration(sec)] – this part defines whether the base value is stored in seconds (sec), minutes (min), hours (hour) or days (day).

dd:hh:mm:ss – output format in days, hours, minutes in seconds. We can add [opt:] before each part to tell OBIEE to omit this value if the opt part has no value (for example the value is less than a day, then the day is omitted).

Data Conversion Format String Output
[duration(sec)][opt:dd]:hh:mm:ss Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds.opt:dd displays the number of days, but if opt has no value, it is not displayed. 

Second is the default unit of time.

[duration(min)][opt:dd]:hh:mm:ss Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds.
[duration(hour)][opt:dd]:hh:mm:ss Formats the total of hours as duration.
[duration(day)][opt:dd]:hh:mm:ss Formats the total of days as duration.

The drawback of this solution is that it would display the values separated by colon, without giving the descriptions (for example 2 days 6 hours 32 minutes). If we need to add descriptions, we would need to use the other solution. Another issue may appear when exporting the report with custom data formatting to Excel, this bug is fixed since version

For reference, check Formatting Analyses, Views, and Dashboard Pages in OBIEE documentation at https://docs.oracle.com/middleware/1221/biee/BIEUG/format.htm#BIEUG1172 .

Splitting it inside the column’s formula

We can add a column formula to the column with values in seconds to split it by days, hours, minutes and seconds. That way we also can add some text.

First we need a column that contains a numeric value expressed in seconds, minutes, hours or days. We can have it already calculated in the source database or through ETL process.

If we don’t have the value calculated we can use another OBIEE function: TimestampDiff to calculate difference between two dates expressed in seconds, minutes, hours or days depending on the interval we specify.

Export and import settings for catalog objects in OBIEE

Archiving options:
Check if the security permissions in Development Environment should be migrated to UAT or Production environment.
  • Keep permissions – if the check box is ticked the source environment permissions will be exported. Objects archived with that option can affect the existing  permissions on target environment depending on the unarchiving option.  
  • Unticked – no permissions are exported. If the object already exists in the target environment will maintain the permissions of the target environment. If the object doesn’t exist will inherit the permissions from its new parent folder.
Unarchiving options:
REPLACE: Old – replaces existing objects with last modified date older than the migrated ones. Usually this one is used.
REPLACE: All – replaces existing objects with the same name, overwriting only those that do not have the read-only attribute set
REPLACE: Force – replaces existing objects with the same name, overwriting even those that have the read-only attribute set
REPLACE: None – doesn’t replace the objects if an object with existing name exists on the target environment
ACL: Create – existing groups/roles in the target environment will be used. The groups/roles used by the migrated object that don’t exist in the target environment will be CREATED.
ACL: Preserve – existing groups/roles in the target environment will be used. The groups/roles used by the migrated object that don’t exist in the target environment will be DISCARDED.
ACL: Inherit – assigns permissions of the parent folder on the target environment

Add total in a chart using Calculated Items in OBIEE

How to add an additional category for the total to a graph in OBIEE?

Sometimes in the requirements you need to present a breakdown of values by categories and their total on one chart. In the example below this is done for horizontal bar graph, but it can be done for any graph using the Calculated Items feature.

OBIEE Chart Example Continue reading

Change query logging level from Answers in OBIEE

How to override the log level settings directly in Answers?

Level of query logging for users should be set in the Oracle BI Administration Tool in Security Manager, but we can override the session variable to turn on logging (or change to a higher level of logging) for a particular analysis.

This can be sometimes useful as in production environments logging level is usually set to 0 (no logging).  Changing it to second logging level (LOGLEVEL=2) would fetch both the logical and physical SQL. Continue reading

Edit reports starting on Criteria tab in OBIEE

How to start editing a report on Criteria tab instead of Results tab?

By default the when you click on Edit report, you OBIEE Answers opens it on the Results tab. This triggers the report’s query execution and most of the time you want to go to the Criteria tab anyway. So it can save you some time if you change your account settings to navigate directly to the Criteria tab when editing. Continue reading

Check locked objects in Oracle database

How to check which objects are locked in Oracle database?

Sometimes its necessary to check which tables are locked on the database (be it by running ETL sessions or other database users). Information on current locks on the Oracle database objects can be found in the V$LOCKED_OBJECT view. Here’s a SQL query to check the locked objects on Oracle database: Continue reading

Display custom message in No Results view in OBIEE

How to change or remove the No Results message in OBIEE?

When the results of an analysis return no data, the following default message is displayed to users:


We can modify the No Results view, by adding custom message or more explanation on the use of report or hints on how to filter values.  We can also change it’s visual formatting.

Continue reading

Oracle Alta UI color palette

The colors of Oracle’s new user interface

Oracle announced Alta User Interface – a new design and set of guidelines for the developers for the latest Oracle applications (cloud-based products, Fusion Middleware and mobile applications) and the next Oracle products to come. Alta UI is created as a responisve, mobile-ready, flat and clean interface designOracleAltaUI1 Continue reading