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

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.

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:

OBIEECustomNoResultsView

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

Clean default dashboard styles with CSS

How to remove bevels, spaces and box shadows from OBIEE Dashboard?

By default, dashboard sections are visually separated from each other using spaces, borders, bevels and box shadows. Also the default color of dashboard background is light blue. We may want to remove it to get a cleaner dashboard display.

RemoveDashboardStyleFormatting1

Continue reading

Hide dashboard objects in OBIEE

How to hide a column or section from the dashboard display?

This workaround allows you to embed dashboard objects that are hidden from the view. Using this property you can hide Dashboard Column, Section or Alert Section objects. To hide particular reports or prompt you need to put them inside the hidden column or section.

We will need to use CSS formating to hide the dashboard elements. First, go to the top right corner of the column or section you want to hide and click on Format Section (or Format Column):

HideDashboardObjectsOBIEE1

Click on Custom CSS Style Options (HTML Only) and check Use Custom CSS Style.HideDashboardObjectsOBIEE2

In the text box put:

display: none;

The element with this property won’t be displayed on the dashboard. Note that this affects only the display of the dashboard, if you export the dashboard contents to a PDF the objects hidden with the CSS formatting will be visible.

Go back to report prompts with a custom button in OBIEE

When a prompt is added to a report in OBIEE, it will ask user to provide the prompt values when the report is opened. Report results will be filtered by the values chosen by user in prompts. After that, user can’t change the report’s filtering condition as the prompts are no longer visible. To solve this, we will add  GoBackToReportPrompt04 button with a link to take the user back to prompts.

How to add Go to Prompts button to the report?

Let’s add a Static Text view in report’s results page.

AddStaticTextViewOBIEE1

Inside the Static Text view check the Contains HTML Markup box and add the following HTML code:

<button onclick="window.location.href='saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FReports%2FSales' ">Go to Prompts</button>

replacing the path= parameter with the path to the report starting with forward slash / (%2F in URL encoding).

GoBackToReportPrompt6

Below the Static Text field you can see the preview of the button. Add the Static Text view to report’s Compound Layout.

GoBackToReportPrompt5

Now the report displays ‘Go to Prompts’ button that takes user back to prompts.

GoBackToReportPrompt3