Business Intelligence – Oracle

Archive for December, 2007

Oracle BI EE – Presentation Variable in Report Titles

Posted by Venkatakrishnan J on December 12, 2007

I thought of blogging about another pretty common question which keeps getting repeated in the forums. The question is “How do we refer the Presentation Variables in the Title of a report?”. Lets try to achieve that here. To take an example, i have a dashboard page containing a dashboard prompt and a sample report. The sample report will be filtered based on the value chosen in the dashboard prompt. This is achieved using a presentation variable product.




Now, our aim to display in the Title of the Report something like “Product Chosen in the Prompt is: Actual Value Passed in the Prompt“. Now lets go to the report and add a sample dummy column. In that dummy column refer the presentation variable like this ‘@{product}’ including the single quotes and hide the column.




Now go to the narrative view of the report. In the narrative view enter the desired text followed by @n (@ refers to the column and n refers to the column position. In our case we are referring to the hidden presentation variable column). Now go back to criteria. Remove the filter (the reason why we are removing the filter is we would be getting a no data found message and hence we would not be in a position to arrange the narrative view to look like the title view). Once this is done, add the narrative view to look like the title view. And go back to the criteria and add the filter again with the presentation variable. Save the report. Now you would be able to see the filter with a customized message in your title view.




The advantage of the narrative view is that you can format your variables and also can add marquee like scrolling titles using various HTML formatting tags. But if you just want display the filter applied without any custom message you can use the Filter View.


Posted in All Posts, OBI EE Plus | 4 Comments »

Oracle BI EE – Customizing Login Screen

Posted by Venkatakrishnan J on December 11, 2007

I thought of picking up an older forum question for my blog entry today. The question is how do we go about customizing BI EE login screen? BI EE login screen primarily works (from the UI perspective) using the following style, stylesheets and XML messages.

1.   Logon.css – Stylesheet that sets the font sizes, background color etc
2.   LogonControlMessages.xml
3.   LogonMessages.xml
4.   ProductMessages.xml
5.   Utilmessages.xml
6.   sk_oracle10 – Base skin which provides the images for the login screen.

