OBIEE 11.1.1.6.0 New feature : Auto-Complete Functionality for Prompts

 

Oracle BI EE 11.1.1.6 provides auto-complete functionality for prompts, which, when enabled, suggests and highlights matching prompt values as the user types in the prompt selection field.

Auto-complete is only available for the Choice List prompt type when the prompt designer selected the “Enable User to Type Value” option in the “New Prompt dialog”.

Note : auto-complete is not available for hierarchical prompts.


The administrator configures the auto-complete functionality to be case-sensitive or case-insensitive, and then specifies the matching level. The matching level determines how Oracle BI EE matches the column’s values to what the user types .

Step-By-Step :

System Setting:

Step1 : Navigate to the path <Middleware>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 and open the instanceconfig.xml file .
Add the below entry in between <ServerInstance></ServerInstance> tag.
<Prompts>
< MaxDropDownValues>256</MaxDropDownValues>
< AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
< AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
< AutoCompletePromptDropDowns>
< SupportAutoComplete>true</SupportAutoComplete>
< CaseInsensitive>true</CaseInsensitive>
< MatchingLevel>MatchAll</MatchingLevel>
< ResultsLimit>50</ResultsLimit>
< /AutoCompletePromptDropDowns>
< /Prompts>

The entry should looks like below screenshot .



Restart the OBIEE 11G Services .

1.1 : Login into Presentation services and create a prompt(In my case I am using “Sample Sales Lite” Subject Area)


Here select ‘User Input as Choice List’ and check ‘Enable user to type values’ .
Click on to preview the prompt .

Type the values in the prompt and you can observe that the prompt suggests and highlights matching prompt values as the user types in the prompt selection field.

Dashboard Setting:

Step2 :How to Off Auto-Complete functionality in Dashboard?

If auto-complete is enabled for Oracle BI EE, then the dashboard designer can remove the auto-complete functionality from individual dashboards by setting the Prompts Auto-Complete field to Off in the “Dashboard Properties dialog”.

2.1 :Edit the dashboard and goto Dashboard properties .



2.2: Set Prompts Auto-Complete to Off .



Step3: How to Off Auto-Complete functionality in User Level?

3.1: Goto User -> My Account ( Here in my case user is weblogic )



3.2: Set Prompt Auto Complete to Off and click Ok



Hope this blog helped you.



OBIEE10g Auto Suggest Prompt

A client asked me if I could create an auto suggest prompt for him. (ie: Google Style Prompt). Basically he wanted an edit box prompt which would fill an suggestion box which he could tab trough to make the right selection.
Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. But I didn’t reinvent the wheel ! The people at jQuery already did the bases, I simple adapted it for usage in OBIEE 10g.
1. Download the jQuery UI package here. Install it in your b_mozilla directory’s (or other webserver dirs you use).
2. Download the jQuerySetup from here.
3. Add the setup script to a textbox on your dashboard page:

Alter files locations if needed, don’t forget the Contains HTML Markup checkbox.
4. Add a dropdown prompt to your dashboard page.

5. Create a javascript file in your b_mozilla directory’s called: autocomplete.js

function SetAutoComplete(PromptColumn){
var domNode = document;
var tagName = '*';
var tags = domNode.getElementsByTagName(tagName);
var y ="";
for(i=0; i<tags.length; i++){

if (tags[i].className == 'GFPFilter') {
if (tags[i].getAttribute('gfpbuilder').indexOf(PromptColumn) != -1)
{
y = tags[i].getAttribute('sid')
};
$(
function()
{
$( "#"+y ).combobox();
});
};
};
};

6. After the dropdown prompt add a textbox with:

<script src="res/b_mozilla/autocomplete.js" language="javascript"> </script>
<script language="javascript">
SetAutoComplete('C1 Cust Name');
</script>

7. Add your report and run the dashboard:



Thanks,
Satya Ranki Reddy

Oracle BI EE 10.1.3.3 – Configuring Delivers – iBots

I had one OBI EE user today who had specifically asked about configuring iBots. Though it is pretty straight forward, the customer was looking out for a screenshot by screenshot tutorial on how to go about setting delivers. Well, i had some time to put this one together. Hopefully, you would find it of some use.
1. The first step is to configure a schema that would hold all our scheduler tables. If you do not have a schema create or use an existing one. In my case i have a schema called S_NQ_SCHED wherein i would create all my scheduler tables.
2. Navigate to your {ORACLEBI}/Server/Schema folder and run the scheduler scripts. If you are on an Oracle Database, you would have to run SAJOBS.Oracle.sql. This would basically create the scheduler tables that Oracle Delivers would use later.
3. The next step is to open your Job Manager. Go to File – > Configuration Options and enter the schema and connection details for the scheduler schema. In my case it would be S_NQ_SCHED schema.

4. Go to the General tab. If you had followed the default install, then leave everything the same. Just enter in the Administrator usernames and passwords.

5. Go to the Mail tab and enter in your mail server details.

6. Open your instanceconfig.xml and add in the scheduler details. This step basically lets the presentation server know about the machine and port details of the scheduler. By default the scheduler port is 9705.

7. The next step is to run a cryptotools utility that would basically store the username and password of the scheduler into an XML file called credentialstore.xml.
8. Go to {OracleBI}/web/bin from command prompt. Then enter in the following command.
cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml

Ensure that you have the alias as “Admin”.
9. The next step is to add the details of this credentialstore.xml file into the instanceconfig.xml.

Once this is done you should be able to schedule and create new ibots. The best and quick way to check whether the configuration has been done properly is to save a sample ibot. If you are able to properly save one then your configuration has been properly set. For more details refer the docs here.




OBIEE Configuring the scheduler on Windows2003/Oracle

        

As with many configurations on OBIEE, it’s in the documentation only distributed on many PDF’s. Here is how install the OBIEE scheduler on Windows2003 and an Oracle DB. Basiicly you have to go trough 5 steps:
1. Create the DB User and Tables
2. Check the instanceconfig.
3. Add the Scheduler Administrator to the credential store
4. Configure the Job Manager
5. Run A test


1. Create the DB User and Tables.
The table scripts can be found in ..\OracleBI\server\Schema\SAJOBS.Oracle.sql, but you have create the user yourself. Or you can use this script:
/*****************************************************************************/
/* Setup OBIEE scheduler on ORACLE */
/* READ THE SCRIPT BEFORE YOU RUN IT !!!!!!!!!! */
/* Run the script as SYSTEM */
/*****************************************************************************/
/*****************************************************************************/
/* Create the S_NQ_SCHED user */
/* You can change the password NOT the user name */
/*****************************************************************************/
DROP USER S_NQ_SCHED;
CREATE USER S_NQ_SCHED IDENTIFIED BY "S_NQ_SCHED_PASSWORD"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT
QUOTA UNLIMITED ON "USERS";
GRANT "CONNECT" TO S_NQ_SCHED;
GRANT "RESOURCE" TO S_NQ_SCHED;
GRANT CREATE SESSION TO S_NQ_SCHED;
GRANT CREATE TABLE TO S_NQ_SCHED;
GRANT CREATE VIEW TO S_NQ_SCHED;
ALTER USER S_NQ_SCHED DEFAULT ROLE NONE;
2. Check the instanceconfig.xml
In ..\OracleBIData\web\config you will find the instanceConfig.xml

Check if the entry between Alerts Tags has a ScheduleServer entry. If you run it on an other port then 9705 add it in the form ServerName:PortNumber (MyServer:1234).
3. Add the Scheduler Administrator to the credential store
Open a commend line box:
cryptotools credstore -add -infile e:/OracleBIData/web/config/credentialstore.xml
Credential Alias: admin
Username: SchedulerAdmin
Password: SchedulerAdmin Do you want to encrypt the password? y/n (y): Y
Passphrase for encryption: secret
Do you want to write the passphrase to the xml? y/n (n): Y
File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to
overwrite it? y/n (y): Y


4. Configure the Job Manager

From the file menu select Configuration Options

On the Database tab enter the connection pool data. Always use the native call interface if possible!.

On the General tab enter the adminstartor name and credebntials
Start the scheduler service

You can find the log in: ..\OracleBI\server\Log
5. Run A test
Create a simple report and press Save And Schedule

Click on the destinations tab an select Oracle BI Server Cache

Save the IBot.
Open a connection with the jobmanager:

Enter the login credentials

Press the refresh button:

Have a look at the entry:



Thanks,
Satya Ranki Reddy

Calling Java Scripts and Java Classes

Oracle BI EE 10g/11g – Calling Java Scripts and Java Classes from iBots

One of the very good features of BI EE is its ability to call custom Java Scripts and Java Classes after the execution of iBots. Lets look at some examples today. In the first example we shall see how to save the reports scheduled via ibots to a local directory using a simple Java Script. It is assumed that you have your scheduler up and running. The first step in this process is to create a simple Java Script like the one below. I have taken this directly from the docs for demonstration purposes. You can customize this to your needs.

var FSO = new ActiveXObject(“Scripting.FileSystemObject”);
var fileName = “D:\\” + Parameter(1);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);

As you see above what this Java Script basically does is that it accepts one parameter (Parameter(1)) which is the file name and saves this file to a desired location that is in D drive. You need to save this file under {OracleBI}\server\Scripts\Common folder.
      
      
Once this is done, the next step is to call these scripts in your ibots. For example, lets choose a dashboard for content, PDF for attachment and schedule it to run immediately. In the Advanced of this ibot, choose the Java Script that you had created earlier. Remember you need to pass the file name (in my case it is sample.pdf) for Parameter(1) as a parameter.
      
      
      
      
      
The above was pretty straight forward. All we needed to do was to put the custom Java Script in a designated folder and call that in the Advanced tab of an ibot. Now lets look at calling a Java Class from an Ibot. There are certain things that we need to understand before we proceed further. Oracle Delivers uses the Java Host Service to make calls to a Java Procedure. So, if you go to {OracleBI}\web\javahost you would find a directory called Scheduler. Under this directory you would find a jar file called schedulerrpccalls.jar. This is the scheduler jar file that we can use to directly obtain the scheduler objects like the iBot attachments, the instance, job ids etc. So, our aim is to call a Java Program after the execution of an iBot is to write the details of the iBot schedule to a text file. In this example, i shall be using JDeveloper. Lets look at the steps one by one.
1.   Open the Config.xml file under {OracleBI}\web\javahost\config in a text editor. Search for the Scheduler tag and change it as shown below.

