Business Intelligence – Oracle

Archive for March, 2008

Oracle BI EE – Rolling Year,Month and Date filters – Moving Window Filters

Posted by Venkatakrishnan J on March 11, 2008

I have received this question a couple of times before and this also came up in the forums today. Lets look at the question first (modified version of the original question to make it more clearer). “I have a dashboard prompt wherein the user can choose any year (using a dropdown). I also have a report which shows sales across all the years (containing a lot of historical data). Now, based on the year that was chosen in the dashboard prompt i would like to restrict the report to only the previous 4 years of the selected date“. Though this question is pretty straight forward, i thought i would blog about it to serve as a point of reference for future questions like this. Just to make it a bit more complex lets also make the number 4 above dynamic. Lets start first with creating a dashboard prompt on 2 columns. One prompt would extract the year from a date field and the other would just let the user enter some numeric data. Make both the columns to set presentation variables promptdate and promptnumber. The first column would have a formula to extract the year from the date.


And the second one would just let the user to enter any value. So, in order to achieve that enter the below formula



Once this is done, lets create a simple report containing 2 columns. CALENDAR_MONTH_START_DATE and its corresponding SALES.


Now, create a filter on any of the columns and convert that filter to SQL.


Now, in the SQL box enter the below filter formula.

TIMES.CALENDAR_MONTH_START_DATE >= TIMESTAMPADD(SQL_TSI_YEAR, – @{promptnumber}{2}, TIMESTAMP ‘@{promptdate}{2003}-01-01 00:00:00’)

Similarly, create another filter and enter the below formula after converting it to SQL.

TIMES.CALENDAR_MONTH_START_DATE <= TIMESTAMP ‘@{promptdate}{2003}-12-31 00:00:00’

So, what the above filter does is it dynamically varies the window of the filter that is being applied. Notice that even this SQL box can accept presentation variables.The other important thing to note in the formula is the use of negative year values in TIMESTAMPADD function. This will move the year backwards.


Now, if you go to the dashboard prompt and enter 1 and 2006 in the prompts we would get a report containing the dates from 2005. Similarly, if you enter 2 and 2005, it would get a report containing dates from 2003 till 2005.



Again, very simple but can be useful in a lot of situations.


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

Oracle BI EE – Sending Reports to Non-OBI Users – Delivery Manager API of BI Publisher

Posted by Venkatakrishnan J on March 10, 2008

Another common question that generally comes from end users is “How do we make OBI EE Delivers to email the reports to users who are not part of a Delivery profile?”. Well, there are a variety of ways to achieve this. Let us first list those down

1.   Create a dummy user and assign as many email ids as possible to this dummy user’s delivery profile. While using Delivers, send the reports out to all these users.
2.   Create SA system subject area in BI Administrator and let the subject area be populated from database using Init Blocks. In this case, all the email ids would come from the database.
3.   Use BI Publisher Delivery Manager API to accept multiple email ids as its parameter and send the report to various users.

Today, we shall be looking at the 3rd approach which is easier to implement. Also, this provides us with much more flexibility in delivering the reports to the end users. In order to use this approach, you need to go through the basics of calling Java Classes from Delivers in my blog entry here. For the Delivery Manager API of BI Publisher to work, we need to have the following jars.

a.   Activation.jar – You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\lib
b.   Mail.jar – You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\lib
c.   xdocore.jar – You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
d.   versioninfo.jar – You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
e.   schedulerrpccalls.jar – You can get this jar from {OracleBI}\web\javahost\lib\scheduler

Now open JDeveloper and include the above jars in your classpath. Also, create a Java Class to enter the below code.

package bieesoap;



import javax.activation.CommandMap;
import javax.activation.MailcapCommandMap;