The login image of the login screen comes from the bglogon.jpg (this would be present in your base skin i.e {OracleBI}\web\app\res\sk_oracle10\b_mozilla_4.


In order to modify the above image, just create a new skin and put a new image under the same name as bglogon.jpg. For creating a new skin, check my blog entry here

The word Oracle Business Intelligence comes from the kmsgProductGeneral message in ProductMessages.xml (This file would be under {OracleBI}\web\msgdb\l_en\messages )


“Please enter your User ID and Password below, and then press the Log In button.” – This comes from kmsgAuthenticateRequestedContentRequiresLogon message of the logonmessages.xml (This file is present under {OracleBI}\web\msgdb\l_en\messages ).


The UserID, Password and the Login button all come from a message called kmsgLogonLanguageForm in logoncontrolmessages.xml (This file would be present under {OracleBI}\web\msgdb\messages).


Lastly the copyright messages come from the message kmsgNQuireLegalCopyright in utilmessages.xml (This file would be present under {OracleBI}\web\msgdb\l_en\messages).


In order to customize these messages check my blog entry here and here. Just a word of note: Some images like “Powered By Oracle” logo should not be modified since they would violate the license agreement. The above should give you an idea of the list of messages/images that you need to modify to get your desired customization.

Posted in All Posts, OBI EE Plus | 22 Comments »

Oracle BI EE – Displaying BLOB images – Using Pl/SQL Gateway

Posted by Venkatakrishnan J on December 10, 2007

I had received a couple of emails last weekend on how to go about displaying images stored inside an Oracle Database as a BLOB field in OBI EE. Well, i have had this question asked before by some customers with whom i had interacted in the past. So, i thought of blogging about it so that others can use the same. Before proceeding further, just a word of notice, BLOB images are not directly supported in OBI EE. i.e. One cannot import these fields as normal fields in the repository and then make them display in BI EE. So, in order to display the BLOB images in OBI EE, we would be using a feature of Oracle Database called as “Pl/SQL gateway”. So, what is a pl/sql gateway? To keep it simple, PlSQL gateway is a feature wherein one can expose Oracle Pl/SQL packages to a web browser. It is similar to a HTTP server but would be using Pl/SQL packages. A very good example of a sample application that uses plsql gateway is the Application Express 3.0. If you had installed 11g, you would have noticed that APEX 3.0 gets installed by default and also the Pl/SQL gateway gets configured by default. One of the main reasons of using PlSQL gateway is to have a mechanism wherein we can display all BLOB images over a web browser. So, basically we would be using the PlSQL gateway a call a procedure which in turn would download a stored BLOB image. In the example to follow, i would be using the 11g plsql gateway and APEX 3.0. If we configure APEX 3.0, the gateway also gets configured. To configure APEX 3.0 in 11g, there are 2 simple steps

1.   Run the @apxconf.sql (under {ORACLE_HOME}/apex) as Sys. It would ask you for a port and password. In my case, i have used 7779 as the port.


2.   Unlock the Anonymous Account.


Now, you should be able to log into APEX using this URL http://localhost:7779/apex.


This would mean that pl/sql gateway is working. If you watch closely, the URL would have been modified to http://localhost:7779/apex/f?p=1232353. So, what this means is that APEX by default calls a procedure named “f” which accepts parameter p. So, lets test this out by compiling a sample procedure (which would print employee details. I have taken this directly from the docs to keep it simple) and by calling this procedure from the gateway. The procedure is given below and i am compiling this from a schema called blob1.

CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees ORDER BY last_name;
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>List of Employees</title>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘<table width=”40%” border=”1″>’);
HTP.PRINT(‘<th align=”left”>Last Name</th>’);
HTP.PRINT(‘<th align=”left”>First Name</th>’);
HTP.PRINT(‘</tr>’); FOR emp_record IN emp_cursor LOOP HTP.PRINT(‘<tr>’);
HTP.PRINT(‘<td>’ || emp_record.last_name || ‘</td>’);
HTP.PRINT(‘<td>’ || emp_record.first_name || ‘</td>’); END LOOP;
HTP.PRINT(‘</table>’); HTP.PRINT(‘</body>’); HTP.PRINT(‘</html>’); END;

      As you see what the above procedure does is, it prints out all the employee details. HTP is a system package to make browser calls. Now lets call this procedure from the URL using the same syntax as above. The URL would look like this



What happened? Why do we get a Error:404 page not found error. This means that the ANONYMOUS account(ANONYMOUS should have this privilege since we are not giving any login details) does not have a execute privilege on this package. So, lets give the privilege and try that out.



What happened now? We are geting a Error:403 Page forbidden error. This means that we are able to access the package but pl/sql gateway does not allow us to execute this. In order to make plsql gateway to allow this procedure to execute, we need to add our procedure to the list of allowed procedures in the wwv_flow_epg_include_mod_local function. Use the below function to add out procedure to the list

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
if upper(procedure_name) in (‘BLOB1.PRINT_EMPLOYEES1’) then
return TRUE;
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Thanks to the APEX blogs here and here for giving the details on the above function. Now lets compile the above function as FLOWS_03000 and see what happens.



So, this time you would be seeing the list of employees in a web browser. Now that we are sure about the list of steps to configure PlSQL gateway to run desired procedures lets look at how to go about configuring the same for downloading BLOB images. The first step is to create a table containing an id field and corresponding image BLOB field. If you are not sure about the insert syntax for BLOB’s i would recommend using a shareware utility called as OracleLOB Editor. In my case, i have created a table having 3 fields