<Scheduler>
 <Enabled>True</Enabled> <DefaultUserJarFilePath>D:\Oracle\OracleBI\web\javahost\lib</DefaultUserJarFilePath>
</Scheduler>

The reason why we are changing the above tags is to make Java Host aware of the Java Classes that scheduler would be calling. And the DefaultUserJarFilePath tag points to the directory where we would be placing our Jar file( custom jar file using our custom Java Class). After changing this restart the Java Host Service.
2.   Open JDeveloper and create a simple Application, Project with a simple Java Class. In my case i have created a simple Application called SAWSched which in turn has an application called sawsched. The final class file that i created under this project is called as sawsched.java.
3.   In this custom java file lets include the following code. Ensure that you are including the schedulerrpccalls.jar in your Project Properties.

package sawsched;import java.io.*;
import java.lang.Thread;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;
public class sawsched implements SchedulerJavaExtension{
public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException
{
System.out.println(“JobID is:” + jobInfo.jobID());
System.out.println(“Instance ID is:” + jobInfo.instanceID());
System.out.println(“JobInfo to string is:” + jobInfo.toString());
try
{
File outputFile = new File(“D:\\JavaJob.txt”);
File attachFile = jobInfo.getResultSetFile();
attachFile.createNewFile();
FileWriter out = new FileWriter(outputFile);
out.write(“User ID:\t\t” + jobInfo.userID() + “\r\n”);
out.write(“Job ID:\t\t” + jobInfo.jobID() + “\r\n”);
out.write(“Instance ID:\t\t” + jobInfo.instanceID() + “\r\n”);
out.write(“Parameter Count:\t\t” + jobInfo.parameterCount() + “\r\n”);
out.write(“File Path: ” + attachFile.getAbsolutePath());
for(int i = 0; i < jobInfo.parameterCount(); ++i)
{
out.write(“\tParameter “);
out.write(new Integer(i).toString());
out.write(“:\t” + jobInfo.parameter(i) + “\r\n”);
}
out.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}
}
public void cancel()
{
}
}

      
What this class basically does is it creates a text file which would have details about the iBot job. The above example is available in the bookshelf.
4.   Once this is done compile this code to ensure that you have no errors. The next step is to create a JAR file out of this class. So, right click on your Project and create a Jar File. In your JAR ensure that you are also including your schedulerrpccalls.jar and the above compiled class.
      
      
      
I have named my Jar file as BISched.
      
      
Once this is done right click on your resources ( you would find your Jar there) and then click on Deploy.
      
Copy your Deployed Jar file to {OracleBI}\web\javahost\lib (The one that we included in the DefaultUserJarFilePath tag).
      
5.   Now lets go back to delivers and create a simple iBot and choose Java Program in the Advanced Options.
      
In the Java Properties enter the class name, in our case it is sawsched.sawsched and then in the CLASSPATH enter the jar file that we had bundled. Save the ibot and you would notice that a text would have been created under D drive.
      


Thanks,
Satya Ranki Reddy

Setting up iBot to save report to a folder

Setting up iBot to save report to a folder

Here are the steps;


Create a JavaScript file with below contents name it "savetofile.js" and save it at \\bise1\bi\server\Scripts\Common ;

var FSO = new ActiveXObject("Scripting.FileSystemObject");
//var foldername = GetConfigurationValue("Log Dir", "iBots") + "\\" + UserID
var foldername = "\\Reports"
var rptname;
with (new Date) {
rptname = ((((getFullYear() * 100 + getMonth() + 1) * 100 + getDate()) * 100 + getHours()) * 100 + getMinutes()) * 100 + getSeconds();
}
if (FSO.FolderExists(foldername))
{
var fileName = foldername + "\\" + "RPT_" + rptname + Parameter(1) + Parameter(2);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);
}
else
{
FSO.CreateFolder(foldername);
//var fileName = foldername + "\\" + JobID + "-" + InstanceID + "-" + Parameter(1);
var fileName = foldername + "\\" + "RPT_" + rptname + Parameter(1) + Parameter(2);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);
}



Step 1: Create an iBot and set schedule as required;

Step 2: Select Delivery Contents for iBot and select send content as “Attachement(PDF)”

Step 3: Select Email for the Specific Devices in the destination Tab;

Step 4: In the advanced tab select Add Action and then Custom Script

Step 5:
In the file name add “savetofile.js”
Select “Pass Delivery content to script” in the radio button
In the other parameter add two text boxes. In the first box add file name and in the second box add file extension and click OK

Step 6: Save the iBot

Step 7: The report will be saved in the directory “C:\Reports”
File will be named with following convention;
RPT_DATETIME_REPORTNAME.EXTENSION

Step 8: Done and the iBot will look like this;

Thanks,

 

Satya Ranki Reddy