Create Report in BI Publisher 11G using RTF template


What is BI Publisher (BIP)?

BI Publisher is a sub tool of OBIEE. It will be installed as a part of OBIEE. The link used for accessing BI Publisher is http://<hostname>:<portname>/xmlpserver

For example http://localhost:9704/xmlpserver

BI Publisher vs OBIEE!!

BI Publisher is the pixel perfect reporting solution within OBIEE. Presentation Services (Answers) is the analytical solution with in OBIEE.

BI Publisher is a simpler tool compared to the OBIEE answers. BI Publisher is used for reporting.Answers is used for analysis.

We can create the reports quickly using BI Publisher by directly writing queries against the database. Whereas in OBIEE answers we have an underlying RPD and it takes a lot of time and effort creating reports.

It is easier to query against the ODS using BI Publisher.

Discussion on this topic can be found in the below link :

In my present project we are using BI Publisher in the initial phase of the project. Later we will be developing the same reports using OBIEE.

The steps to be followed for developing the BIP reports are:

1.Configure the Data Source

We have to mention the data source details. This is done using Administration section.

2.Develop the Data Model

You can either use the Query Builder utility or directly paste the complete SQL of the report to be generated for developing the data model.

3.Create the report

In this step we can just drag and drop the columns into a Table,Chart or other similar layouts.

NOTE: BI Publisher has a limitation that we won’t be able to add more than 5 or 6 columns in the table view.

In this case we have to develop a RTF template which contains the table structure.We will then import this RTF template to BIP and create the report. The RTF template is just a Word document saved in .rtf format. This document will have the table structure and xml code written in this.

1. Configuring the Data Sources

Types of Supported Data Sources

Oracle BI Publisher supports various types of Data Sources such as JDBC data sources (Oracle Database, and other Databases), XML Files, BI EE Reports (Answers), JNDI data sources, OLAP data sources, Fusion Application Data sources, Web Services, HTTP data sources, and Discoverer.

Before you create a data model based on these data sources, you need to configure a connection to these data sources first. In this topic, you will learn how to configure a JDBC connection to an Oracle Database.

Defining a JDBC Connection

1. To define a JDBC connection click the Administration link found on the right side of the BI Publisher page.

This displays the BI Publisher Administration page as shown below .Click the JDBC Connection link found under the Data Sources section in the Administration page.

2. This displays the Data Sources page. In the JDBC section, click Add Data Source to create a JDBC connection to your database.

3. In the Add Data Source page that is displayed, enter the details as given below –

Data Source Name – OE

Driver Type – Select a driver type to suit your Database (for example, you can select Oracle 10g or Oracle 11g to suit your Database). Database Driver Class – oracle.jdbc.driver.OracleDriver (Define a driver class to suit your Database)

Connection String – Provide the database connection details. For example hostname:port:sid.

User name – OE (Database user name)

Password – OE (Database user password)

Do not click Apply or Cancel after defining the above details. Click Test Connection.

If the connection to the database is established, a confirmation message is displayed indicating the success.

Then click Apply.

Note: To continue with this tutorial exercises, the sample schemas OE should be installed in your Oracle Database. Else, you will not be able to define this JDBC connection

4 .You can see this newly defined connection (OE) in the list of JDBC Data Sources.

Note: You can also set up the the default connection demo to point to OE schema in your database, as the samples use this connection. Then the demo connection can be used as the default data source for this tutorial too.

2. Developing the Data Model

1. To define a Data Model click the Data Model as shown below.

2. Select the data source in the data model.

3. From the Data Set drop-down menu, select SQL query as the type of data set.

4. In the Create Data set -SQL dialog box that appears, enter an appropriate name for the data set, accept the default data set option (as you have already defined it in the Data Model), and click Query Builder to create a query. When it comes to complex queries it will be difficult to create it using the query builder.

The other option is to create the query manually in SQL Developer and paste the query here.

5.The next step is to view the XML Output and Saving the Sample Data.