public class burstEmail implements SchedulerJavaExtension{
public burstEmail() {

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
FileInputStream fis = new FileInputStream(jobInfo.getResultSetFile());
DeliveryManager dm = new DeliveryManager();
DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_LOCAL);
req.addProperty(LocalPropertyDefinitions.LOCAL_DESTINATION, “D:\\Output.pdf”);

if (req.getStatus() == DeliveryRequest.STATUS_SUCCESSFUL)
MailcapCommandMap mc = (MailcapCommandMap)CommandMap.getDefaultCommandMap();
mc.addMailcap(“text/html;; x-java-content-handler=com.sun.mail.handlers.text_html”);
mc.addMailcap(“text/xml;; x-java-content-handler=com.sun.mail.handlers.text_xml”);
mc.addMailcap(“text/plain;; x-java-content-handler=com.sun.mail.handlers.text_plain”);
mc.addMailcap(“multipart/*;; x-java-content-handler=com.sun.mail.handlers.multipart_mixed”);
mc.addMailcap(“message/rfc822;; x-java-content-handler=com.sun.mail.handlers.message_rfc822”);
req = dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, “Mail from BI Delivers”);
req.addProperty(DeliveryPropertyDefinitions.SMTP_HOST, jobInfo.parameter(1));
req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM, “”);
req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, jobInfo.parameter(0) );
catch(Exception ex)
throw new SchedulerJobException(1, 1, ex.getMessage());


public void cancel() {



As you see, what the above code basically does is, it first stores the report in a local destination and then sends that report out to all the users. jobInfo.parameter(0) can accept comma seperated email ids as parameter. Compile the above code and bundle it into a jar file (including all the above 4 jars). Now, put this deployed jar file in {OracleBI}\web\javahost\lib. This will make the jar file to be accessible by delivers. Once this is done, create a simple ibot with PDF as the attachment


Now, in the advanced tab of this ibot, call the above class as shown below. This will accept 2 parameters

   1. Comma Seperated Email ids to which you want to send the report to
   2. Your SMTP mail Server


Now, you should be able to send emails out to multiple users. You can use it or customize the above code to your convenience. Let me know if anyone needs the jar file, i will post it here.

Posted in All Posts, Bi Publisher, OBI EE Plus | 18 Comments »

Oracle BI EE – Changing Column Indent Color in Pivot Tables

Posted by Venkatakrishnan J on March 7, 2008

I saw another question in one of the forums today wherein the user basically wanted to change the Column Indent Color in a Pivot table. As you might know, all the attributes of a pivot table are exposed to end users and can be customized using Section, Page and Column properties. But in the case of Column Indent there is no property in the UI to change the background color. If you are not sure what i mean, check the sample Pivot table below


The above pivot table shows you the Column Indent. Unfortunately, there is no property within the pivot table to change this color. lets look at an approach today to change the color of this cell. Remember, by following the below method, it will affect all your Pivot tables. The steps to modify this color are

1.   Go to {OracleBI}\web\app\res\s_oracle10\b_mozilla_4. Open the Views.css file in a text editor and Search for PTIndentCell. This is the Property which handles the color of the column indent. Now, change the background color to the desired color. For example,

.PTIndentCell {
background-color: #CC3333;


2.   Save the above file and repeat the same procedure to the Views.css file under {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4.

3.   Restart Presentation Services. Now, if you go to your pivot table, you would notice that the color for this cell would have been changed.


But just be careful while doing this since it would affect all your pivot views. Use this approach only if you have no other choice.

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

Oracle BI EE – Integrating Scheduler/Delivers into Other Applications

Posted by Venkatakrishnan J on March 6, 2008

Lets look at an important utility today that would enable Application developers to integrate BI Scheduler/Delivers into their application. That utility is called as SASchinvoke.exe. This enables end users to call/run ibots programmatically from any application(You dont have to come into Delivers). One of the major advantages of this utility is that you can run an ibot at any given time i.e You can call ibots on demand. You cannot do this from answers since all the ibots are triggered at specific time periods and you do not have the flexibility to call them on demand. Lets look at how to go about using this utility. You can find this in {OracleBI}/Server/Bin folder. The Syntax for the utility is as shown

Usage:   SASchInvoke.exe
-u <Admin Name>/<Admin Password>
                        (-j <job id> | -i <iBot path>)
                        ([-m <machine name>[:<port>]]
                        | -p <primaryCCS>[:<port>] -s <secondaryCCS>[:<port>])
                        ([(-r <replace parameter filename>
                            |-a <append parameter filename>)]
                         | [-x <re-run instance id>])
                         [-c <SSL certificate filename>
                          -k <SSL certificate private key filename>]
                         [-w <SSL passphrase>|-q <passphrase_file>|-y]
                         [-h <SSL cipher list>]
                          [-e <SSL verification depth>]
                          -d <CA certificate directory> | -f <CA certificate file>
                          [-t <SSL trusted peer CNs>]

This has a lot of options and you pass multiple parameters(we would not be discussing all of them). Lets look at a simple example of calling ibots using this utility. In order to execute an ibot, all we need is either the ibot’s Job id or its path. You can find the Job id from Job Manager.


In our case, lets run the ibot with a Job ID of 1.

saschinvoke -u Administrator/Administrator -j 1



We shall be using this utility in a variety of different applications which i shall be discussing in future blog entries. The above should give you a basic idea of what this can do.

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

Oracle BI EE – Conditional Drills and Dynamic Tool Tips – HTML Formatting and GO URL

Posted by Venkatakrishnan J on March 5, 2008

I got these 2 questions via email yesterday. We have discussed a variation of the above questions before. Lets look at both the questions one by one.

Question 1: How can we enable Conditional Drills for a column? i.e. if you look at the screenshot below, we have a very simple report containing only the region names. Based on the Region Values, is there a possibility to drill down to different reports. For example, i would like to drill down to say Report A for Asia Region and for other Regions i would like to have the default drill enabled.


This is a pretty interesting question since currently BI EE does not have the capability to setup conditional drills in the Repository. Lets look at a simple way of achieving this using HTML formatting. Of course, this is applicable only for tabular views. Lets start with creating a simple target Report (Report A for the drill on Asia Region) containing only the Region Column. Create a filter on this column with is prompted clause.


Now, start creating the main report with only the Region Column. In the formula tab of this Region Column, enter the below formula

CASE WHEN GEOGRAPHY.REGION_NAME = ‘Asia’ THEN ‘<a href=”http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Conditional%20Drills/Report%20A&Action=Navigate&P0=1&P1=eq&P2=GEOGRAPHY.REGION_NAME&P3=’||GEOGRAPHY.REGION_NAME||’&Options=rmf” style=”text-decoration: none;”>’||GEOGRAPHY.REGION_NAME||'</a>’ ELSE GEOGRAPHY.REGION_NAME END


Now, convert the data format of this column to HTML. Now when you click on Asia, it will take you to Report A with Asia as parameter being passed to that Report. For other Regions, normal drills would happen.



Question 2: How do we Produce Dynamic Tooltips in a Report?

Again, this is pretty straight forward if you know HTML tags. But remember this would work only for Tabular views. Lets start with the same simple report containing Region names. In the formula tab enter the below formula

‘<div title=”‘||GEOGRAPHY.REGION_NAME||'”>’||GEOGRAPHY.REGION_NAME||'</div>’


Convert the column to HTML format.


Now, you should be able to get the column values themselves as the tooltip in your tabular report.


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

Oracle BI EE – Removing URL Links to Marketing, Disconnected Analytics and Delivers

Posted by Venkatakrishnan J on March 4, 2008

Another good question came up in the forums yesterday with regard to Dashboard Customization. As you might probably be aware, when one purchases BI SE1, the licensing of BI SE1 does not allow users to use components like Marketing, Disconnected Analytics and Delivers. When you install BI SE1 you would notice that these components would not be visible from the Dashboards — More Products link. But if you notice, BI SE1 comes with version of BI EE. So, whenever you are doing an upgrade then the component links like Marketing, Disconnected Analytics and Delivers would reappear in the dashboard(if you are not restoring the older XML files). Now, the question is how do we go about removing these links. In order to remove these links, the set of steps would be similar to what we saw here. In our example, we shall see how to go about removing the Marketing link. The procedure to remove the other links would be the same.

1.   The first step is to copy the commonuitemplates.xml file from {OracleBI}webmsgdbmessages to {OracleBIData}webmsgdbcustomMessages (if you do not have this folder, create one).

2.   Open this copied file in a text editor and search for the kuiMainBarActionsMenus webmessage.


3.   In this web message, you would find an XML like the one below

<sawm:if name=”marketingURL”><a class=”NQWMenuItem” name=”marketing” href=”@{marketingURL}” mce_href=”@{marketingURL}” target=”@{target}”><sawm:messageRef name=”kmsgUIMarketing”/></a></sawm:if>

This is the XML that makes the Marketing URL to be visible from the dashboard. Just remove the above XML from the actual message and save the file. Your final XML for this webmessage should look like the one below

<WebMessage name=”kuiMainBarActionsMenus” translate=”no”><HTML>
<div id=”MainBarProductMenu” class=”NQWMenu” align=”left” onmouseover=”NQWMenuMouseOver(event)”><sawm:messageRef name=”kuiMenuShadowBegin”/><sawm:if name=”deliversURL”><a class=”NQWMenuItem” name=”delivers” href=”@{deliversURL}” mce_href=”@{deliversURL}” target=”@{target}”><sawm:messageRef name=”kmsgUIiBots”/></a></sawm:if><sawm:if name=”advReportingURL”><a class=”NQWMenuItem” name=”advReporting” href=”@{advReportingURL}” mce_href=”@{advReportingURL}” target=”@{target}”><sawm:messageRef name=”kmsgUIAdvancedReporting”/></a></sawm:if><sawm:if name=”marketingURL”><a class=”NQWMenuItem” name=”marketing” href=”@{marketingURL}” mce_href=”@{marketingURL}” target=”@{target}”><sawm:messageRef name=”kmsgUIMarketing”/></a></sawm:if><sawm:if name=”mobileURL”><a class=”NQWMenuItem” name=”mobile” href=”@{mobileURL}” mce_href=”@{mobileURL}” target=”@{target}”><sawm:messageRef name=”kmsgUIMobile”/></a></sawm:if><sawm:if name=”officeURL”><div class=”NQWMenuItemSeparator”/><a class=”NQWMenuItem” name=”mobile” href=”@{officeURL}” mce_href=”@{officeURL}” onclick=”NQWClearActiveMenu()”><sawm:messageRef name=”kmsgUIBIforOffice”/></a></sawm:if><sawm:messageRef name=”kuiMenuShadowEnd”/></div><div id=”MainBarSettingsMenu” class=”NQWMenu” align=”left” onmouseover=”NQWMenuMouseOver(event)”><sawm:messageRef name=”kuiMenuShadowBegin”/><sawm:if name=”adminURL”><a class=”NQWMenuItem” name=”admin” href=”javascript:void(null)” mce_href=”javascript:void(null)” onclick=”saw.popupWindow(‘@{adminURL}’,’SAWAdmin’);saw.clearActiveMenu();return false”><sawm:messageRef name=”kmsgUIAdmin”/></a></sawm:if><sawm:if name=”canProxy”><a class=”NQWMenuItem” name=”proxy” href=”javascript:void(null)” mce_href=”javascript:void(null)” onclick=”saw.proxy.showProxyDialog(‘@{proxyTargetAccount}’, ‘@{proxyStartPage}’, event);saw.clearActiveMenu();return false;”><sawm:messageRef name=”kmsgUIProxy”/></a></sawm:if><sawm:if name=”prefsURL”><a class=”NQWMenuItem” name=”prefs” href=”javascript:void(null)” mce_href=”javascript:void(null)” onclick=”saw.popupWindow(‘@{prefsURL}’,’SAWPrefs’);saw.clearActiveMenu();return false”><sawm:messageRef name=”kmsgUIMyProfile”/></a></sawm:if><sawm:messageRef name=”kuiMenuShadowEnd”/></div></HTML></WebMessage>

4.   Save the file after making changes to the web message. Restart presentation Services. Now you would notice that the Marketing URL would not be visible under More Products link.


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

Oracle BI EE – Downgrading Repositories

Posted by Venkatakrishnan J on March 3, 2008

For those of you who have worked across multiple versions of BI EE, you would have noticed that the Repository files( .rpd files) are not backward compatible(but they are a upward compatible) i.e One cannot use a rpd file created with a higher version of the Admin tool in a lower version of the Admin tool. But the vice versa is possible. Look at the screenshot below,


I got the above error, after trying to open a repository that was created with of the Admin tool from version of the Admin tool. Now, how do we downgrade the repository to a lower version. In earlier releases of BI EE and Siebel Analytics, there was a tool called as nqgenoldverrpd.exe which would automatically downgrade the repositories. But in the current releases we do not have this tool. So, in order to downgrade in the later releases lets look at an approach today. Just remember this is not a supported approach and this assumes that you are not using any new features(in your rpd) that are specific to the newer version of the Admin tool. Ensure that you are taking a backup of all the repositories before trying this The below diagram explains the versions that i have in 2 different machines.


As shown above, i have version of the Admin tool in Machine A and version of the Admin tool in Machine B. Our aim is to make the repository created with the Admin tool in Machine B, work with the Admin tool in Machine A. In order to achieve this, go to machine B and open up a command prompt. Type in the following command.

D:OracleOracleBIserverbinnqudmlgen -U Administrator -P Administrator -R bise1.rpd -O bise1.txt


So, what we are basically doing is we are creating the UDML of the repository using the nqudmlgen.exe utility. Once this done, copy the bise1.txt to Machine A. Open up the copied UDML file (bise1.txt) in a text editor. One the top of this text file you would basically see something like the one below.

‘CustomPresentationLayer’ = ’01’,
‘PersistedNextUpgradeID’ = ‘BA500000’);
VERSION 1.1.185;

We got the above mentioned error because of the VERSION 1.1.185. Now, edit this line and change it to 180 (for version. This number varies for each version of BI EE release).

‘CustomPresentationLayer’ = ’01’,
‘PersistedNextUpgradeID’ = ‘BA500000’);
VERSION 1.1.180;

Save this file in Machine A and then in Machine A open up a command prompt and enter the below command.

D:oracleOracleBIserverBinnqudmlexec -U Administrator -P Administrator -I bise1.txt -O bise1_new.rpd


Again, we are basically using the nqudmlexec.exe utility to recreate the repository from the UDML text file. But now we should be able to open the repository in the lower version of the Admin tool.


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