Tip of the Day on Saved Filters

For each dash board prompt, create corresponding saved filter and use this filter across all reports.
Are you thinking why?
Suppose you have so many pages on dashboard with common dash board prompt across all pages and each page has one report, each report has drilldown to detail reports.
In these situations many people will create filters on each and every report from scratch, even though dashboard prompts (filters) are common across all the main pages and detailed pages. In these situations using of saved filters will be more powerful, its just one time creation and reusing it in all reports.
Even it is more useful, if requirement changes frequently on dash board prompts, its just matter of changing corresponding saved filter that’s all. How cool it is :)

Here is step by step to create Saved Filter and using it in reports.

1) Go to Answers and Click on create saved filter button
2) Choose subject area
3) Choose columns and corresponding condition i.e. all dashboard prompts.
4) Save
After save look like this
5) Create a report and add this saved filter.
6) Save report.
7) Link this report and dash board prompt in Dash Board page.
8) Browse the page.

Hope you will follow this as development standard, going forward in your development.


Sawping is BI server ping tool comes along BI installation, it has following otions.

sawping [-p port] [-s host] [-v (verbose mode)] [-q (quiet mode)] [-h]

Here is an example on ping local machine which has BI server services down
C:\OracleBI\web\bin>sawping -v
Unable to connect to server. The server may be down or may be too busy to accept additional connections.
An error occurred during execution of "connect". No connection could be made because the target machine actively refused it.
Error Codes: ETI2U8FA

Suppose server is running on remote machine
C:\OracleBI\web\bin>sawping –v –s servername
Server alive and well.

Custom logos/template in Office Add-ins

Default office excel report will be populated with oracle logos and footage, if you want to have customization as per your company, then here is a solution.
All these information is stored in template files, which are located at C:\Program Files\Oracle\BIOfficeExcel\Template\ on disk with file names as OBIReport.xlt, OBIReport.xls modify these files by remove oracle logos, add your logos, header, footer, back ground logo etc and save. Upon logon to office add-ins you will be able to see reports with your template.

Helpful articles,
1) Step by step on Using Office Add-ins with OBIEE in Oracle tech web

Purge BI Server Cache from Dash Board

One of colleague asked me, is it possible delete server cache from dash board? So started working on it, finally got solution how to do that.
I have created a script using C# on top of obiee-webservices, built a web page and embedded it on Dashboard, as of now I have incorporated Purge All, Purge by Database and they are working cool.

I have refered following resource,

Thanks John, for a very good article on cache purge.
Oracle technical resource on cache purging.
Oracle technical guide on Web Services.

I am thinking to make it as handy tool and available to every one.

All Resource files at one place

Many times I have confused to use fmap :)
For suppose I have placed html file in images folder (OracleBI\web\app\res\s_oracle10\images) and tried accessing using ‘fmap: images/help.html’ this was pointing me to wrong url http://localhost/analytics/Missing_images/help.html suppose to be http://localhost/analytics/images/help.html.
Then I have realized each and every resource folder has specific purpose in context to fmap usage i.e. if you want to place any image file then you need place it in only images folder.
If you want to place any help files we need to place these in “help” folder, by default help folder will not be there so create a help folder under OracleBI\web\app\res\s_oracle10 then keep what ever web file need.

But suppose you want to refer doc file as help file, then this is not possible using “fmap:help/help.doc”

If suppose you have dev, test, prod servers and you are doing an incremental releases then you need to look for all resource folders for any change at each release migrating from one server to other server.

To avoid all above problems its better to manage all resource files in one folder (virtual directory) local to BI server and access resource files from that url.

How to do that?
This method works well with IIS.

Create a virtual directory; this can be in three ways
Way 1:
1) start – run—inetmegr
2) locate analytics web folder
3) Right click – new – select virtual directory
4) Follow GUI
5) Alias as “ObieeResourcesFiles” or what ever name --next
6) Choose a folder to place all resources
7) Say next next
8) In 6th step folder place all resource files needed.

Way 2:
1) Go to C:\inetpub\wwwroot
2) Create a folder “ObieeResourcesFiles”
3) Restart iis services.
4) Place your resource files into this folder

Way 3:
1) Create a folder “ObieeResourcesFiles”
2) Right click folder select properties
3) Goto web sharing tab select bullet Share this folder.
4) Say okay, place all resource files here.