To save this as sample data, click the Open Menu drop-down list icon, and select Save as Sample Data.

Note: It is very important to save sample data for a data model, else when creating Layouts, the previews do not appear correctly.

You can select various values (multiple/ single) for Department to see the data. You can also restrict the number of rows that you want to see.

3. Creating the report

I will be explaining 3 methods of creating a report. First method is to directly create the report. Second method is by using the RTF template Add-in feature of BIP. Third method is by importing the RTF template.

Refer the below link for knowing steps to be followed for creating the report directly :

We won’t be able to create a report with more than 5 or 6 columns using the above(first) method. Hence the third method of creating report by importing the RTF template is used frequently. You can follow the second method for creating the RTF template in a simpler way.

I will be discussing the third method here.

We need to create a RTF template containing the report layout. Follow the steps below for this:

Steps to create the RTF template

Open a Word Document .Create the table structure necessary for the report.

Create the table structure shown below in word.

Also enter the code shown below for getting the columns values

Note that G_1 is the element tag name(see the image below). Also note that we have inserted the tag <?for-each:group element tag name?>

before the first column value and <?end for-each?> in the last column.


Save the word document in rtf format as shown.

Now that we have created the template follow the steps below for creating the report.

1. Click New (found at the top left corner of page) to see the drop-down menu and click Report to create a new report.

2. Select the data model on which the report should be built and click Open.

3. Click on upload as shown below.

4. The screen shown below will appear. Here we need to enter the layout name. Also browse the word document(saved in rtf format) you have created.For the steps to create the RTF template which can be used as the template file see above . Enter the other fields as shown below.

7. The template will be uploaded as shown below.

8. Click the Save icon found on the top right corner to save this layout.

Enter Salary as the name of the layout, and click Save.

9.Now click Save and save the report.

10. Click on view report.

11. You will be able to see the report as shown below.

You can explore more possibilities. For example you can create an RTF template with your company logo , report name and date as shown below.

Later this template can be used to develop reports by importing it to BIP.

Till next time …

Defining KPI,KPI Watchlist and Scorecard in OBIEE 11G

Defining KPI,KPI Watchlist and Scorecard in OBIEE 11G

What are KPIs ?

KPIs are measurements that define and track specific business goals and objectives that often roll up into larger organizational strategies that require monitoring, improvement, and evaluation. KPIs have measurable values that usually vary with time, have targets to determine a score and performance status, include dimensions to allow for more specific analysis, and can be compared over time for trending purposes and to identify performance patterns.

(source: oracle docs )

How KPIs are Evaluated ?

A KPI’s status and score are determined by comparing its actual value against the thresholds that you define. The performance status of a KPI is represented by the status icon that you assign to each range.

(source: oracle docs )

Process Flow for Creating a KPI

(image source: oracle docs)

Goal : Build key performance indicators and scorecards in your business intelligence metadata with Oracle Business Intelligence Enterprise Edition 11g.


Creating KPIs

We are planning to create 2 KPIs Sales Performance and Margin Maintenance. Later we will be using these KPIs to create a scorecard .To create the KPIs, take the following steps:

1. Log in to Oracle Business Intelligence Enterprise Edition 11g, using your username and password. From the home page, select New -> KPI from the common header area. When prompted to select a subject area, select Sales History.

2. The KPI Entry screen will then appear. This screen enables you to define the actual value and target value measures for the KPI, as well as an optional time offset to show performance as a trend. In this example, enter “Sales and Costs Fact”.”Amount Sold” as the Actual Value measure and “Sales and Costs Fact”.”Amount Sold Target” as the Target Value measure, and leave the Enable trending checkbox unchecked. Click Next to proceed to the Dimensionality screen.

Note: We can change the data type if required by editing the Data Format.

