Non Standard SQL in BIP 11g – Dynamic query implementation


SQL query is often used as a data source in BIP and often we have a requirement in which we have to use one table when the user selects a certain value in a parameter and select a totally different table when the user selects another value in the parameter. While we have discussed a method to do this using pipelined functions in http://obiee-oracledb.blogspot.com.au/2011/07/performance-enhancing-drug-called-table.html, this blog post talks about a (more...)

Increasing width of Multiselect in OBIEE 10g


I was recently involved in a project implementation on OBIEE 10g. The dashboard had a multiselect and the values in the prompt were huge. The standard width of the multiselect was making the user uncomfortable since a large part of the value was hidden due to the small size of the multiselect. This blog talks about a method to fix this problem.

Let me first show you how the multiselect looked.







I added a text (more...)

Privilege to see hidden objects in OBIEE 11g


My client wanted to hide some of the reports from the users and release them at a later date. The however wanted a set of users to have the privilege to see hidden objects. Let us see the process to achieve this objective.

While we can directly assign the privilege to the users, it is far better to do the following.

1. Create a power user group
2. Create a new role
3. Assign the (more...)

Smart Tables in OBIEE 11g


This article introduces the concept of Smart Tables in OBIEE. I call them 'Smart' because these tables have the ability to produce different data on the fly. These can morph themselves according to the user requirement. Smart Tables also make the data model dynamic in nature as a Smart Table can change characteristics on the fly. Let me now explain the procedure of implementing Smart Tables.
Objective: We will create 2 tables in the database. (more...)

Skipping schedule in BI Publisher 11g


This is an awesome feature in BI Publisher 11g but I do not hear a lot of noise about it so let me create some.
I'm talking about Event triggers in BI Publisher 11g. Using event triggers, we can conditionally stop the delivery of a report. We will see the process of skipping schedules in this article.

Steps:
  1.   Create a data model for a BIP report.
    • a. Define a dataset
    • b. Define a Event (more...)

Drill-able charts in OBIEE 11g


This is a discovery more than a solution. OBIEE 11g lets us use Hierarchical column as dimensional attributes for charts.

Using hierarchical columns in chart enables us to create drill-able charts. The default view can be the top level view and the user can then drill to a more granular view in the chart on the fly.

Consider the following Analysis. I have dragged hierarchical time dimension column and a measure column. I have created (more...)

Formatting levels of a hierarchical column in OBIEE 11g


I had a requirement to indent the levels in a hierarchical column. The solution isn't really innovative but I thought of sharing this because finding the option can become a treasure hunt.

The objective is to attain the following.

















Note that the values in every consecutive level is indented compared to the previous level.

Steps:

1. Drag a hierarchical column, expand its levels and select Hierarchy Level Properties as shown below.








2. You will land (more...)

Capturing Environment Variables in Oracle APEX


 This post focuses on the Environment variables as they are vital in coding conditional statements.

Check https://apex.oracle.com/pls/apex/f?p=81782:2:0::::: to have a look at the possible variables in a typical APEX environment.

Let me share a screenshot of the page

The page shown above has the following 2 regions.

1. Using USERENV
2. CGI Variables

Let us talk about these one by one.

Using USERENV region: This region displays information captured using the (more...)

String aggregation in OBIEE


In the BI world, we talk about measures and aggregation but we don't talk a lot about string aggregation. String aggregation is about concatenating strings for a certain dimensional value. Let me give an example. Let us say that we have a purchase order (Lets call it PO1) raised for a set of items (Lets say I1,I2 and I3). Lets say that the supplier does not have I1 in stock and wants a larger order (more...)

Using DB Link in OBIEE


This blog talks about the process to use DB Links on OBIEE.

Since we are talking about linking data from 2 different databases, I will like to mention that OBIEE gives the freedom to join tables across databases in the physical layer.
BI Publisher in 11g also lets us join data sets but the join is currently an equi join. This join is called a "Link" in BI Publisher world and you can find more (more...)

Using BIP parameters in OBIEE dashboards without creating prompts


This is a simple trick that presents the process to have a parametrized BIP report in OBIEE without the need to create new prompts in OBIEE.


Steps:

1. Edit the dashboard and drag Embedded Content in an OBIEE dashboard section.

2. Put the following URL in the Embedded Content Properties
http://<OBIEE presentration server>:<OBIEE presentation server port>/xmlpserver/<BIP report xdo file path starting from shared folders>?&_xmode=2

For example,

http://<OBIEE presentration server>:<OBIEE presentation server port>/xmlpserver/Supply+Chain/Item (more...)

Ways to create Matrix report in Oracle APEX


This is another one of those articles where I pick a topic from the sample chapter of my book and share with you. Some of the other discussions in this blog from the sample chapter of my book are on dynamic breadcrumbsdata densification and ReForms. In this post, we will see 3 different ways of creating a Matrix report a.k.a Crosstab report a.k.a Pivoted report.


Let me (more...)

Consuming OBIEE 11g Web Services using Java


We will talk about consuming OBIEE 11g webservices using Java in this article. My previous articles have been about consuming webservices using JavaScript and consuming webservices using PL/SQL. This is my first on consuming webservices using Java. 
My book also has a number of recipes about consuming and creating web services. Check out a brief description of the book and the table of contents.

The prerequisite for this article is Oracle JDeveloper Studio Edition (more...)

Creating ReForms in APEX


Let me begin by describing a ReForm. A ReForm is an amalgum of Reports and Forms and has the DNA of both. This discussion not only presents a ReForm but also demonstrates the enormous benefits of using JQuery in APEX report development.
I wish to state that this article is (more...)

Data Densification in Oracle APEX


Data is often stored in sparse form and business often wants to see data in a more organized and explicit fashion. Report developers working across reporting tecnologies face a requirement to show a record with a '0' in the measures columns if no data exsists for a particular combination of (more...)

Creating dynamic breadcrumb chains in Oracle APEX


This article is borrowed from one of the topics of my book (http://www.packtpub.com/oracle-apex-4-2-reporting/book) on Oracle APEX 4.2 reporting. I wish to talk about the process to engineering breadcrumbs of varying lengths in this article. Let me explain the motive with an example.


Let us say (more...)

Tracking the running reports in OBIEE 11G

 
I faced a rather strange but relatively simple requirement a few days back. The client had employed a resource to refresh the sessions page and capture the running queries from it and mail it to a team that dealt with performance tuning. This is certainly not the most efficient (more...)

Sangam 2013


Just read about http://www.sangam13.info/.The event has been cohosted by AIOUG and India OAUG

Such events are indeed rare and few in India. Really hope this to be a trendsetter.


Sangam has been instrumental in introducing Jonathan Lewis, one of the most credible voices on Oracle, as (more...)

ESRI map with Google map and map viewer in APEX


This article is in continuation of my previous workon Oracle Map Viewer/Map Builder and Oracle 12c database.

This article demonstrates the conversion of ESRI maps to datatype (SDO_GEOMETRY) that can then be used in Map Viewer. The article ends with an HTML code that can be put in APEX's HTML region to display the map with ESRI info on it.


Let’s start

1.    Get an ESRI map. I got it from http://www.mapmakerdata. (more...)

My take on using Mapviewer with Oracle 12c and Google maps API



Map viewer is a kool technology and I wanted to write about it for a long time. 


Let me first chalk the steps that we will follow. We will


1. Download Map Viewer and Map Builder.

2. Create a pluggable database (pdb), create a pdb user and grant the (more...)