Now goto IIS manager you will be able to see your resource files in folder “ObieeResourcesFiles” in this directory locate any file – right click—select Browse, corresponding file will be opened in internet explorer copy that url paste it in where ever required in report.
Url look like http://localhost/virtualdirectory/resourcefile
Sample: http://localhost/ObieeResourcesFiles/Help.htm

Following image shows how it works.

Hope it helped you.

TODATE function's possible error

I have observed todate function error couple of days back situation is like this.

One of our report consist dashboard prompt on Quarter and other columns, default quarter is set to current quarter using repository variable, report does not have any date dimension columns to display, we have one measure in this report as QTD_Amount related column in RPD defined as “ifnull(ToDate(sum(ifnull(Order_Total , 0)), [Level Quarter]) , 0)” in this situation report works as expected.

Even if I chose some other quarter using Dropdown works fine or Dash Board prompt changed to Multi Select control on Quarter and choosing “All Choices” works fine, but when I select “all choices” in dropdown then it gives following error.

Odbc driver returned an error (SQLExecDirectW).

Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22036] Level in TODATE function (Quarter) must be a rollup of (or equivalent to) the measure level (Year). (HY000)

Report expects quarter column( level) or in other terms sql-group by expects quarter. To overcome this error we need to add level column of todate to report and hide that column if you don’t want to display.

Hope it helped.

Obiee Web Services – Using .net and C#

From many days I was thinking to blog on Web Services using .net, today I could able to make it.
If you want to see what are all available web services in Bi Server then just browse this url in any browser http:///analytics/saw.dll?WSDL you will be able to see all available services.
Here is a very basic code (kind of hello world) in C# which will logon user to BI server and get the report results.
To do that, go to report's advanced tab collect Request XML, then create project in visual studio and web reference to project as show in fig.
As per our requirement we need following class objects to achieve our task
SAWSessionService, ReportRef, XmlViewService.
Here is code which will authenticate user and gets report results as xml string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using saw = Obiee_Services.web_Reference;
using System.IO;
using System.Data;

namespace Obiee_Services
class HelloWorld
static void Main(string[] args)
var sawservice = new saw.SAWSessionService();
//Give user credentials who has soap access.
var sessionID = sawservice.logon("Administrator", "");

var repRef = new saw.ReportRef();
//Pass report name along path and Request XML to ReportRef object
repRef.reportPath = @"/users/administrator/#cachehitsbyusers";
repRef.reportXml = "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"xmlVersion=\"200705140\"xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"xmlns:sawx=\"com.siebel.analytics.web/expression/v1\">\" \"columnID=\"c1\"/>formula=\"SUM(S_NQ_ACCT.NUM_CACHE_HITS)\"columnID=\"c4\"/>";
var xmlViewService =new saw.XmlViewService();
var xMLQueryExecutionOptions = new saw.XMLQueryExecutionOptions();
xMLQueryExecutionOptions.refresh = true;
//Pass report parameters if you have
var reportParams = new saw.ReportParams();
//Execute XML Query
var queryResults = xmlViewService.executeXMLQuery(repRef, saw.XMLQueryOutputFormat.SAWRowsetSchemaAndData, xMLQueryExecutionOptions,reportParams, sessionID);
//Output as you required

There are around 10 main classes and each has its own functionality.

"HtmlViewService" to embed Oracle BI HTML results into ASP or JSP or Web Portals
"IBotService" to execute Oracle iBots programmatically
"JobManagementService" to manage all jobs related things
"MetadataService" to retrieve description of columns, tables, and subject areas etc
"ReplicationService" to provide methods for replicating catalog
"ReportEditingService"to merges arguments with services data as users needed
"SAWSessionService" provides authentication related stuff
"SecurityService" to identifying accounts and there privileges
"WebCatalogService" for navigating and managing the Presentation Catalog
"XmlViewService" retrieve results from BI Web Services in XML format

http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b31769.pdf this pdf covers all available class and respective methods exposed to end users.

--Hope you have enjoyed.

Funny Error: htmlreportgenerator

I was writing Direct Database Query while doing this, I have come across following funny error
"View Display Error Assertion failure: nFields > 0 at line 1214 of ./project/webreportgenerators/htmlreportgenerator.cpp
Error Details Error Codes: OQ78YWIW"
I have provided proper connectionpool name, proper query still i could see above error.
Funny part is, in criteria I have provided all proper information, clicked on result tab saw error, went back to criteria did nothing, again clicked on Results tab, this time query worked fine.

Even i have tried ByPassCache still same as above only.