3. In this example, you want users to be able to switch the product category for which the KPI is displayed but you want the year, 2001, to stay constant (or “pinned”). To do this, click the Add button on the Dimensionality screen, select the “Times”.”Calendar Year” attributes, and select 2001 for Value. Then click Add again to add the “Products”.”Product Category” attribute column, but leave Value as <is prompted>. Click Next to proceed to the next step in the process: defining the states.

4. On the States screen, you can specify values that will determine whether the performance achieved warrants an OK, Warning, or Critical indication. In this example, performance will be considered OK if it is within 90 percent of the target and critical if it falls below 70 percent. To define these thresholds, enter “Sales and Costs Fact”.”Amount Sold Target” * .90 as the first threshold value and “Sales and Costs Fact”.”Amount Sold Target” * .70 as the second.

Note: We can add more levels if required. Select the Show Advanced Properties for this.

5. Click Next to proceed to the next step, where you can define related documents for the KPI. This enables users to reference other analyses, external Web pages, and documents that can be used to support the KPI. For this example, however, there are no related documents, so click Next.

Note: We can add either a catalog object (eg: report) or links here.

6. This enables users to manually create a custom column for the KPI. For this example, however, there are no custom attributes. Click Finish to move to the final steps, where you can name and save the KPI.

7. Now repeat this process to create the second KPI, which will be used to monitor the margin made on sales. Enter the “Sales and Costs Fact”.”% Unit Margin” measure for Actual Value, but this time enter the literal value 40 for Target Value (because you want to ensure that sales have a target margin of 40 percent). Use the same attribute columns for the KPI dimensionality, and enter the values 25 and 10 as the two thresholds. Once complete, save the KPI to the Web catalog, using the name Margin Maintenance.

Displaying KPIs, Using a KPI Watchlist

Now that you have defined two KPIs, you can give users a means of displaying these on a dashboard through a KPI watchlist. To create a KPI watchlist, take the following steps:

1. From the Oracle Business Intelligence home page, select New -> KPI Watchlist.

2. From the Catalog view on the left-hand side of the screen, drag and drop the two KPIs you created previously onto the watchlist on the right. The selected KPIs will then be displayed in a grid format, showing the actual and target values, along with the variance and the percentage variance.

3. The KPI watchlist will be pinned to the year 2001, per your KPI definitions, but it will enable users to select the product category. To select a specific product category, use the list next to the “Sales History”.”Products”.”Product Category” label above the KPI watchlist and select a category such as Photo or Electronics. Note how the KPI values are recalculated in response to your product category changes in this dimension and how the status changes to reflect the values compared to the thresholds you chose earlier.

Creating a Scorecard

Now that you have defined your KPIs, you can arrange them into a simple scorecard to help achieve your organization’s goals. Using this scorecard, you will define an objective, “Improve Performance,” that breaks down into two contributing objectives, “Increase Sales” and “Maintain Margins.” Users will be able to use this scorecard to view progress toward the organization’s goal, either for all product categories or for any one category in particular.

To create the scorecard, take the following steps:

1. Login to Oracle Business Intelligence Enterprise Edition 11g using your username and password, and select New -> Scorecard. When prompted, name the scorecard Organizational Performance and click OK to accept the default scorecard settings.

2. With the new scorecard open, navigate to the Strategy panel at the top left corner of the screen and click the Create Objective button. Name the new objective Improve Performance, and click the Save button just above the text box to save the new objective to the scorecard definition.

3. With the objective you just created selected in the Strategy panel, repeat this process to create another objective, naming it Increase Sales (this new objective should be located under the Improve Performance objective, to indicate that it contributes to the broader objective).

4. When adding this new objective, assign a KPI to it. To do so, use the Catalog panel on the bottom left of the screen and drag and drop the Sales Performance KPI onto the Objectives and KPIs list for the objective. Click OK to save this objective.

5. Now repeat the previous step, with the Improve Performance objective initially selected—but this time creating a third objective, Maintain Margins—and associate the Margin Maintenance KPI with it.

Your Strategy panel within the scorecard should now have a primary objective, Improve Performance, with two contributing objectives, Increase Sales and Maintain Margins, under it. To complete the initial scorecard, you will now create a strategy tree to show how these objectives are related.