And i have inserted images into my IMAGE_BLOB field. The next step is to write a procedure which can download the images. I will be using the procedure below (taken from the docs here

CREATE OR REPLACE procedure custom_image_display(p_imageid in number) as
v_mime varchar2(255);
v_length number;
v_file_name varchar2(2000);
Lob_loc BLOB;
select ‘image/gif’, image,file_name,dbms_lob.getlength(image)
into v_mime,lob_loc,v_file_name,v_length
from hr.blob_image
where id = p_imageid;

— set up HTTP header

— use an NVL around the mime type and
— if it is a null set it to application/octect
— application/octect may launch a download window from windows
owa_util.mime_header( nvl(v_mime,’application/octet’), FALSE );

— set the size so the browser knows how much to download
htp.p(‘Content-length: ‘ || v_length);
— the filename will be used by the browser if the users does a save as
htp.p(‘Content-Disposition: attachment; filename=”‘ || v_file_name || ‘”‘);
— close the headers
— download the BLOB
wpg_docload.download_file( Lob_loc );
end custom_image_display;

Now compile this procedure and grant execute privilege to the anonymous user. Also, add this procedure to the wwv_flow_epg_include_mod_local function as mentioned above. Now test the procedure using the below URL




In your case, it might display the image or would show a pop up to download the image. If you get either of them then the Procedure and the PlSQL gateway are working fine. Now go to BI EE Answers and we would be using the same technique to display the images as mentioned in my previous blog entry here.

Lets start with creating a sample report. We shall include Region, ImageID (a running number to pass the id to the procedure above) and the image column.


In the image column, enter the following formula and change the data format to HTML.



Now, if you view the report, you shall see the blob images being rendered directly within BI EE.


Posted in All Posts, APEX, OBI EE Plus | 5 Comments »

Oracle BI EE – Marquee, Images and Gantt Charts

Posted by Venkatakrishnan J on December 6, 2007

Thanks to Matt Bedin, BI EE product managment and Thomas Lukens for sharing this. One of the important requirements for any user is to have multiple formatting options within a Tabular View table. For example, one might need scrolling Marquee within a column i.e make a column value move from left to right or one might want to display images from a site or one might want to even put submit forms directing to an external application and last not the least to display Gantt like charts within a tabular column. So, lets look at enabling each one of the above one by one.


   Lets start with creating a sample report. Lets create a report with Product Names and the their corresponding Sales. Our aim is to make the Product names rotate like a Marquee.


Now go to the formula window of the Product Names and enter the below HTML text.

      ‘<MARQUEE bgcolor=”#CCCCCC” loop=”-1″ scrollamount=”2″ width=”100%”>’||PRODUCTS.PRODUCT_NAME||'</MARQUEE>’


Now go to the Properties of this columns and navigate to Data Format and change the data format to HTML. Now if you look at the report the product names would be scrolling.



Now you can realize the kind of extensibility this can have. You can have your own HTML formatted columns. All the remaining 2 that we would be seeing are actually extensions of this property.


   Lets use the same methodology above to display images. Lets display the Oracle logo that we have in the Oracle.Com website. The procedure is same. In the column formula enter following HTML

      ‘<IMG SRC=”” WIDTH=”80″ HEIGHT=”80″ BORDER=”0″ ALT=”Oracle”>’

And change the column format to HTML as above.


Gantt Charts:

   One amazing use of the above HTML property is that you can mock and create Gantt Charts. Gantt Charts are basically bar charts wherein the bars would denote the size of the metric for which they are plotted on. There are many variations of this chart. But for illustration purposes lets create a simple one. In the column formula enter

      ‘<table><tr><td style=”padding-left:0″ width=’ || CAST(SALES1.Sales/1000 AS VARCHAR(20)) || ‘ bgcolor=#9C9ECE height=16> </td></tr></table>’

Now change the data format to HTML.


Here, we are basically creating a horizontal bar whose size varies according to SALES1.Sales column. We have divided by 100 in order to bring the size relevant to HTML format. One can use this to have multiple other formatting options. If you are good in HTML then this is just for you.

Posted in All Posts, OBI EE Plus | 12 Comments »

Oracle BI EE – Bins and Groups

Posted by Venkatakrishnan J on December 5, 2007

One of the features that end users generally want is the ability to group multiple dimension attribute values into their own understandable groupings. For example, if we have a Product Name column, then end users would like to group different products under different categories like say Computer Products, Household Products etc. Also, they would like to have the capability to drill down from these groupings into their corresponding individual products. In order to achieve this we can use a feature in Answers called as Bins. Bins can group multiple values into smaller simpler groups thereby helping end users to analyze their data better. Lets see how this Bins work. We shall start with a simple report containing the Product Names and their corresponding sales.


Now if you go the formula section of the PRODUCT_NAME column, you would find a tab called as bins.


Now lets try to add a simple Bin. As you would see this would pop up a filter window wherein we can choose how we want to group the values into. In our case lets choose a set of products into a simple group called as “Important Computer Goods”. And for the remaining products lets group them as “Remaining Goods”.



Now if you go back to the formula console, you would see that the corresponding CASE statements would have been generated automatically. Now lets view the results. As you would see you only have 2 groupings now and you can drill down within each of these groups. Also, aggregation for these groups would happen automatically.


A very simple feature but can reduce a lot of effort while writing case statements.

Posted in All Posts, OBI EE Plus | 9 Comments »

Oracle BI EE – URL Parameters

Posted by Venkatakrishnan J on December 4, 2007

I had blogged a couple of times about the various uses of GO URL parameters. But if you had noticed, the documentation is not comprehensive enough. Also, apart from GO URL we also have a lot of other options. I would classify them as URL parameters. GO URL and Dashboard URL parameters would help in integrating BI EE reports and dashboards into any application. But what if you want other components like Answers, Delivers etc to be integrated? For this we have the generic URL parameters. Lets try to understand the URL format of BI EE. Following would be the generic syntax



Hostname: Machinename hosting BI EE Presentation Services (eg: localhost)
Port: Web Server Port (eg: 9704)
CMD: Command thats actually passed to saw.dll. This determines which page should be opened Eg: GO, Dashboard etc
PARAMETERS: The parameters that CMD above accepts Eg: &nquser, &nqpassword etc

I am in the process of documenting all the list of CMD values and their corresponding Parameters. So far, i have found around 62 CMD values and about 38 parameters. You can get the list below. It is still a draft version. I will be adding more and more as and when i find some new ones. Also, do let me know if you find any. I have found this to be pretty useful especially to understand how BI EE works. All these were taken by sifting through a lot of XML messages and Java Scripts.

You can download the list here

I have found some interesting ones. For example, PreviewGo would give the preview format of an Answers report.

Eg: http://localhost:9704/analytics/saw.dll?PreviewGO&Path=/shared/paint+demo/navigation/navigation1

IssueRawSQL would give a new window wherein one can issue/test Logical SQL commands.

Eg: http://localhost:9704/analytics/saw.dll?IssueRawSQL

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE – Level Based Measures (LBMs)

Posted by Venkatakrishnan J on December 3, 2007

Now that i am back from quite a good weekend, i thought i would blog about something which is pretty interesting i.e. Level based Measures. Lets try to understand this first. This is what the documentation has got to say about this “A level-based measure is a column whose values are always calculated to a specific level of aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. “ As stated above Level based measures have their aggregation set based on a level in the hierarchy. Lets try to understand this using a simple example. We will start with a simple hierarchy as shown below.


Basically, we have a hierarchy and we would like to have the Sales measure aggregated over 3 levels i.e. Total Level, Region Level and the Country level. Now lets start with creating 3 logical measure columns Sales By Region, Sales By Country and Total Sales. Easch of these measures are exact replica of the Sales Measure which has a default aggregation of SUM.




Now, in order to make the 3 columns to aggregate over Region, Country and Total levels just drag and drop the corresponding columns to their associated levels as shown below.


Now, try creating a report with all the 4 columns(including the base SUM aggregated Sales measure) and see what happens.


As shown above what happens is Sales By Country calculates the sales across all the countries. Sales By Region calculates the sales across all the regions. And Total sales gives total sales across all the regions and countries. But you would see a lot of line items because of the granularity at which the Sales By Country Works. Since there are 17 different countries you would see 17 different line item sums. Now, if you remove the Sales By Country column the number of line items gets reduced to the number of regions i.e. 5


This could be a lot useful in situations where you want to a division of different LBM’s for example, Sales By Region/Sales By Country.

Posted in All Posts, OBI EE Plus | 2 Comments »