To create a new strategy tree, navigate to the Strategy panel at the top left of the screen and this time click the View Strategy Tree button. When the strategy tree appears, expand the entries under the objectives to see the KPIs and their values.

This strategy tree shows that the Sales Performance objective is being met across all product categories but the Maintain Margin objective is not, contributing to a warning about the overall objective. Use the dimension selector above the strategy tree to focus on individual product categories to see how each of them is performing against the targets and the thresholds you defined earlier.


Organizations often use KPIs and scorecards to measure their performance in terms of reaching an objective, setting targets and thresholds to enable them to measure progress across the organization. Oracle Business Intelligence Enterprise Edition 11g gives you the ability to define these objectives and strategies within your BI environment, bringing this important measure of corporate performance to the desktops of everyone on your team.

Add Application Policy to Application Role in OBIEE

Add Application Policy to Application Role in OBIEE

Hi …I have created a Application role Webcat Admnistrators . I want to grant rpd access to this role.

Follow the below mentioned steps:

Step 1 :

Step 2 :

Step 3 :

Step 4 :

Select the permission,

This will grant rpd access to this role.

Check whether the permission has been added.

Now if we grant the role WebcatAdministrators to a group, the user included in that group will be able to login to the rpd.

OBIEE 11G Scheduler

Hi i tried out how to schedule a report in OBIEE11g .Thought i will share the errors which i got and steps to implement the scheduler with everyone .Hope you will enjoy this post..


Like OBIEE 10g we can create a saved request / Analysis in OBIEE 11g and we can schedule the reports. But in OBIEE 11g all the tables related to scheduler services are preconfigured. When we are installing OBIEE 11g, we are running repository creation utility (RCU). RCU is creating two schema called Metadata schema (MDS) and BIPLATFORM. All the scheduler related tables were created in BIPLATFORM schema. So Enterprise Manager (EM) is taking these schemas automatically and running scheduler services in BI 11g.

Connect with BIPLATFORM schema in the data source. There we can view the scheduler related tables S_NQ_JOB, S_NQ_INSTANCE, S_NQ_JOB_PARAM, S_NQ_ERR_MSG which are created already when we were running RCU.

Configure Email settings:

1. Login to Fusion Middleware Control Enterprise manager (http://yourservername:7001:/em) using Admin user credentials

2. Navigate to Mail tab (Business Intelligence > coreapplication > Deployment>Mail

3. Click Lock and Edit Configuration to enable changes to be made.

4. Complete the elements with the following information.

• SMTP Server – SMTP server of your email (e.g.

• Port – Port of the SMTP server (e.g. 25)

• Display name of sender – Any name(e.g.Oracle Business Intelligence)

• Email address of sender – Sender’s email address (e.g.

• Username – Same as the sender’s email (e.g.

• Password – password of your email

• Confirm password – confirm the same password as above

• Number of retries upon failure – any number

• Maximum recipients

• Addressing method To, Blind Copy Recipient (Bcc) – if you want to receive a BCC, select it.

I created a gmx mail account by logging into have entered the username and password of that account here.

Note: If you search a free smtp service, the site is reported as offered a free smtp server which work without the need to upgrade to a professional account as for instance Google and Yahoo ask for.

smtp server :

port : 25

5. Click Apply, and then click Activate Changes.

6. Return to the Business Intelligence Overview page and click Restart.

Creating an Agent to send mails:

Since we are going to configure just to test the email settings we did in the above, we create this agent as simple as possible.

Login to OBIEE Web (http://yourservername:7001/analytics)

1.Click New in the Global header and then select Agent and Actionable Intelligence.

2.In the General tab set the Priority as Normal

3.In the Schedule tab and then select Once from the Frequency list box.

4. In the Delivery content tab, select the content you want to deliver clicking the Browse button.

5. In the Recipients tab, add emails (you can add external users mails as well) whom do you want to receive a mail with delivery content.

6.In the Destinations tab,select the destinations which include user dashboard and email.

7. Save the agent and Run it.You will be able to run the agent only after saving it. Click the icon shown below for running the Agent.

If it runs successfully recipient get the mail with pdf attachment (we selected pdf format in the Delivery content tab).

Also it appears on the corresponding dashboard of the user.

Problems i faced while doing this:


This issue is my Mozilla was updated to Mozilla10.It doesn’t support Oracle BI Presentation Services.


I downloaded and installed Google Chrome OR you can download Waterfox which is 64bit version of Firefox .Its working fine now. I found Google Chrome browser to be a really good browser.


This error came when i created an agent and was trying to save it.The issue is with IE9 version.


Works well in Google Chrome.Able to save the agents now.


I had given 2 recipients. The report alert was being delivered only to the dashboard of the user. The report was not delivered to the mail. The issue came because i tried to send using Gmail, yahoo mail etc..


Select the SMTP server as gmx or give your company SMTP server.

Hope it helps, if you still have any issues you can discuss with me in comments section below.


Differences between ODBC and OCI

ODBC: – Open Database Connectivity, which is known as Universal Data Connector.
OCI: – Oracle Call Interface, Normally connects to Oracle Source.
Hence, both can connect to Oracle Sources.
ODBC: – if we are using ODBC to connect Oracle source then we need to give ODBC connection in the machine where the BI Server installed on.
— So, the machine which have BI Server that should contain ODBC connection.
OCI is a Native Connection, without having ODBC Connection in the machine we can import the data.
Ex: – Importing through Server.


OCI is a much better choice than ODBC for the following reasons:

* OCI is optimized for queries. Transparent pre-fetch buffers reduce round-trips and improve performance and scalability. As a result, there is reduced memory usage on the server.
* OCI is optimized for round-trips. No-reply requests are batched until the next call is generated for the server. This allows certain calls to be lazily propagated.
* OCI is thread safe. You do not need to mutex (use mutual exclusivity locks) any of the OCI handles. ODBC is not thread safe, so you have to mutex most data structures.
* OCI provides an asynchronous event notification API for active databases.
* OCI provides enhanced array data manipulation language (DML) operations that reduce round-trips.
* OCI returns ROWIDs for all rows selected for update in a single round-trip. Using ROWID allows for more efficient SQL access.
* ODBC has no concept of sessions. OCI decouples connections, sessions and transactions. Multiple users can use a single connection; they get serialized on the connection for SQL operations. Multiple transactions can exist per user. This allows users to scale and service more users than there are connections available. Sessions and transactions can be migrated between connections to the same server.
* ODBC does not support object types, large objects (LOBs), and other new Oracle datatypes.
* ODBC affects server scalability. Using ODBC and having n number of concurrent users forces the server to have n number of processes service the clients if Oracle8i is operating in dedicated server mode. Your operating system may or may not support so many connections and processes.
* ODBC is a wrapper around OCI so it is slower.

How to Update Physical Layer Table Columns

Issue: New columns are added to a table in the database. We want to add these columns to the physical layer table.

Possible ways:

  1. Drop the previously imported table and re import it again. But this method won’t work out when we have already defined joins and built the rpd, because we will have to define all the joins again if we follow this method.
  2. The suggested method is to use the “Update Physical Layer” Utility. Follow the steps below:

Aggregation Rules and Functions

The following list describes the aggregation rules that are available for measure columns in the “Layout pane” and for columns in the “Edit Column Formula dialog: Column Formula tab”. The list also includes functions that you can use when creating a calculated item.

Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.
Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.
Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the numbers of rows with nonnull values for that column are returned.
Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
None — Applies no aggregation. Not available for calculated items.
Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.
Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

Examples are:

Level Based Measure and Report Totals in OBIEE 11G

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

The calculation of this measure is independent of the query grain and used always the aggregation grain of the logical column.

Level-based measures allow:

  • to return data at multiple levels of aggregation (the query grain and the level-based column grain) with one single query
  • to create share measures (percentage), that are calculated by taking some measure and dividing it by a level-based measure. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

The new OBIEE function in 11G AGGREGATE AT can leverage the same functionality.

Create Level Based Measure

In this scenario column “Amount Sold by Region” is a level based measure. In the column “Amount Sold by Region”, amount sold is calculated at the region level. In the below figure even though the country name is changing, the region is Europe (constant) and hence Europe will have the same “Amount Sold by Region” for different country names.

To create a level based measure as the amount sold by region, you can:

  • make a copy of the Amount Sold logical column
  • and then
  • drag the logical column into its hierarchy level
  • or set the aggregation grain in the level tab of the logical column property.

Method 1 (with a lowest query grain)

With a lowest query grain, each query that requests these columns will return the amount aggregated to its associated levels (in our case by region).

To obtain the good total, you have to uncheck the aggregate option : Report-Based Total (when applicable).

Method 2 (with a higher query grain)

When a query includes a level-based measure column and the query grain is higher than the level of aggregation specific to the column, the query results return null. Note that in releases previous from 11g, results were returned for this situation, but they were not deterministic.

How Will Report-Based Totals Created in Previous Releases Be Upgraded?

In previous releases, you had the ability to create report-based totals in table views. Because report-based totals are handled slightly differently in this release, you might notice a difference in totals as follows:

  • If the previous table included all report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
  • If the previous table view included a mix of report-based totals and non-report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.
  • You can work around the upgraded totals manually. If you want to use the same measure value as in the previous release, then duplicate the measure column in the table and use the Aggregation Rule menu to specify a non-report-based total.
  • If the previous table view included all non-report-based totals, then all measure columns and attribute columns in the upgraded table will continue to use non-report-based totals.

Query for Reference

SELECT Data1.c4 AS column1,
Data1.c2 AS column2,
SUM(Data1.c1) OVER (partition BY Data1.c4) AS column3,
Data1.c1 AS column4
T175.COUNTRY_NAME AS column2,
) Data1
ORDER BY column1, column2


By default, each level of a dimension hierarchy shows both:

  • dimension columns that are assigned to that level,
  • And level-based measures that have been fixed at that level. If you don’t want this behavior, you can check the “Hide Level Based Measures” options (Tools/Options in the Administration Tool).

The query results return null because the query grain is higher than the level of aggregation specific to the level-based measure column.

How to Start AdminTool in OBIEE 10G / OBIEE 11G

admintool.exe is the Administration tool to manage OBIEE server and have some capabilities to automate some repetitive tasks and principally his embedded tools.

Oracle Business Intelligence Administration Tool is available only for Windows operating systems.

The Administration Tool is a Windows application that allows the BI Server administrator to create and edit repositories.

Repositories can be edited with the administration tool in either online or offline mode.

The Administration Tool also includes a session manager and a cache manager which are available in online mode. The Cache Manager displays what queries have been cached and how frequently they have been accessed. You can also select and delete items in cache.

Start in OBIEE 10G

You can start it in two ways:

  • with the Windows Menu : Start > All Programs > Oracle Business Intelligence > Administration
  • by starting the executable file admintool.exe (only for the version 10g)

The admintool is located in this directory


Start in OBIEE 11G

You can start it in two ways :

  • with the Windows Menu : Start > All Programs > Oracle Business Intelligence > Administration
  • launch the Administration Tool from the command line using bi-init.cmd.

Do NOT open the RPD by simply double clicking on the RPD file or typing Admintool.exe in a command line. The resulting Administration Tool window is not initialized to your Oracle instance, and errors will result later in your session. Ignoring this will lead to creating inconsistencies in the RPD file.

The admintool is located in this directory.

BI_ORACLE_HOME/bifoundation/server/bin (\MiddelwareHome\instances\instance1\bifoundation\server\bin


In 11g Client, log file is located at: